I have two tables. Table 1 has item, color, and size for all items in the store. Table 2 is a pricing list and has item, size, price, and itemid. Price is determined from item and size. How can I get the price on to Table 1?
Table1- Example
| item | color | size |
|---|---|---|
| shirt | red | M |
| pants | blue | S |
...
Table2- Example
| item | size | price | item_size_id |
|---|---|---|---|
| shirt | S | 2.99 | 013443 |
| shirt | M | 3.99 | 013444 |
| shirt | L | 4.99 | 013445 |
| pants | S | 5.99 | 013452 |
...
Result wanted:
| item | color | size | price |
|---|---|---|---|
| shirt | red | M | 3.99 |
| pants | blue | S | 5.99 |
...
I have tried:
SELECT item, color, size, price
FROM table1
LEFT JOIN table2
ON table1.item = table2.item AND table1.size = table2.size
But this results in all nulls for price.
I have tried CASE WHEN statements and while this worked, it took forever so is there is a better way?
Your query is correct, except that columns of the same name exist in both tables and you have not specified which one you are referring to.
See dbFiddle line below for schema and testing.
/* query provided in the question as provided */
SELECT item, color, size, price
FROM table1
LEFT JOIN table2
ON table1.item = table2.item AND table1.size = table2.size
ERROR: column reference "item" is ambiguous
LINE 2: SELECT item, color, size, price
^
/* query provided with table identifiers added */
SELECT table1.item, table1.color, table1.size, table2.price
FROM table1
LEFT JOIN table2
ON table1.item = table2.item AND table1.size = table2.size
item | color | size | price :---- | :---- | :--- | ----: pants | blue | S | 5.99 shirt | red | M | 3.99
db<>fiddle here