LEFT JOIN some data WHERE

Question

I have two mySQL tables (orders and details) and I want to get all orders (including date and amount) of type = sale and status = complete for a specific affiliate.

I could post 20 different ways I have tried so far, but none of them worked and it would be a waste of space. I'm pulling my hair out on this and looking on other stackoverflow questions didn't help.

+--------------------------------------------+
|                    orders                  |
+---------+----------+------------+----------+
| orderID | type     |    date    |  status  |
+---------+----------+------------+----------+
|    1    | sale     | 11/01/2020 | complete |
|    2    | sale     | 12/01/2020 | pending  |
|    3    | sale     | 13/01/2020 | complete |
|    4    | sale     | 14/01/2020 | complete |
|    5    | giveaway | 15/01/2020 | complete |
+---------+----------+------------+----------+

+-----------------------------+
|           details           |
+---------+-----------+-------+
| orderID |    key    | value |
+---------+-----------+-------+
|    1    | amount    |  30   |
|    1    | affiliate |  999  |
|    2    | amount    |  40   |
|    2    | affiliate |  999  |
|    3    | amount    |  60   |
|    3    | affiliate |  333  |
|    4    | amount    |  70   |
|    5    | amount    |  80   |
+---------+-----------+-------+

Expected output for affiliate = 999

+---------+-------------+--------+
| orderID |    date     | amount |
+---------+-------------+--------+
|    1    | 11/01/2020  |     30 |
+---------+-------------+--------+

Answer 1

One option uses two joins:

select o.orderid, o.date, sum(dam.value) amount
from orders o
inner join details daf on daf.orderid = o.orderid 
inner join details dam on dam.orderid = o.orderid
where 
    and o.status = 'complete'
    and daf.key = 'affiliate' and daf.value = 999
    and dam.key = 'amount'

You can also use a single join, and conditional aggregation:

select o.orderid, o.date, 
    sum(case when d.key = 'amount' then d.value else 0 end) amount
from orders o
inner join details d on d.orderid = o.orderid 
where o.status = 'complete'
group by o.orderid
having max(d.key = 'affiliate' and d.value = 999) = 1

Answer 2

You must join orders to 2 copies of details:

select o.orderID, o.date, d2.value amount
from orders o
inner join details d1 on d1.orderID = o.orderID and d1.key = 'affiliate' and d1.value = 999
inner join details d2 on d2.orderID = o.orderID and d2.key = 'amount'
where o.status = 'complete'

See the demo.
Results:

> orderID | date       | amount
> ------: | :--------- | -----:
>       1 | 11/01/2020 |     30