Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jun 2002
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Problem with "distinct"

    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.

  • #2
    New Coder
    Join Date
    Jun 2002
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    any more ideas?

  • #3
    New Coder
    Join Date
    Jun 2002
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GOT IT!!

    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.

  • #4
    New to the CF scene
    Join Date
    Jun 2002
    Location
    Illinois
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Let me splain

    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •