JOIN, select 'string' if not found

Question

SELECT p.name, p.price, COALESCE(s.size, 'Medium')
FROM product p
LEFT JOIN sizes s ON p.id = s.product
WHERE p.id = 1

If a product is not mentioned in sizes table, it doesn't select that product?

  id      name      price
  1.     cap         50
  2.     glove       75


 id      product                   size
  1.     2                       'small'
  2.     2                       'large'

If product.id doesn't equal 1, it doesn't select that product. — Mike Sherrill 'Cat Recall', Mar 16 '14 at 11:45
no, i want the product even if it doesn't have a size. Should i make another query and work it out with php? — NestedWeb, Mar 16 '14 at 11:54
Can you provide the test data and result which are requires. — Hamlet Hakobyan, Mar 16 '14 at 11:55
Based on what you've posted, your tables don't directly support the notion of a default size. You can fake it with joins, but you're better off storing data in tables. (As opposed to essentially storing it in a query string.) — Mike Sherrill 'Cat Recall', Mar 16 '14 at 11:58
Your query should work as you expect . . . selecting all products in the product table and defaulting the size to "Medium" if it doesn't exist. — Gordon Linoff, Mar 16 '14 at 13:07

score 0 · Answer 1 · answered Mar 16 '14 at 11:52

what about this:

     SELECT p.name, p.price, IF( EXISTS(
         SELECT size
         FROM sizes s LEFT JOIN product p ON p.id = s.product
         WHERE p.id = 1
          ), s.size, 'Medium') as siz
    FROM product p
    LEFT JOIN sizes s ON p.id = s.product
    WHERE p.id = 1

score -1 · Answer 2 · answered Mar 16 '14 at 12:06

-1

Your query is fine except the WHERE condition

SELECT p.name, p.price, COALESCE(s.size, 'Medium')
FROM product p
LEFT JOIN sizes s ON p.id = s.product

Here is the code at SQL Fiddle Let me know if meant to achieve something else with the query.

answered Mar 16 '14 at 12:06

Dipendu Paul

2,685
1
23
20

To whoever marked it down, I know the solution is same as @NestedWeb posted, I just wanted to add a SQL Fiddle and ask if he wants something else to be done. – Dipendu Paul Mar 16 '14 at 12:13

JOIN, select 'string' if not found

2 Answers2