CODEWITHSUNDEEP
sqlpostgresql
puji
puji

Reputation: 507

How do you join two tables A and B in order to get a non join column in A with data not in B?

I'd like to do a join across the following two tables that would help identify all of the Customers that have Attributes they are missing. A full outer join on Attributes doesn't work since the null values don't correspond to any customer. One option is to group by Customers to get a count of distinct attributes and compare that with count of attributes. That would give Customers that have discrepancies, but wanted underlying attributes missed. Is this possible?

Table A (Customer to Attributes)
-----------
C1 | ATTR_1
C1 | ATTR_2
C1 | ATTR_3
C2 | ATTR_1
C2 | ATTR_2
C3 | ATTR_3

Table B (Attributes)
-------
ATTR_1
ATTR_2
ATTR_3

Output
------
C2 | ATTR_3
C3 | ATTR_1
C3 | ATTR_2

Upvotes: 0

Views: 54

Answers (4)

joop
joop

Reputation: 4503

(Assuming a customers table) reformulating the cross-join:

SELECT c.cust, a.attr
FROM customers c
JOIN attributes a
        ON NOT EXISTS (
        SELECT *
        FROM customer_attributes ca
        WHERE ca.cust = c.cust AND ca.attr = a.attr
        );

Upvotes: 0

www
www

Reputation: 34

Hope Can Help You

Select * Into #Cust From (
    Select 'C1' [IDCustomer], 'ATTR_1' [IDAttributes] Union All
    Select 'C1' [IDCustomer], 'ATTR_2' [IDAttributes] Union All
    Select 'C1' [IDCustomer], 'ATTR_3' [IDAttributes] Union All
    Select 'C2' [IDCustomer], 'ATTR_1' [IDAttributes] Union All
    Select 'C2' [IDCustomer], 'ATTR_2' [IDAttributes] Union All
    Select 'C3' [IDCustomer], 'ATTR_3' [IDAttributes]
) A

Select * Into #Attr From (
    Select 'ATTR_1' [IDAttributes] Union All
    Select 'ATTR_2' [IDAttributes] Union All
    Select 'ATTR_3' [IDAttributes]
) A

Select C.IDCustomer, A.IDAttributes From (
    Select IDCustomer From #Cust
    Group By IDCustomer
) C
Left Join #Attr A On 1 = 1
Left Join #Cust D On D.IDCustomer = C.IDCustomer And D.IDAttributes = A.IDAttributes
Where D.IDCustomer Is Null

enter image description here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If you have a customers table, I would use that:

select c.cust, a.attr
from customers c cross join join
     attributes a left join
     customer_attributes ca
     on c.cust = ca.cust and a.ttr = ca.attr
where ca.cust is null;

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Do a cross join to get all combinations. Use except to remove the existing ones:

SELECT DISTINCT ta.Customer, tb.Attributes
FROM tableA ta CROSS JOIN tableB tb
EXCEPT
SELECT Customer, Attributes
FROM tableA

Upvotes: 2

Related Questions