rtom rtom - 2 months ago 7
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

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
Comments