CODEWITHSUNDEEP
mysqlsql
hgiesel
hgiesel

Reputation: 5658

Matching over related tables

I have two tables, one with names, nametable:

id | name
---------
1  | alice
2  | bob
3  | charlie

A second with related data, datatable:

id | data
------------
1  | chicken
2  | fish
2  | chicken
3  | spaghetti

Now I want to find matching matching data between two names of nametable:

name1 | name2 | data
--------------------
alice | bob   | chicken

Now I have this:

SELECT nt0.name, nt1.name, nt0.data
FROM nametable AS nt0
INNER JOIN datatable AS dt0
ON nt0.id = dt0.id
LEFT JOIN nametable AS nt1
INNER JOIN datatable AS dt1
ON nt1.id = dt1.id
WHERE dt0.data = dt1.data AND nt0.name < nt1.name;

But it doesn't work. I guess the JOINs are the reason, but I don't know how else to do it.

Upvotes: 0

Views: 30

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do a self join on derived tables:

SELECT
    t1.name, t2.name, t1.data
FROM (
    SELECT n.id, n.name, d.data
    FROM nametable n
    INNER JOIN datatable d
        ON d.id = n.id
) t1
INNER JOIN (
    SELECT n.id, n.name, d.data
    FROM nametable n
    INNER JOIN datatable d
        ON d.id = n.id
)t2
    ON t2.data = t1.data
    AND t2.id > t1.id

Upvotes: 1

Related Questions