Ika Ika - 4 months ago 9
MySQL Question

Returning value from a diferent row in a CASE

I don't know if it is possible, but I thought I could ask

I have a table with families and subfamilies (family_table):

family_id family_name parent_id
1 Family 1 null
2 Family 2 null
3 Subfamily 1.1 1
4 Subfamily 1.2 1
5 Family 3 null
6 Subfamily 2.1 2


Then I have a complicated SELECT that returns some family_id, like 2, 4.

Then I would need to return the following information:

principal_id family_name subfamily_id subfamily_name
2 Family 2 null null
1 Family 1 4 Subfamily 1.2


EDIT: I wouldn't mind changing how the results are shown or changing something else as long as it is clear enough.

Here it's what I've done so far:

SELECT
CASE
WHEN ft.parent_id IS NULL THEN
ft.family_id
ELSE
ft.parent_id
END AS principal_id,
/*I was thinking something like this, but I don't know how to select another row value here, or if there would be another way
CASE
WHEN ft.parent_id IS NULL THEN
ft.family_name
ELSE
*Family name from the parent*
END AS family_name*/
FROM
family_table ft
LEFT JOIN family_table ft2 ON ft2.parent_id = ft.family_id
WHERE
ft.family_id IN (2,4/*complicated select*/)


EDIT: THIS ATTEMPT BELOW WOULDN'T WORK, AS IT RETURNS THE RESULTS FROM A SUBFAMILY AS A FAMILY, and it doesn't join the parent ID from the subfamily, which I need.

Or maybe something like this:

SELECT
ft.family_id as principal_id,
ft.family_name as family_name,
ft2.family_id as subfamily_id,
ft2.family_name as subfamily_name
FROM ft.family_table
LEFT JOIN ft2.family_table ON ft2.parent_id = ft.family_id
WHERE
/*ft.family_id OR ft2.parent_id*/ IN (2,4/*complicated select*/)


I don't want to repeat the monstrous select by doing
ft.family_id IN (2,4/*complicated select*/) OR cf2.parent_id IN (2,4/*complicated select*/)
.
I have the feeling that I am over-complicating everything...

I'd appreciate your help. Thank you!




HERE IT IS THE FINAL QUERY, TAKING THE ACCEPTED ANSWER INTO ACCOUNT (in case someone was wondering)

SELECT
COALESCE (ft2.family_id, ft.family_id) AS family_id,
COALESCE (ft2.family_name, ft.family_name) AS family_name,
CASE WHEN ft.parent_id IS NOT NULL THEN ft.family_id
END AS subfamily_id,
CASE WHEN ft.parent_id IS NOT NULL THEN ft.family_name
END AS subfamily_name
FROM family_table ft
JOIN (/* complicated select */) AS t ON t.family_id IN (ft.family_id, ft.parent_id)
LEFT JOIN family_table ft2 ON ft2.family_id = ft.parent_id

Answer

Try joining to the "complicated select" instead of using IN() , something like this:

SELECT
.....
FROM
    family_table ft
LEFT JOIN family_table ft2 ON ft2.parent_id = ft.family_id
JOIN ( /* complicated select here */ ) t
 ON(t.ID_OR_WHATEVER IN(ft.family_id,cf2.parent_id))

This will have the same effect.