MySQL连接查询，但如果它是重复的，则不显示第一个表的结果

Question

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 1

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.

As @roberto06 suggested, this query returns NULL instead of duplicates, but if you don't like NULLs and want an empty string you can change SELECT tmp.name to SELECT IFNULL(tmp.name,'')