CODEWITHSUNDEEP
mysql
APorter1031
APorter1031

Reputation: 2256

MYSQL Check if every item that exists within one table, exists in another

How can I check if all the items in one table exists within another. For example, consider the following two tables.

With their respective query:

SELECT orderID
FROM orders
WHERE customer_id = 1;

TABLE A
-------

orderID
2
1
6
4

SELECT orderID
FROM orders
WHERE customer_id = 2;

TABLE B
-------

orderID
5
1
7
9

I would not want to output anything because all the items from TABLE A do not exist within TABLE B.

However, I were to have:

SELECT orderID
FROM orders
WHERE customer_id = 3;

TABLE C
-------

orderID
5

I would want to output it because all the items within TABLE C are in TABLE B.

If I were to do a select with TABLE C, I would expect table C to be the output.

I have tried the following query:

SELECT *
FROM (SELECT orderID
FROM orders
WHERE customer_id = 1) A
WHERE A.orderID IN (
SELECT orderID
FROM orders
WHERE customer_id = 2);

Upvotes: 0

Views: 326

Answers (2)

totalhack
totalhack

Reputation: 2598

One approach would be to left join a table with TABLE B and look for nulls in the resulting sub-query. Here is some (untested) pseudo code:

SELECT * 
FROM ( 
 SELECT 
 table_B.orderID 
 FROM table_A 
 LEFT JOIN table_B ON table_A.orderID=table_B.orderID 
) x WHERE x.orderID IS NULL

Any rows from table_A that don't have a match in table_B will have a NULL value as table_B.orderID. The outer select you may have to change depending on what type of output you are looking for, but hopefully the principal helps.

Upvotes: 0

Andrew
Andrew

Reputation: 7880

If you can to check in a by-customer basis, you can do this:

IF (NOT EXISTS
    SELECT NULL
    FROM orders
    WHERE customer_id = 1 -- "Table A"
    AND orderID NOT IN 
        -- "Table B"
        (SELECT orderID
        FROM orders
        WHERE customer_id = 2)
    )
    SELECT orderID
    FROM orders
    WHERE customer_id = 1;

Here the query basically gets the orders from table A which are not in table B. If there is none, it then performs the select at the end. Otherwise, it does nothing.

If you use customer_id = 3 instead, you get one row with orderID 5 ("table C").

Upvotes: 1

Related Questions