CODEWITHSUNDEEP
phpsql
user3843997
user3843997

Reputation: 205

SQL query selecting from two tables returning duplicate rows

I am trying to run this SQL query:

SELECT t.*, tu.*, t.contact_name as contact_name, 
t.contact_email as contact_email, t.ticketnumber as ticketnumber, 
t.subject as subject 
FROM tickets t, ticket_updates tu 
WHERE t.ticketnumber = tu.ticketnumber 
AND tu.type = 'update' AND tu.customer <> 'Y' 
AND t.status = 'Awaiting Customer' AND tu.datetime <= '2014-10-18 16:26:00' 
order by tu.datetime DESC LIMIT 0,5

It is returning the correct results, but its showing multiple of the same row

Upvotes: 0

Views: 52

Answers (2)

AnchovyLegend
AnchovyLegend

Reputation: 12538

Try to select DISTINCT:

SELECT DISTINCT t.*, tu.*...

OR you can try a GROUP BY:

SELECT t.*, tu.*, t.contact_name as contact_name, t.contact_email as contact_email, 
 t.ticketnumber as ticketnumber, t.subject as subject 
  FROM tickets t, ticket_updates tu 
  WHERE t.ticketnumber = tu.ticketnumber AND 
  tu.type = 'update' AND tu.customer <> 'Y' AND 
  t.status = 'Awaiting Customer' AND 
   tu.datetime <= '2014-10-18 16:26:00' 
   GROUP BY tu.`id`
   order by tu.datetime DESC LIMIT 0,5

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

For a quick fix you could use SELECT DISTINCT. Usually, though, duplication is a hint there is a problem with your database and/or join conditions. So make sure there are no duplicates in the tables, and the join conditions are correct.

Upvotes: 0

Related Questions