Get unique rows - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Get unique rows

Author

Topic

honee
Starting Member

5 Posts

Posted - 2011-05-07 : 07:45:55

hi!
i want to join three tables.and i want to get distinct values while selecting the data.
scenerio is like " i have tables named property, residential and images, and property table has propertyID as a primary key and images and residential has propertyID as foreign key.and images table has multiple rows with same propertyID.now i want to select unique rows from property table with a unique propertyID while joining these three tables i have multiple imageNames in the image table with same propertyID and i just want on image not all images corresponing to a propertyID but the problem is that i am not getting unique rows... here is the query...please tell me what is the problem ...

Collapse
SELECT DISTINCT p.PropertyID, p.AgentID, p.UserID, p.Title, p.PropertyType,
p.Location, p.Address, p.City, p.Country, p.TotalArea,
p.CoveredArea, p.BuiltAfter, p.for_sale_rent, p.Status,
p.Description, p.Price, p.Latitude, p.Longitude,
p.CornerPosition, p.KeyWord,
r.Res_ID, r.PropertyID AS Expr1, r.HomeType, r.Rooms,
r.DrawingDining, r.Garages, r.Kitchen, r.Bath, r.Lounge,
r.Furnished, r.Basemant, r.serventQuarter, r.Stories,
i.ImageName AS Expr2
FROM Property AS p
LEFT OUTER JOIN Residential AS r ON p.PropertyID = r.PropertyID
LEFT OUTER JOIN Images AS i ON p.PropertyID = i.PropertyID

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-07 : 08:12:34

When you have multiple rows in the Images and/or Residential tables, you will get multiple rows in the result set for a single row in the Property table if you do only a simple left join. So if you want only one row per Property, you have to decide which of the multiple ImageNames you want to get and, which Residential row you want to get. There are a few different ways to do this. I am showing couple of them below:

SELECT
	p.PropertyID,
	p.AgentID,
	p.UserID,
	p.Title,
	p.PropertyType,
	p.Location,
	p.Address,
	p.City,
	p.Country,
	p.TotalArea,
	p.CoveredArea,
	p.BuiltAfter,
	p.for_sale_rent,
	p.Status,
	p.Description,
	p.Price,
	p.Latitude,
	p.Longitude,
	p.CornerPosition,
	p.KeyWord,
	MAX(r.Res_ID) AS OneofTheRes_IDs,
	-- an aggregate function such as max, min etc. of other columns here
	MAX(i.ImageName) AS Expr2
FROM
	PROPERTY AS p
	LEFT OUTER JOIN Residential AS r
		ON  p.PropertyID = r.PropertyID
	LEFT OUTER JOIN Images AS i
		ON  p.PropertyID = i.PropertyID
GROUP BY
	p.PropertyID,
	p.AgentID,
	p.UserID,
	p.Title,
	p.PropertyType,
	p.Location,
	p.Address,
	p.City,
	p.Country,
	p.TotalArea,
	p.CoveredArea,
	p.BuiltAfter,
	p.for_sale_rent,
	p.Status,
	p.Description,
	p.Price,
	p.Latitude,
	p.Longitude,
	p.CornerPosition,
	p.KeyWord;

That used a max function to pick one row from Residentials and Images tables. The next one picks one randomly, but you could add an ordering criterion to decided which one.


SELECT
	DISTINCT p.PropertyID,
	p.AgentID,
	p.UserID,
	p.Title,
	p.PropertyType,
	p.Location,
	p.Address,
	p.City,
	p.Country,
	p.TotalArea,
	p.CoveredArea,
	p.BuiltAfter,
	p.for_sale_rent,
	p.Status,
	p.Description,
	p.Price,
	p.Latitude,
	p.Longitude,
	p.CornerPosition,
	p.KeyWord,
	r.Res_ID,
	r.PropertyID AS Expr1,
	r.HomeType,
	r.Rooms,
	r.DrawingDining,
	r.Garages,
	r.Kitchen,
	r.Bath,
	r.Lounge,
	r.Furnished,
	r.Basemant,
	r.serventQuarter,
	r.Stories,
	i.ImageName AS Expr2
FROM
	PROPERTY AS p
	OUTER APPLY
	( SELECT TOP 1 r.Res_ID,...,r.Stories 
	  FROM Residential r 
	  WHERE r.PropertyId = p.PropertyId
	  -- an order by criterion if required
	)r
	OUTER APPLY
	( SELECT TOP 1 i.ImageName AS Expr2
	  FROM Images i
	  WHERE i.PropertyId = p.PropertyId
	  -- an order by criterion if required
	)i

honee
Starting Member

5 Posts

Posted - 2011-05-07 : 09:55:04

thanks alot..it really worked..thankyou so much.:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources