0

I have two tables. I want to combine them but include rows that don't return a value. This question is probably going to be flagged as duplicate or something, but I have already tried reading the other posts and still failed. So I might be below the average MySQL Programmer. Hope somebody can help.

table_price_list

item_id price_type_id   price_amount
1       1               100.00
1       2               95.00
1       3               90.00
1       4               85.00
1       5               80.00
1       6               75.00
2       1               201.56
2       2               196.45
2       3               191.78
2       4               186.36
3       1               1210.12
3       2               1205.45
3       3               1200.69
3       4               1195.48
3       5               1190.98

table_price_type

price_type_id   price_type
1               srp
2               reseller
3               distributor
4               mega
5               depot
6               special

Desired output

item_id price_type_id   price_type
1       srp             100.00
1       reseller        95.00
1       distributor     90.00
1       mega            85.00
1       depot           80.00
1       special         75.00
2       srp             201.56
2       reseller        196.45
2       distributor     191.78
2       mega            186.36
2       depot           null
2       special         null
3       srp             1210.12
3       reseller        1205.45
3       distributor     1200.69
3       mega            1195.48
3       depot           1190.98
3       special         null

The best I could get so far is this, this leaves out the blank price_type

 select b.item_id, a.price_type, b.price_amount
    from table_price_type A
    left outer join table_price_list B on A.price_type_id=B.price_type_id

It doesn't have to be null, it could just be blank (' ').

YinYangKim
  • 31
  • 3
  • 12
  • Why are the same item_ids? ids should be unique. The price type column has strings, not values, like in the desired output. Have you tried simple left join? (without "outer"?). Your two tables have the same name? – kry Aug 02 '18 at 11:55
  • Do you want to replace `null` with any particular string(In your case blank space)? – WASEEM Aug 02 '18 at 12:00
  • @kry the item_ids are unique in another table containing all the items. I didn't think it was needed so i didn't post it here. The table you are looking at have 2 primary keys. The item_id and price_type. Sorry about the two tables having the same name, that was a typo. Already edited. – YinYangKim Aug 02 '18 at 12:03
  • @WASEEM it could be `null` or it could be blank. As long as it is included as shown in the desired output. – YinYangKim Aug 02 '18 at 12:04

1 Answers1

0

You can use left join to get required result.

select b.item_id, a.price_type, b.price_amount
from table_price_type A
left join table_price_list B on A.price_type_id=B.price_type_id
jmsds
  • 225
  • 1
  • 7
  • Sorry, but the output for this leaves out the price_type that are blank/null. – YinYangKim Aug 02 '18 at 12:05
  • Because you do not have "null" values in your price_list table. I think you want the full outer join: https://stackoverflow.com/questions/7978663/mysql-full-join – kry Aug 02 '18 at 12:14
  • I see... thanks for the reference. I'll try to see what I can do with the pseudo FULL JOIN using union all. – YinYangKim Aug 02 '18 at 12:32