CODEWITHSUNDEEP
mysqlsqlunique
jerrygarciuh
jerrygarciuh

Reputation: 21998

Querying distinct pairs of ids

This query is successfully pulling pairs of ids for rows in table taxi_lines where a driver appears with more than one open assignment (eg in 2 taxis at once).

My problem is that it pulls each pair of ids in both orders (eg 500 and 509 and then 509 and 500).

data

I tried adding DISTINCT(CONCAT(tl1.id,tl2.id)), and also GROUP BY id1, id2 but these did not achieve unique pairs.

Here is the working query that gets the data above. How do I refine to get unique pairs?

SELECT tl1.id AS id1, tl2.id AS id2
FROM taxi_lines tl1
JOIN taxi_lines tl2 ON tl1.driver = tl2.driver
AND tl1.id != tl2.id
AND tl1.driver > 0
WHERE tl1.end =  '0000-00-00 00:00:00'
AND   tl2.end =  '0000-00-00 00:00:00'

Upvotes: 0

Views: 106

Answers (3)

Vulcronos
Vulcronos

Reputation: 3456

Try something simple like:

AND t11.id > tl2.id

This will only return rows where the first id is larger than the second. It will get rid of half your rows.

Upvotes: 1

comfortablydrei
comfortablydrei

Reputation: 316

Replace AND tl1.id != tl2.id with AND tl1.id > tl2.id

Upvotes: 2

user3703582
user3703582

Reputation:

what affect does this have

SELECT distinct 
      case when tl1.id < tl2.id then tl1.id else tl2.id  end as id1,
      case when tl1.id < tl2.id then tl2.id else tl1.id  end as id2

FROM taxi_lines tl1
JOIN taxi_lines tl2 ON tl1.driver = tl2.driver
AND tl1.id != tl2.id
AND tl1.driver > 0
WHERE tl1.end =  '0000-00-00 00:00:00'
AND   tl2.end =  '0000-00-00 00:00:00'

Upvotes: 0

Related Questions