CODEWITHSUNDEEP
sqlpostgresql
Mohammed Mortaga
Mohammed Mortaga

Reputation: 139

SQL - Select rows based on filters for two different relations

I'm trying to create a daily menu for my users based on all the meals I have available for their hubs today. Here's what my database looks like: schema

Here's exactly what I want the query to retrieve:

Give me the emails of the users who have daily_email set to True with the information of their company and the hub their company belongs to. 
For each user, I need to see the meals available for them along with the information of the restaurant this meal belongs to.

So far I've tried using INNER JOINS and mixing all tables together then doing the filters I want. It gave me what I wanted but with terrible performance. I'm not mainly looking for the optimal solution, but at least one that has acceptable performance.

Any help would be appreciated.

Upvotes: 0

Views: 38

Answers (1)

forpas
forpas

Reputation: 164099

There is no other way than joining all tables based on their relations:

select u.email, c.name, h.name, m.name, r.name
from users u
inner join companies c on c.id = u.company_id
inner join hubs h on h.id = c.hub_id
inner join hubs_meals hm on hm.hub_id = h.id
inner join meals m on m.id = hm.meal_id
inner join restaurants r on r.id = m.restaurant_id
where u.daily_email

Make sure that all the foreign keys are indexed.

Upvotes: 1

Related Questions