join two tables based on priority and union

Question

I am stuck and hoping that someone can help me. I would like to join the two query’s below but I not know how.

Each article should have discount attached to it. The discount can be based on the article code, a brand, a category or a letter and the query should only display one discount price or percentage per article.

Is this possible?

Discount table

(SELECT type, dicountprice, percentage, 1 priority FROM discount_table
WHERE code = 'artcode' AND discountcode = '11')
union
(SELECT type, dicountprice, percentage, 2 priority FROM discount_table
WHERE code = 'brand' AND discountcode = '11')
union
(SELECT type, dicountprice, percentage, 3 priority FROM discount_table
WHERE code = 'category' AND discountcode = '11')
union
(SELECT type, dicountprice, percentage, 4 priority FROM discount_table
WHERE code = 'letter' AND discountcode = '11')
ORDER BY priority DESC

---

Product table

 SELECT artkod, price, image FROM product_table

---

an example

discount_table
id | discountcode | pricelevel | code  |   type      | dicountprice | percentage
---------------------------------------------------------------------------------
1  |    11        |      1     | acd-re| percentage  |              |    30
2  |    11        |      2     |  hp   | percentage  |              |    10
3  |    11        |      4     |  D    |   dollar    |      200     |   


product_table
id | artkod     | lettercode | brand     |  price   |  image
----------------------------------------------------------------
1  | acd-re     |     D      | samsung   |   300    |   noimage
2  | qdpoll123  |     C      |   hp      |   40     |   noimage
3  | add-345-v  |     D      | samsung   |   600    |   noimage


should result in   
rows
acd-re     | 300  | 30%
qdpoll123  |  40  | 10%
add-345-v  | 600  | 200 dollar

Many thanks Linda

Answer 1

I am not shure that i have fully understood the link between your two tables. But if it is a 1:1 relation, the following should do the job :

select 
    d.code,
    p.price,
    IF(d.discountprice is null,
        concat(d.percentage, ' %'),
        concat(d.discountprice, ' $')) as discount,
    case d.code
        when p.artkod then 1
        when p.brand then 2
        when p.lettercode then 3
    end as priority
from
    product as p
        left join
    discount as d ON d.id = p.id
order by priority desc

Here is what it give :

code   price discount priority
D      600   200 $    3
hp     40    10 %     2
acd-re 300   30 %     1

I think you should consider creating a table referencing the different discount types with related rules. Using indexes will make you gain in ressources. Specially if you have a lot of records to handle.

Or maybe you could just place indexes on artkod, brand and lettercode if you can't really modify the schema.