Select common records from 3 tables 1

[duffyciaran]

Hi - I'm trying to construct a select statement from the following tables(see example)

_________________________________
|Customer | Product | Shop |
|----------|----------|---------|
| | | |
|Customer1 | Milk | Dairy |
| | | |
|Customer2 | Cream | Dairy |
| | | |
|Customer3 | Milk | Dairy |
| | | |
|Customer1 | Trainers | Sports |
| | | |
|Customer2 | Football | Sports |
_________________________________

REQUIRED RESULT SET: Milk, Cream.

Basically I want to select all of the Products (no duplicates) where EVERY customer(1, 2 & 3) have bought a from a common shop type: i.e 'Trainers' and 'Football' should not be selected as Customer3 has not bought any goods from a sports store.

This looks like it should be easy but my SQL isn't the best ;-)

I'd really appreciate any help you could give!!

Cheers!

 

[SantaMufasa]

I'm trying to construct a select statement from the following tables...Customer, Product, Shop.

Do you mind posting a DESCRIBE of the pertinent columns from these three tables?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[duffyciaran]

Hi Mufasa,

This is just an example of a larger problem but the relevent columns are as follows:

CUSTOMER:
customerID

PRODUCT:
productID
customerID
shopID

SHOP:
shopID

 

[SantaMufasa]

So, for the purposes of this exercise, the only relevant table is PRODUCT, right? (...since PRODUCT contains all of the information that exists in both the CUSTOMER and SHOP tables.)

So here are your data:

SQL> select * from product;

CUSTOMERID           PRODUCTID            SHOPID
-------------------- -------------------- ------
Customer1            Milk                 Dairy
Customer2            Cream                Dairy
Customer3            Milk                 Dairy
Customer1            Trainers             Sports
Customer2            Football             Sports

...and here is a query that delivers what you want:

select distinct productid
  from product
 where shopid in (select shopid
                    from (select distinct customerid,shopid
                            from product)
                   group by shopid
                  having count(*) =
                         (select count(distinct customerid)
                            from product));

PRODUCTID
---------
Cream
Milk

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[duffyciaran]

Thanks a million Mufasa - worked perfectly ;-)

You're a legend!

 

[duffyciaran]

Hi Mufasa,

I'm just running this query on an Oracle database where I'm getting a "[Error Code: 907, SQL State: 42000] ORA-00907: missing right parenthesis" error.

I think this has something to do with the AS keyword but not sure. I was testing this on a MySQL DB previously and it worked fine - is there some syntax difference on an Oracle DB??

 

[Thargy]

yes,

Oracle doesn't need the AS keyword for aliasing, it's implicit.

If you say in Oracle

SELECT DUFFY FROM DUFFYTABLE CIARAN WHERE DUFFY IS NOT NULL;

then it's the same as saying
SELECT DUFFY FROM DUFFYTABLE AS CIARAN WHERE DUFFY IS NOT NULL;

Leaving out the AS keyword upsets SQLServer for example, but not Oracle

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

Duffy,

If you continue to have trouble, please post both the full offending statement, along with the error message.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]