...

View Full Version : Problem with "distinct"

Justin Ba
06-20-2002, 10:58 PM
SELECT DISTINCT
a.field1, c.field2, a.field3, left(d.field4,15)
FROM tbl_Table1 a
LEFT JOIN tbl_Table2_R c ON a.field1 = c.FKField1
LEFT JOIN tbl_Table3_R d ON a.Field1= d.FKField1
WHERE 1=1
AND a.field3= 'text' ORDER BY a.field3

is returning duplicate a.field1 values where there are many d.field4s for the 1 a.field1. I'm totally stumped as i need it to return only 1 record per a.field1... I'm running SQL server 7.0

thanks in advance.

Justin Ba
06-20-2002, 11:49 PM
SELECT DISTINCT
a.field1, c.field2, a.field3, left(d.field4,15)
FROM tbl_Table1 a
LEFT JOIN tbl_Table2_R c ON a.field1 = c.FKField1
LEFT JOIN tbl_Table3_R d ON a.Field1= d.FKField1
WHERE 1=1
AND a.field3= 'text' ORDER BY a.field3, a.field1

Same problem :confused:

any more ideas?

Justin Ba
06-21-2002, 06:54 PM
Ok guys... if anyone else has the same problem... after much thinking, i came up with a cheater way to accompish what i wanted!!

here is the solution!

SELECT DISTINCT
a.field1, c.field2, a.field3, MAX(left(d.field4,15))
FROM tbl_Table1 a
LEFT JOIN tbl_Table2_R c ON a.field1 = c.FKField1
LEFT JOIN tbl_Table3_R d ON a.Field1= d.FKField1
WHERE 1=1
AND a.field3= 'text' GROUP BY a.field3, a.field1, c.field3

Just add max to all the fields that might be Many -> 1 with your primary table.:thumbsup:

Gozirra
06-27-2002, 04:43 AM
Just so you know, distinct selects distinct rows, not a distinct column. Thus, select distinct col1, col2 from x will give you all distinct rows. If col1 = x 100 times but col2 = x+1 within each row, you will get 100 rows in your result set.

Peace, Love, and Darth Vader.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum