Conditional IF ELSE Table Selection in Subquery in WHERE Clause

Question

I have to query some results from a table recursively, so I think I need to have a where clause that branches if one condition is need to match parent-child on id else match parent-child on another field. I have looked into the CASE and IF functions but they do not work as I want to.

Something like:

SELECT * from table1 AS parent,
JOIN jointable AS table2
ON parent.id=table2.parent_id
WHERE
IF (SELECT * FROM table1 WHERE id=parent.id, GET CONDITION RESULT, ELSE CONDITION RESULT);

I don't know if something like that will work in MySQL?

I want to get parent of selected row based on one of either parentId or id, therefore I want to run an subquery based on if condition result - to return either parentId or id matched parent row data.

Answer 1

Using UNION and LIMIT

If you want to select from table1 if it exists, but select from table2 instead if it exists, and do it all in one MySQL query, one way is to combine UNION with LIMIT.

(SELECT * from table1 AS parent,
JOIN jointable AS table2
ON parent.id=table2.parent_id)
UNION
(Whatever Your Other Query Is)
LIMIT 1

NOTE: You'll need to have matching fields on both tables. But I assume this is already naturally the case since you are selecting on parent_id, which means both tables must have that field.

Using ISNULL

Use isNull() with an if statement. If you want to use a field from one table if it exists, then another if it doesn't exist on the first, try IF(ISNULL(table1.parent_id), table2.parent_id, table1.parent_id). You don't go into detail about what your other condition is, so I have no idea how to implement this for your case, but it's possible it'll work.