Getting an output based on conditions and creating a new column and updating it based on a condition in SQL

We have two tables: “riders” and “drivers”. Some riders are also drivers, so will show up on both tables. Each table looks like this:

Table riders

City | user_id | signup_date
SF   | u1      | 3/12
CH   | u2      | 12/12
SF   | u3      | 5/10

I have tried this code:

SELECT riders.id, 'flag' as flag FROM riders INNER JOIN drivers ON riders.id = drivers.id where riders.City ='SF';

Write a query that outputs all SF riders and add a column called ‘flag’ that prints ‘true’ if the rider is also a SF driver and ‘false’ if the rider is not an SF driver.

My output should look like a column of user_id and flag

user_id | flag
u1      | true    if u1 is also in Table 'drivers'
u3      | false   if u3 is not in Table 'drivers' but in City SF

Answer

You are on the right track, but your query has two problems:

• The definition of flag is just a constant.
• Your join conditions are not correct.

You need to JOIN the tables on the user_id and then check if there is a match in the second table:

SELECT r.user_id, (d.user_id is not null) as flag
FROM riders r LEFT JOIN
     drivers d
     ON r.user_id = d.user_id
WHERE riders.City = 'SF';

If you want true and false as strings:

(CASE WHEN d.user_id IS NOT NULL THEN 'true' ELSE 'false' END)