I have 2 tables. One is a generic price list for all customers, the other is a special price list for selected customers.
An example of them:
Special List
ID | STOCK_CODE | PRICING_REF | PRICE
1 | 1234 | CUST001 | 1.00
2 | 1235 | CUST001 | 2.00
Generic List
ID | STOCK_CODE | PRICE
1 | 1234 | 4.99
2 | 1241 | 4.99
3 | 1249 | 5.99
I would like to joint the 2, which I am currently doing. However, rather than have just one list displayed I'd like to merge the 2 but only show the price from the special list, if the stock code matches and there is a price available in that row.
My statement so far is:
SELECT * FROM stock s LEFT JOIN price p ON p.stock_code = s.stock_code WHERE PRICING_REF = '$pricing_ref' AND s.stock_code = '$ref'
This only returns the price of items from the special list, missing the remaining items from the generic list.
Currently, if I do a search for items : '%1%' it will return stock_code 1234 and 1235, but not 1241 and 1249 which I'd also like included. However, the price for 1234 must come from the Special List, not the Generic List.
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire