如何检查多个表中是否存在类似的值

Question

I've read and reviewed numerous posts on here (and elsewhere) about how to do this, but my results seem to be working backwards from what makes sense.

I have 4 tables that have different columns/data, but the columns I need to select from are the same and have the same structure.

Table 1: | id | item_id | user | other table 1 specific columns...<br>
Table 2: | id | item_id | user | other table 2 specific columns...<br>
Table 3: | id | item_id | user | other table 3 specific columns...<br>
Table 4: | id | item_id | user | other table 4 specific columns...<br>

I am trying to check to see if a specific user/item_id combination exists across all 4 tables in 1 query instead of 4.

If I'm reading all the posts correctly, I should do a UNION ALL between the SELECT statements, however, I'm actually getting the reverse of what is making sense.

To keep things simple, I am just testing this with Table 1 and Table 2.

Table 1 does NOT have the combination I'm looking for.

Table 2 does have the combination I'm looking for.

SELECT item_id AS table1ID
FROM table1
WHERE item_id=35 AND user='usernamehere'
UNION ALL
SELECT item_id AS table2ID
FROM table2
WHERE item_id=35 AND user='usernamehere'

So, what I was expecting to see was a null result from Table 1 and a result with the item_id from Table 2, but what's happening is I'm getting no result at all from Table 2 and a "hit" from Table 1.

Seems backwards and confusing since Table 1 doesn't actually have a record with item_id=35 and user='usernamehere'.

Any advice or insights are greatly appreciated.

Answer 1

Your query is just listing item_id's in the specific table. It does not specify where they come from. For two table, you can solve the problem using a join.

I think for more tables, you will want something like this:

select item_id, group_concat(which order by which) as tables
from ((select item_id, user, 'table1' as which from table1) union all
      (select item_id, user, 'table2' from table1) union all
      (select item_id, user, 'table3' from table1) union all
      (select item_id, user, 'table4' from table1)
     ) t
where user = 'usernamehere'
group by item_id;

For each item_id, this will list the tables it is in. If you want items in all four tables. then add a having clause:

having count(distinct which) = 4;

Similarly, if you want examples where one or more are missing:

having count(distinct which) < 4;