[FIXED] How can I make a LEFT JOIN, but with rows separed from its relations in MySQL?

Issue

I need to get all rows that are in the table A, but joining with the table B (basically a LEFT JOIN), but also, I need to get the A table row itself, for example, with these tables:

Table A:

id	name
1	Random name
2	Random name #2

Table B:

id	parent_id	location
1	2	Location #1
2	2	Location #2

With this query:

SELECT * FROM A
LEFT JOIN B
ON A.id = B.parent_id;

I get something like this:

id	name	id	parent_id	location
1	Random name	NULL	NULL	NULL
2	Random name #2	1	2	Location #1
2	Random name #2	2	2	Location #2

But I want to get something like this:

id	name	id	parent_id	location
1	Random name	NULL	NULL	NULL
2	Random name #2	NULL	NULL	NULL
2	Random name #2	1	2	Location #1
2	Random name #2	2	2	Location #2

As you can see, there is a row by itself of "Random name #2" separated from its joins, how can I do that?

The main idea is that there are an ads table (the table A), but also, there are a subads table (the table B) with little variations of the ads table, and I need to show all ads and subads in a unique query.

Tanks a lot!

Solution

Two suggestions:

SELECT * FROM A
INNER JOIN B
ON A.id = B.parent_id
UNION ALL
SELECT *, NULL, NULL, NULL FROM A

or

SELECT A.*,B.*
FROM (SELECT 1 A_ONLY UNION ALL SELECT 0) A_ONLY
CROSS JOIN A
LEFT JOIN B
ON A.id = B.parent_id AND NOT A_ONLY
WHERE A_ONLY OR B.parent_id

The latter is an approach you can use to emulate WITH ROLLUP when that isn't allowed or when you want something slightly different than that produces (here, avoiding a grand total record and avoiding a double record when there are no B rows).

Answered By - ysth

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)