CODEWITHSUNDEEP
oracle-databasejoin
dscl
dscl

Reputation: 1626

Oracle/SQL - Join one table multiple times to the same table

I'm trying to wrap my brain around a query and hoping you can help. I have two tables: a customer_table and a product_table that look like this

name    sku_num1    sku_num2    sku_num3    sku_num4
----------------------------------------------------    
Bob     A           B           C           D
Frank   E           A
Tom     G
Shelly  G           E
Justin  E           G           A

sku_num     widget_name
-------------------
A           widget_a
B           widget_b
C           widget_c
D           widget_d

So what I want to do is return a list of all the customers who have at least 1 product whose SKU appears in the product table. So with the above information I would receive back the records for

Bob

Frank

Justin

Any ideas how to do this?

Upvotes: 0

Views: 11028

Answers (4)

Brian Hoover
Brian Hoover

Reputation: 7991

Christopher, Does this mean that you are just looking for any customers where sku_num1 or sku_num2 or sku_num3 or sku_num4 is not null?

If that's the case, you could do it two different ways.

select * from customers 
    where sku_num1 is not null 
    or sku_num2 is not null
    or sku_num3 is not null
    or sku_num4 is not null

If you are trying to see if they have ordered something from a specific product list, you could modify this to:

select * from customers
    where sku_num1 in (select sku_num from skus)
    or sku_num2 in (select sku_num from skus)
    or sku_num3 in (select sku_num from skus)
    or sku_num4 in (select sku_num from skus)

Incidentally, this is why people normally don't structure tables like this. There should be another join table, for lack of anything better to call it, customer_skus which would tie together customers and skus and just have a customerID and a sku_num.

This would make the query easier to write, read, and maintain. The query would look something like:

select distinct name from customers, customer_skus, skus
    where customers.id = customer_skus.id
    and customer_skus.sku_num = skus.sku_num

Upvotes: 1

Stephen ODonnell
Stephen ODonnell

Reputation: 4466

Does something like this not work?

select name 
from customer c, products p
where ( c.sku_num1 = p.sku)
or    ( c.sku_num2 = p.sku)
or    ( c.sku_num3 = p.sku)
or    ( c.sku_num4 = p.sku)

Upvotes: 3

user330315
user330315

Reputation:

There is no need to join one table twice:

SELECT *
FROM customers
WHERE sku_num1 IN (SELECT sku_num
                   FROM product_table)
   OR sku_num2 IN (SELECT sku_num
                   FROM product_table)
   ....

The reason why you need multiple conditions that do a full table scan on the product_table is your wrong database design. The products a customer bought should not go into multiple columns in the customer table. Instead you should have a third table that is a N:M relation between customer and products.

Upvotes: 5

Christopher Armstrong
Christopher Armstrong

Reputation: 7953

You can join a table with itself by using an alias:

select * from mytable a, mytable b where condition

Upvotes: 0

Related Questions