Using the same table more than once in a SQL View

Etienne_123

Hi Is it possible to use one table more than once when creating a SQL VIEW? For example, the tables I`m using store data about houses and it's features (e.g. bathrooms, bedrooms, etc), and what I want to accomplish is to display the number of bathrooms as well as the number of bedrooms in one view. To do this I`m using the COUNT function to count the number of FeatureId's where the FeatureName = 'Bathroom' etc. as shown below:

SELECT DISTINCT dbo.Mandate.Id, COUNT(BedroomFeature.FeatureId) AS Bedrooms, dbo.MandateType.MandateType, dbo.Mandate.ErfSize
FROM dbo.Mandate INNER JOIN
dbo.MandateType ON dbo.Mandate.MandateTypeId = dbo.MandateType.Id LEFT OUTER JOIN
dbo.MandateListing ON dbo.Mandate.Id = dbo.MandateListing.MandateId LEFT OUTER JOIN
dbo.MandateFeature AS BedroomFeature ON dbo.Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.Feature AS Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.Feature ON BedroomFeature.FeatureId = dbo.Feature.Id
GROUP BY dbo.Mandate.Id, dbo.MandateType.MandateType, dbo.Mandate.ErfSize

This displays perfectly with the number of bedrooms. How can I show an extra column for the number of bathrooms? I tried adding the features table again and giving it a different alias, but somehow that didn't give me the desired results

loveangel888

One method i can think of is using a store procedure to get your count separately. Or a sub query in the select parameter. But either ways aren't really efficient ways. P.S. first time answering questions here. Hoped I helped

Wayne Gaylard

Could you give a bit more detail on the structure of the database, as it seems to me this is a really difficult way to find out the number of bathrooms and bedrooms for a particular house, especially for a database that profiles houses.

...and I have extensive experience writing computer code, including OIC, BTW, BRB, IMHO, LMAO, ROFL, TTYL.....

Etienne_123

I have a MandateFeature table that contains two Id's , MandateId and FeatureId. Each of these are foreign keys from two different tables (Feature and Mandate). If a mandate (house) contains 3 bedrooms and the Id for bedroom is 5, then the entries in the MandateFeature table will look something like this: MandateId | FeatureId ---------------------- 2, 5 2, 5 2, 5 If a mandate (house) has 2 bathrooms and the Id for bathroom is 6, then the table would look something like this: MandateId | FeatureId ---------------------- 2, 5 2, 5 2, 5 2, 6 2, 6 What I want to do, is to count the number of bedrooms as well as the number of bathrooms and then display it as follows: Bedrooms: 3 Bathroom: 2 So I get it working using this:

SELECT DISTINCT dbo.__Mandate.Id, COUNT(BedroomFeature.FeatureId) AS Bedrooms, dbo.__MandateType.MandateType, dbo.__Mandate.ErfSize
FROM dbo.__Mandate INNER JOIN
dbo.__MandateType ON dbo.__Mandate.MandateTypeId = dbo.__MandateType.Id LEFT OUTER JOIN
dbo.__MandateListing ON dbo.__Mandate.Id = dbo.__MandateListing.MandateId LEFT OUTER JOIN
dbo.__MandateFeature AS BedroomFeature ON dbo.__Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.__Feature AS __Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.__Feature ON BedroomFeature.FeatureId = dbo.__Feature.Id
GROUP BY dbo.__Mandate.Id, dbo.__MandateType.MandateType, dbo.__Mandate.ErfSize

..but this of course only displays the number of bedrooms. As soon as I duplicate this part:

LEFT OUTER JOIN
dbo.__MandateFeature AS BedroomFeature ON dbo.__Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.__Feature AS __Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.__Feature ON BedroomFeature.FeatureId = dbo.__Feature.Id

and modify it to this:

LEFT OUTER JOIN
dbo.__MandateFeature AS BathroomFeature ON dbo.__Mandate.Id = BathroomFeature.MandateId AND BathroomFeature.FeatureId =
(SELECT Id

Mycroft Holmes

Move your sub query from the join to the select and add the mandateID to the where clause. This can be repeated for different feature types. Caveat this type of sub select can be very slow and expensive. Another way would be to left join out to the feature table multiple times (1 for each feature type) using a case statement on the if field (case featureid when is null then 0 else 1 end), group by your mandate fields and sum each feature field. Another way is to use the same multiple joins and then pivot the rows so each feature is in it's own column.

Never underestimate the power of human stupidity RAH