Joining 3 Tables Inner & Left

Question

Can someone verify if my query is producing the intended results that I am hoping for?

I am using PostgreSQL.

 SELECT sold_at
, quantity
, returned_at
, name
, price
FROM sales
LEFT JOIN returns
ON sales.order_id=returns.order_id
JOIN  products
ON sales.product_id=products.product_id
WHERE name LIKE '%sock%'
ORDER by sold_at ASC

sold_at | quantity | returned_at | name | price

2015-06-01 | 8 | |socks-toeless | 15
2015-06-01 | 7 | 2015-06-08| socks-flat | 15

My goal is for the output to include all sock purchases from the sales table (regardless of whether or not it was returned), and have a column that shows whether the sock order was returned that is populated with a date if it was returned and is blank if the item was not returned.

The name of the product (i.e., 'sock') comes from the product table.

What database are you using? You might just need to change "LEFT JOIN" into "LEFT OUTER JOIN". — Anthony Drogon, Apr 09 '16 at 20:03
Then OUTER is implied, so I guess your query is fine. Is it not working like you expect? — Anthony Drogon, Apr 09 '16 at 20:11
Sample data as well as desired results would help. Your query is unlikely to be producing the results that you intend. — Gordon Linoff, Apr 09 '16 at 20:12

score 0 · Answer 1 · answered Apr 09 '16 at 20:14

0

Your question does not describe what the tables look like. I would expect the returns table to include the product, so I would anticipate a query that looks more like this:

SELECT s.*, p.name,
       (case when r.product_id is null then 0 else 1 end) as IsReturnFlag
FROM sales s JOIN
     products p
     ON s.product_id = p.product_id LEFT JOIN
     returns r
     ON s.order_id = r.order_id AND
        s.product_id = r.product_id
WHERE p.name LIKE '%sock%';

answered Apr 09 '16 at 20:14

Gordon Linoff

1,242,037
58
646
786

*Sales table -- sold_at, order_id, product_id, quantity, user_id *Products table -- product_id, name, price *Returns table -- returned_at, order_id – am340 Apr 09 '16 at 20:21
@am340 . . . How do you know what product was returned, if the order contains multiple products? – Gordon Linoff Apr 09 '16 at 21:48

score 0 · Accepted Answer · answered Apr 09 '16 at 20:26

0

Yes, your query looks like it should work as intended.

answered Apr 09 '16 at 20:26

Anthony Drogon

1,704
1
17
24

Thanks! Within Postgres how would I select only the date from a datetime variable? i manually deleted it above but the DATETIME actually includes hours mins secs. – am340 Apr 09 '16 at 20:34
A simple cast to a "date" works: http://stackoverflow.com/questions/6133107/extract-date-yyyy-mm-dd-from-a-timestamp-in-postgresql Depending on what you want, you also can use PostgreSQL functions like "extract" or "date_trunc". – Anthony Drogon Apr 09 '16 at 22:35

Joining 3 Tables Inner & Left

2 Answers2