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.
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,'')
This is a question of presenting the query result, so I would opt for PHP, which is better at handling this than MySQL.
Something like this:
select distinct t1.name, t2.id from table1 as t1 outer right join table2 as t2 on t1.id = t2.table1_id
You can try like...
select A.name,B.id from table1 A
right join table2 B on A.id=B.table1_id
What about this?
SELECT
*,
(SELECT GROUP_CONCAT(CONCAT(`id`) separator '|') AS table2_ids
FROM table2 WHERE table1_id = table1.id) AS m
FROM table1
In PHP, you just need to explode('|', $mvar)
Found it !
SELECT (
SELECT
IF(COUNT(t2_2.table1_id) = 1, t1.name, '')
FROM table2 t2_2
WHERE t2_2.id <= t2.id
AND t2_2.table1_id = t1.id
) AS name,
t2.id
FROM table1 t1
LEFT JOIN table2 t2 ON t2.table1_id = t1.id
Results:
name | .id
----------+-----
Test | 1
| 2
| 3
Something | 4
| 5
More | 6
Explanation :
table1_id IN t2_2 where t2_2.id is lower or equal than the actual t2.id and where t2_2.table1_id is equal to t1.id1, 2, 3, 1, 2, 1 in your table examplet1.name if it is evaluated to 1 and an empty string if not.I don't know if it's clear enough, and I'm pretty sure the performance could be enhanced but hey, it works.