Table A has the current ValueA and ValueB:
Token ValueA ValueB
ABC SHOP Val1
DEF SHOP Val2
GHI SHIPP Val3
JKL SHIPP Val2
Table B has all the data transitions:
Token Value Value2 Date Express
ABC SHOP Val1 2020-01-01 Yes
ABC. SHOP Val2 2020-02-01 Yes
ABC SHIPP. Val3 2020-03-01 Yes
DEF SHOP Val1 2020-05-01 No
DEF SHIPP Val2 2020-04-01 No
DEF SHIPP. Val3 2020-03-01 No
I expect that my result should include the latest data from TableA but add specific dates from TableB:
Token ValueA Val1 Date Val2 Date
ABC SHOP . 2020-01-01 2020-02-01
DEF SHOP 2020-05-01. 2020-04-01
GHI SHIPP NULL NULL
JKL SHIPP NULL NULL
My query is not returning the result as I expected:
SELECT a.token,
A.valueA,
A.valueB,
B.Express
c.Date "SHOP DATE",
d.Date “SHIP DATE"
FROM TableA A
LEFT JOIN TABLEB B ON A.Token = B.Token
LEFT JOIN TABLEB C ON A.Token = B.Token
LEFT JOIN TABLEB D ON A.Token = B.Token
WHERE (c.value = ‘SHOP’ AND c.value2 = ‘Val1’)
AND (c.value = ‘SHIP’ AND c.value2 = ‘Val13)
GROUP BY a.token
ORDER BY b.date DESC;
Query result:
Token ValueA Val1 Date Val2 Date
ABC SHOP . 2020-01-01 2020-02-01
DEF SHOP 2020-05-01. 2020-04-01
Move the WHERE condition inside JOIN:
SELECT a.token,
A.valueA,
A.valueB,
B.Express
c.Date "SHOP DATE",
d.Date "SHIP DATE"
FROM TableA A
LEFT JOIN TABLEB B
ON A.Token = B.Token
LEFT JOIN TABLEB C
ON A.Token = B.Token
AND c.value = 'SHOP' AND c.value2 = 'Val1'
AND c.value = 'SHIP' AND c.value2 = 'Val3'
LEFT JOIN TABLEB D
ON A.Token = B.Token
GROUP BY a.token
ORDER BY b.date DESC;