using join in multiple tables with 1-to-many

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

using join in multiple tables with 1-to-many

Author

Topic

ktechf
Starting Member

7 Posts

Posted - 2011-09-21 : 08:28:30

I have 3 tables with 2 of the tables having 1-to-1 relationship while the 3rd table have 1-to-many relationship with the other 2 tables.

I wanted to write a query to select just one of the multiple records in the 3rd table using join with the 2 first tables.

SELECT M.PropertyId, M.Email, M.PropertySummary, M.PropertyLocation, M.PropertyType, M.PropertyStatus, M.BedRooms, M.Price, P.BathRooms, P.PropertyAddress, P.AvailableTitles, PP.PictureName FROM MData AS M INNER JOIN Prop AS P ON M.PropertyId=P.PropertyId INNER JOIN PropPictures PP ON P.PropertyId=PP.PropertyId WHERE M.Email='g@gmail.com'

This works BUT returning mutiple records on the PropPicture table.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 08:45:25

you need to apply GROUP BY on third table based on joining field and then apply MIN() or MAX() over pk of the table to make sure you return one out of multiple record existing for particular value of joining field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ktechf
Starting Member

7 Posts

Posted - 2011-09-21 : 12:41:28

i tried this out and did not work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 12:45:59

show the used query.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ktechf
Starting Member

7 Posts

Posted - 2011-09-22 : 06:49:32

Thanks this is the new query

SELECT M.PropertyId
,M.Email
,M.PropertySummary
,M.PropertyLocation
,M.PropertyType
,M.PropertyStatus
,M.BedRooms
,M.Price
,P.BathRooms
,P.PropertyAddress
,P.AvailableTitles
,PP.PictureName
,MAX(PP.PropertyId)
FROM MetaData AS M INNER JOIN Property AS P ON M.PropertyId=P.PropertyId
INNER JOIN PropertyPictures AS PP ON P.PropertyId=PP.PropertyId WHERE PP.Email='S@gmail.com' Group BY PP.PropertyId

Error displayed:
Column 'MetaData.PropertyId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 12:12:26

[code]SELECT M.PropertyId
,M.Email
,M.PropertySummary
,M.PropertyLocation
,M.PropertyType
,M.PropertyStatus
,M.BedRooms
,M.Price
,P.BathRooms
,P.PropertyAddress
,P.AvailableTitles
,PP.PictureName
,PP.PropertyId
FROM MetaData AS M INNER JOIN Property AS P ON M.PropertyId=P.PropertyId
INNER JOIN PropertyPictures AS PP ON P.PropertyId=PP.PropertyId
INNER JOIN (SELECT PropertyId,MIN(PropertyPictureId) AS First
FROM PropertyPictures
Group BY PropertyId)PP1
ON PP1.PropertyId = PP.PropertyId
AND PP1.First = PP.PropertyPictureId
[/code]
WHERE PP.Email='S@gmail.com'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ktechf
Starting Member

7 Posts

Posted - 2011-09-22 : 13:03:59

Thanks visakh.

I tried this out but it still returns the multiple records.

SELECT M.PropertyId
,M.Email
,M.PropertySummary
,M.PropertyLocation
,M.PropertyType
,M.PropertyStatus
,M.BedRooms
,M.Price
,P.BathRooms
,P.PropertyAddress
,P.AvailableTitles
,PP.PictureName
,PP.PropertyId
FROM MetaData AS M INNER JOIN Property AS P ON M.PropertyId=P.PropertyId
INNER JOIN PropertyPictures AS PP ON P.PropertyId=PP.PropertyId
INNER JOIN (SELECT PropertyId, MIN(PropertyId) AS First
FROM PropertyPictures
Group BY PropertyId)PP1
ON PP1.PropertyId = PP.PropertyId
AND PP1.First = PP.PropertyId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 13:07:10

please use query as suggested. you're using MIN(PropertyId) as against MIN(PropertyPictureId) in my posted suggestion. b/w PropertyPictureId is the primary key of your PropertyPictures table (i've assumed the name as i dont know your actual column name so make sure you use correct column name instead)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ktechf
Starting Member

7 Posts

Posted - 2011-09-22 : 14:06:47

well, What I have like a prymary key on PropertyPictures table is SN which is auto generated. The PropertyId on the PropertyPictures table is the link between to other 2 tables. It is not a p/k which is the reason why i was able to have multiple records on the PropertyPictures table.

Thanks for your help and support. They are highly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 22:50:25

so has it been sorted out? if no pk in property pictures you've to do something like below to get one out of multiple.


SELECT M.PropertyId
,M.Email
,M.PropertySummary
,M.PropertyLocation
,M.PropertyType
,M.PropertyStatus
,M.BedRooms
,M.Price
,P.BathRooms
,P.PropertyAddress
,P.AvailableTitles
,PP.PictureName
,PP.PropertyId
FROM MetaData AS M INNER JOIN Property AS P ON M.PropertyId=P.PropertyId 
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY PropertyId ORDER BY PropertyId) AS Rn,
            * FROM PropertyPictures )AS PP 
ON P.PropertyId=PP.PropertyId 
AND PP.Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ktechf
Starting Member

7 Posts

Posted - 2011-09-26 : 08:21:30

Thank you Visakh, for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 09:03:07

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources