distinct rows in table

Question

0.00/5 (No votes)

See more:

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 proppertyID while joining these three tables but the problem is that i am not getting unique rows... here is the query...please tell me what is the problem ...

SQL

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

Posted 6-May-11 9:04am

hinzhonee

Updated 6-May-11 9:19am

#realJSOP

v3

Add a Solution

Comments

Corporal Agarn 6-May-11 15:09pm

Updated to add pre tags

5 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Costica U · Answer 1 · 2011-05-08T01:59:00

For SqlServer2005 and up take a look SQL Server CROSS APPLY and OUTER APPLY[^]

So the query will be:

SQL

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
             *
      FROM   Residential
      WHERE  PropertyID = p.PropertyID
      ORDER
      BY     Res_ID --- change this for your criteria
      ) AS r
OUTER
APPLY (
      SELECT TOP 1
             ImageName 
      FROM   Images
      WHERE  PropertyID = P.PropertyID
      ORDER
      BY     ImageName --- change this for your criteria
      ) AS i

Gordon Kushner · Answer 2 · 2011-05-06T09:25:00

Because you are returning Images.ImageName and there are multiple image records, DISTINCT will still give you all the rows.

It depends on what you are trying to do. Do you want to get every one of the images? Just one? Are you trying to get one property row for properties that have at least one image? There are techniques, but it would be helpful to know what you want from this query.

Solution 2 below seems like the way to go, but you could also achieve the same thing by changing the join like this:

From this:

SQL

LEFT OUTER JOIN Images AS i ON p.PropertyID = i.PropertyID

To this:

SQL

LEFT OUTER JOIN (SELECT TOP 1 ImageName FROM Images WHERE PropertyId = p.PropertyId ORDER BY id) AS i ON p.PropertyID = i.PropertyID

My apologies, my solution was crap. Here is a way that works.

Get rid of the LEFT OUTER JOIN Images and change the i.ImageName AS Expr2 in the SELECT clause
to this:

SQL

Expr2 = (SELECT TOP 1 ImageName FROM Images WHERE PropertyId = p.PropertyId ORDER BY id)
from vehicle V

dbrenth · Answer 3 · 2011-05-06T10:14:00

I assume that your "Images" table is the one creating the multiple lines.

What you will need is to join that Images table to itself where the second instance of itself identifies the photo you want to display. For example, I will assume you want the "latest" photo of the property. In that case there must be a date in your Images table so you will do something like this:

SQL

... LEFT OUTER JOIN  Images i1 ON p.PropertyID = i1.PropertyID
    INNER JOIN
      (select max(t.imagedate) maximagedate, t.propertyid
      from images t
      group by t.propertyid) i2
  ON i1.propertyid = i2.propertyid and i1.imagedate = i2.maximagedate

This also assumes that your photo date is not duplicated among your property ids. But you should get the idea from what I am doing.

Good Luck

Ger Hayden · Answer 4 · 2011-05-06T10:36:00

Solution 4

This CP Solution is similar and is working well for me.

Even if my aim as a little differant.

Posted 6-May-11 10:36am

Ger Hayden

AspDotNetDev · Answer 5 · 2011-05-06T13:09:00

There is some abiguity in your question, but a TOP 1 JOIN may be of use to you.

However, if you have trouble understanding that, I'd say you should use a temporary table or cursor to filter through results at each step (rather than JOINing).

By the way, the reason your DISTINCT is not working is because it takes all the fields you specify into account when determining if a record is distinct. I imagine "r.Res_ID" and "i.ImageName" are usually going to be different, especially when in combination with all the other fields in your SELECT query.

distinct rows in table

5 solutions

Solution 7

Solution 1

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

distinct rows in table

5 solutions

Solution 7

Solution 1

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us