Loko Loko - 23 days ago 4
MySQL Question

MySQL join query but dont show results from the first table if it's a duplicate

So basically I have 2 tables:

table1

id| name
1 Test
2 Something
3 More


table2

id| table1_id
1 1
2 1
3 1
4 2
5 2
6 3


Now I need the result to be like this:

name | table2.id
Test 1
2
3
Something 4
5
More 6


So basically no duplicate entries from the first table. So the exact same results as joining it but without showing the name more than once. Is this possible in MySQL or should I just filter it in PHP? I know it's possible in PHP but I am wondering if something like this is achievable in MySQL if so, I'd like to know what to look for. I was thinking something with DISTINCT and/or a left or right join.

Answer

So, you asked if it is possible with MySQL and I answered in comments that it is. If your question was how can I accomplish this with only MySQL, here it is:

SELECT
tmp.name,
tbl2.id
FROM
    tbl2
LEFT JOIN (
SELECT
    tbl2.id AS id,
    tbl1.`name` AS name
FROM
    tbl2
INNER JOIN tbl1 ON tbl1.id = tbl2.tbl1_id
GROUP BY
    tbl2.tbl1_id
) AS tmp ON tbl2.id = tmp.id;

Hope it is what you wanted.

Comments