SQL SELECT statement

Question

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";

Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, Sep 12 '16 at 12:45

Mureinik · Accepted Answer · 2016-09-12T12:50:02.947

1

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

edited Sep 12 '16 at 12:50

answered Sep 12 '16 at 12:43

Mureinik

297,002
52
306
350

1

and you could dupehammer – Drew Sep 12 '16 at 12:47
1

Thanks, it works nice. Is it possible to set default values for fields where nothing returned ? (eg. if its random generated ID username field will be blank, and i would like to set the default value to Guest) – rtom Sep 12 '16 at 12:48
@rtom One elegant way would be to use `coalesce`. See my edited answer. – Mureinik Sep 12 '16 at 12:50

score 1 · Answer 2 · answered Sep 12 '16 at 12:45

1

Nearly, just change to left join

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

answered Sep 12 '16 at 12:45

M O'Connell

487
5
18

score 1 · Answer 3 · answered Sep 12 '16 at 12:47

1

The following statement should do it:

SELECT
 orders.id_o,
 orders.user_id,
 orders.price,
 users.username 

FROM orders

LEFT JOIN users 
  ON orders.user_id = users.id

ORDER BY order_date ASC

answered Sep 12 '16 at 12:47

Andreas Rehmer

63
7

SQL SELECT statement

3 Answers3