rtom rtom - 1 year ago 55
SQL Question

SQL SELECT statement

I need to make an sql SELECT statement where I will join tables(or get the value from joined table) only if condition is met, is this possible ?

I have an order table where I have user IDs, but I also have random generated IDs for users who ordered as guests. And I want to join users table on orders table, with that ID, but when the ID is randomly generated I want to return only values from order table because there are not records in user table for that ID.

I have only this, which will write rows where user_id exists in both tables

$sql = "SELECT orders.id_o, orders.user_id, orders.price, users.username
FROM orders JOIN users
ON orders.user_id = users.id
ORDER BY order_date ASC";

Answer Source

This is exactly what left joins are for. To answer the followup question in the comments, you can use coalesce to replace the nulls returned from the left join:

SELECT    orders.id_o, orders.user_id, orders.price, 
          COALESCE(users.username, 'Guest')
FROM      orders
LEFT JOIN users ON orders.user_id = users.id  
ORDER BY  order_date ASC