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

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.

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.

You should specify and aggregate the field you are counting. See below:

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

I would approach this using NOT EXISTS, NOT IN, or LEFT JOIN. Here is the latter version:

select d.*
from downloadedtickets d left outer join
     redeemedtickets r
     on d.ticketid = r.ticketid
where r.ticketid is null;

Your query seems like it should return the specified result. One possible explanation for the resuts you are getting (no rows) is that there aren't any downloaded tickets that aren't redeemed.

This query seems to assume that ticketid is UNIQUE in the downloadedtickets table, and UNIQUE in the redeemedtickets table... that may be the case, but we don't have that information from the specification we were provided. (If that's not the case, then the COUNT(*) for a ticketid from just the downloadedtickets table could be greater than one.

Is ticketid the right column to use to "match" the rows from the two tables? We'll assume it is, because that's what your query is using. (If it's not, that could also explain the result you are getting.)

Your query is of the form (more easily read):

SELECT t.ticketid 
  FROM ( SELECT d.ticketid
           FROM downloadedtickets d
          UNION ALL
         SELECT r.ticketid
           FROM redeemedtickets r
       ) t
 GROUP BY t.ticketid
HAVING COUNT(*) = 1

We note that there's potential that this query could return a ticketid for a row in redeemedtickets that isn't in downloadedtickets. There may be some sort of guarantee that this wouldn't happen, but again, that information is absent from the specification.

For large sets, materializing that inline view could be expensive.

Personally, I'd prefer use a query with a more efficient "anti-join" pattern:

SELECT d.ticketid
  FROM downloadedtickets d
  LEFT
  JOIN redeemedtickets r 
    ON r.ticketid = d.ticketid
 WHERE r.ticketid IS NULL
 ORDER BY d.ticketid

This essentially says, return all rows from downloadedtickets, along with any "matching" rows from redeemed tickets. The LEFT keyword makes this an "outer" join, so we get all rows from the table on the left side, whether or not there are matching rows from the table on the right side. The trick is the predicate in the WHERE clause, that filters out all rows that had a match. (If there was a match, we are guaranteed that ticketid from redeemedtickets will be non-NULL. So, the only rows that would have a NULL value from that table would be rows from downloadedtickets that didn't have a match.

This query can make efficient use of an index on redeemedtickets with a leading column of ticketid.

This isn't the only query that will return the specified result, there are other query patterns that can return an equivalent result.