CODEWITHSUNDEEP
sql
dazed-and-confused
dazed-and-confused

Reputation: 1333

How do I find what Ids are missing from a table when "those" ids are the result of a query?

These are my tables:

Customer Table
--------------
CUST_ID
SUPPLIER1
SUPPLIER2

Supplier Table
--------------
SUPPLIER_ID
USERID

User Table
----------
USER_ID

The rules:

Customers can have multiple suppliers but suppliers MUST be in the user table if they have a customer.

Because all suppliers have to be users, I need to find all used or assigned suppliers that are missing from the user table.

This query gets me all suppliers that are not users

SELECT 
    U.*
FROM 
    USER U
LEFT JOIN 
    SUPPLIER S ON S.USER_ID = U.USER_ID
WHERE 
    S.USER_ID IS NULL

But how do I get all of the customer's suppliers that are not users?

I tried this but it doesn't seem to be correct:

SELECT 
    *
FROM 
    USER U
LEFT JOIN 
    (SELECT C.SUPPLIER1, S.USER_ID FROM CUSTOMER C, SUPPLIER S WHERE 
     C.SUPPLIER1 = S.SUPPLIER_ID AND C.SUPPLIER1 IS NOT NULL) 
    S2 ON S2.USER_ID = U.USER_ID
WHERE 
    S2.USER_ID IS NULL

Thanks

Upvotes: 0

Views: 76

Answers (2)

Gary Walker
Gary Walker

Reputation: 9134

Is your problem that the Customer table is not normalized, i.e., you have repeated supplier columns in the customer table. You can join the suppliers table twice. e.g.

select C.Supplier1, U1.UserID, C1.Supplier2, U2.UserID
from Customer C
left join Supplier S1 on (S1.Supplier_id = C.Supplier1)
left join User U1 on (U1.UserID = C.Supplier1)
left join Supplier S2 on (SS.Supplier_id = C.Supplier2)
left join User U2 on (U2.UserID = C.Supplier2)
where 
   ((not C1.Supplier1 is null) and (U1.UserID is null))
or ((not C2.Supplier1 is null) and (U2.UserID is null))

This could easily be more useful to use as the union of a supplier1 missing with supplier2 missing.

Upvotes: 0

Wietze314
Wietze314

Reputation: 6020

SELECT
C.Supplier1
FROM Customer C
LEFT JOIN Supplier S
ON C.Supplier1 = S.supplier_id

WHERE S.user_id IS NULL

Upvotes: 1

Related Questions