CODEWITHSUNDEEP
mysqljoinsqlalchemyflask-sqlalchemy
user1492385
user1492385

Reputation: 105

SQLAlchemy - Querying Multiple Tables and Combining Results

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.

Upvotes: 0

Views: 677

Answers (1)

eggyal
eggyal

Reputation: 126035

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%'

Upvotes: 1

Related Questions