SQLAlchemy - Querying Multiple Tables and Combining Results

Question

Answer no longer needed.

I have two tables as follows in my MySQL database:

**Child**
id
name
parent_id

**Parent**
id
name

What I want to do is query both tables but return unique results (no duplicates.) I want both tables to be searched where Child.name is LIKE %query% and return all the Child elements, but I also want to return all the children of Parent where Parent.name is LIKE %query%. I believe a JOIN may work, but I'm not familiar with how to use JOIN.

Here is some example data and output:

Child:
id          1
name        Jesse
parent_id   1

Child:
id          2
name        Walter Jr.
parent_id   1

Parent:
id          1
name        Skylar

If a user queries "Jesse", it will return one result - Child where name is LIKE %Jesse%. If a user queries "Skylar", it will return two results - Child where parent_id = Parent.id WHERE Parent.name is LIKE %Skylar%, thus returning both Children belonging to Skylar.

Thank you in advance!

EDIT: Also to note, I have Child backref'd to Parent.

If you do not need an answer anymore, please delete the question, as it will serve no purpose for the others. Or provide an answer which solved your question and accept it. — van

eggyal eggyal · Accepted Answer · 2012-07-02T02:38:54

You can use UNION to combine matches from one query (on just the child table) with matches from a second query (on the parent table joined with the child table):

SELECT id, name
FROM   Child
WHERE  name LIKE '%query%'

UNION

SELECT Child.id, Child.name
FROM   Parent JOIN Child ON Child.parent_id = Parent.id
WHERE  Parent.name LIKE '%query%'

SQLAlchemy - Querying Multiple Tables and Combining Results

1 Answers