Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    109

    Unanswered: Getting unique rows from the resultset

    Hi,

    I am trying to do a join which involves more than 3 tables. One is a parent table and the other two table have 1:n relationship with that parent table.

    But duplicate records are being returned in the resultset. How do I eliminate duplicate records?

    This is my query.

    SELECT table1.* from table1 left outer join table2 on table1.id=table2.id
    left outer join table3 on table1.id=table3.id

    I tried doing DISTINCT here but with no success.

    SQL with distinct clause.

    SELECT distinct table1.id, table1.* from table1 left outer join table2 on table1.id=table2.id
    left outer join table3 on table1.id=table3.id

    This is the error I get:
    The text data type cannot be selected as DISTINCT because it is not comparable

    Please help.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There you have it - you cannot use distinct with a text field.

    However - could you post your entire query? If that is your entire query then do you realise that table2 and table3 are superfluous?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    The duplicates will be caused by tables 2 or 3 having multiple matches. Since you're not using the data in tables 2 or 3 their only value is in helping to select the data to be shown so an EXISTS clause would be more appropriate.

    For example:

    table 1: employees
    table 2: paymentHistory
    table 3: W2s

    You want to see all the employee info for anyone who's ever been paid and who has a W2 record.

    Select table1.* where exists
    (select top 1 table2.* left join table2 on table2.EmpID = table1.EmpID)
    and exists
    (select top 1 table3.* left join table3 on table3.EmpID = table1.EmpID)


    If you insist on continuing with the query you already have, you can do a Group By and list the columns in table1. That will compress all the duplicates together.

  4. #4
    Join Date
    Aug 2003
    Location
    India
    Posts
    109
    Here is the query:

    Select distinct TOP 10 tickets.ticket_id, clients.client_name, tickets.subject, tickets.message from tickets
    left outer join replies on tickets.ticket_id=replies.reply_ticket_id
    left join clients on tickets.client_id=clients.client_id
    where tickets.subject like '%search%'
    or tickets.message like '%search%'
    or replies.message like '%search%'
    order by tickets.ticket_id desc

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Perhaps:
    Code:
    SELECT TOP 10 tickets.ticket_id, clients.client_name, tickets.subject, tickets.message 
    FROM tickets
    LEFT OUTER JOIN clients on tickets.client_id=clients.client_id
    WHERE tickets.subject like '%search%'
    OR tickets.message like '%search%'
    OR EXISTS (SELECT NULL FROM replies WHERE tickets.ticket_id=replies.reply_ticket_id AND replies.message like '%search%')
    order by tickets.ticket_id desc
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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