A SQL Question

[maccten2000]

I have a SQL problem

1) I have a customer who has multiple attributes in a table

2) There is more than one customer in the table – so many customers many attributes

3) I need a query (this will involve a sub query) which will take all the customers in the aforementioned Table and spit back a list of Attributes they don’t have



Example


Customer Attribute

Customer 1 Sleek

Customer 1 Doesn’t eat Salads

Customer 2 Owns a car

Customer 3 Owns a Bike

Customer 4 etc etc


Customer 4 can have an attribute that customer 2 lacks so would appear in the list for Customer 2 and Not Customer 4

Can Anyone Help?

Thanks Very Much For your Time

 

[SkipVought]

Do you have a table of Customer Attributes, or is Customer Attribute completely open ended?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maccten2000]

It is a single Table which contains Customer Name and Attributes. So customer Name can be repeated multiple times (This is handled via unique ID numbers)

I only need to return the customer names and attributes they lack.

Is this clear?


Thanks very much for your time on this

 

[SkipVought]

So Attribute is completely open ended.

You might enter

Doesn't eat Salads
Doesn't eat Salad
No Salad
Does not like salad
No greens
or???????

for instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Golom]

Skip is right of course ... free-form entry can result in all sorts of things that may not match exactly but have the same meaning.

Assuming that exact matches are all you care about ... try this

SELECT DISTINCT C1.customer, C2.attribute
FROM myTable AS C1 INNER JOIN myTable As C2
    ON C1.Customer <> C2.Customer 

Where C2.Attribute NOT IN 
(Select C3.Attribute From myTable C3 
 Where C3.Customer = C1.Customer)

 

[Golom]

Just a note of caution. The above is a theta-join with a coordinated sub-query.

Less technically, it may be slow if your tables are large.

 

[maccten2000]

Thanks very much, this is exactly what i was looking for

The Tables wont be large so this will work really well

I hope you have a good weekend

Cheers