POSTGRESQL QUESTION: 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 I'm thinking there is a better way. Please advise. thank you.

**tables didn't workout the way it showed in the preview question. hope you can still understand it

CodePudding user response：

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