从2个MySQL表中选择非重复记录

Question

I've 2 tables namely: 1. downloadedtickets 2. redeemedtickets

Each table has the attribute "ticketid".

I want to find people who downloaded their ticket but did not redeem them - essentially, find non-duplicated (unique) ticketids.

My Query (PHP) is as follows:

$sql = "
SELECT ticketid 
FROM (
  SELECT ticketid 
  FROM downloadedtickets 
  UNION ALL 
  SELECT ticketid 
  FROM redeemedtickets
)
GROUP BY ticketid 
HAVING COUNT(*) = 1";

I am not getting any output from this.

Answer 1

A good way to do this is a left join, with a test for NULL.

SELECT d.ticketid
  FROM downloadedtickets d 
  LEFT JOIN redeemedtickets r USING(ticketid)
 WHERE r.ticketid IS NULL

The LEFT JOIN operation creates a resulting row for every downloaded ticket and its redemption. Where there's no redemption, the redeemed.ticketid is NULL, so this query uses WHERE to select just those items.