mysql : intersection between 3 or more SELECT's results

Question

ok, suppose we have 3 or more let's say "k" tables with the same structure:
table 1:

id customer_id
1  35
2  36
3  37

table 2:

id customer_id
1  35
2  38
3  39

...
table k:

id customer_id
1  35
2  69
3  52

how can i select all the customer_id present in all my tables ( in other words: the intersection ), in this example if there are 3 tables the result should be like this

id customer_id
1  35

I'll be very grateful if any one could please help

This question does not show any research effort. It is important to **do your homework**. Tell us what you found and ***why*** it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. [FAQ](http://stackoverflow.com/questions/how-to-ask). — Kermit, Apr 05 '13 at 15:19
sorry buddy, i was in a hurry to get an answer, next time :) — Faouzi Abdeljaoued, Apr 05 '13 at 15:44

score 0 · Answer 1 · answered Apr 05 '13 at 15:21

0

SELECT t1.customer_id
FROM `table 1` AS t1
INNER JOIN `table 2` AS t2 ON t1.customer_id = t2.customer_id
-- ...
INNER JOIN `table k` AS tk ON t1.customer_id = tk.customer_id

answered Apr 05 '13 at 15:21

Oswald

31,254
3
43
68

score 0 · Answer 2 · answered Apr 05 '13 at 15:22

This should work:

SELECT table1.id, table1.customer_id
FROM table1, table2, ..., tableK
WHERE table1.id=table2.id
AND table1.id=table3.id
...
AND table1.id=tableK.id

You can also define aliases for each of your tables for simplicity:

FROM table1 a, table2 b, ..., tableK k
WHERE a.id=b.id

score 0 · Answer 3 · answered Apr 05 '13 at 15:25

Try this :

SELECT 
    customer_id
FROM (
    SELECT
        customer_id
        1 as counter 
    FROM 
        table1

    UNION ALL 

    SELECT
        customer_id
        1 as counter 
    FROM 
        table2

    ....

    SELECT
        customer_id
        1 as counter 
    FROM 
        tablek

) as tmp
GROUP BY
    customer_id
HAVING
    SUM(counter) = k

score 0 · Answer 4 · answered Apr 05 '13 at 17:59

create table dela (
    id int
);
create table delb (
    id int
);
create table delc (
    id int
);

drop table dela;

insert into dela values (1),(2),(3);
insert into delb values (5),(4),(3);
insert into delc values (5),(1),(3);

select 
    a.id
from
    dela a
        join
    delb b ON a.id = b.id
        join
    delc c ON a.id = c.id;

mysql : intersection between 3 or more SELECT's results

4 Answers4