How to combine taking several joins and add constraints on query?-CodePudding

How can I answer the following question by quering this database:

The police is looking for a brown hair coloured woman that checked in in the gym somewhere between september 8th 2016 and october 24th 2016.This woman has a silver membership. Can we find the name of this woman?

I tried the following query:

dbGetQuery(db,"
SELECT *
FROM get_fit_now_member
JOIN get_fit_now_check_in ON id = membership_id
WHERE check_in_date BETWEEN '20160909' AND '20161023' AND membership_status = 'silver'
")

This gives me the following output:

The problem is that I have to join multiple times and at the same time have to add different constrains. How can I solve this question in a clever way?

CodePudding user response：

Here's how I would write the query:

SELECT m.name 
FROM get_fit_now_member AS m
JOIN get_fit_now_check_in AS c ON m.id = c.membership_id 
JOIN person AS p ON m.person_id = p.id
JOIN drivers_license AS d ON p.license_id = d.id
WHERE c.check_in_date BETWEEN '20160908' AND '20161024' 
AND m.membership_status = 'silver'
AND d.hair_color = 'brown';

JOIN is just an operator, like is in arithmetic. In arithmetic, you can extend the expressions with more terms, like a b c d. In SQL, you can use JOIN multiple times in a similar way.

I used correlation names (m, c, p, d) to make it more convenient to qualify the table names, so I can be clear for example which id I mean in each join condition, since a column named id exists in multiple tables.

I also changed the date expression, because I assume "between" is meant to include the two dates named in the problem statement.