CODEWITHSUNDEEP
sqlsql-server
Roms
Roms

Reputation: 105

Joining two tables and two columns

I have articles and discounts table. Articles could have family and subfamily (or not, could be blank).

Articles:

id | name | price | family | subfamily
1  | art1 | 5     | F01    | A02
2  | art2 | 5.5   | F02    |

Discounts

id | customer_id | family | subfamily | discount
1  | ABC123      | F01    | A02       | 40%
2  | CBD321      | F02    |           | 30%

I need to retrieve articles with their discount based on: customer_id (customer who will do order) family and subfamily. Articles could have discount based on his family only (subfamily is blank) or on his family and subfamily but both need to be associed to customers id on the table. If one articles doesnt match anything, his discount would be null for that customer.

How can I do this? I tried this, but only can retrieve rows as if it were INNER JOIN

SELECT
  a.*,
  d.discount
FROM articles a
LEFT JOIN discounts AS d
  ON a.family = d.family
  AND a.subfamily = d.subfamily
WHERE d.customer_id = 'ABC123'

Upvotes: 0

Views: 65

Answers (1)

jarlh
jarlh

Reputation: 44696

Move the discounts condition from WHERE to ON to get true LEFT JOIN result:

SELECT a.*, d.discount
FROM articles a
LEFT JOIN discounts AS d ON a.family = d.family AND a.subfamily = d.subfamily
  AND d.customer_id = 'ABC123'

Upvotes: 2

Related Questions