SOLVED (see below)
I have the following table (only relevant columns and rows are displayed):
table_1
| result | date | id |
|--------|------------|----|
| A | 12.04.2020 | 42 |
| B | 15.04.2020 | 42 |
| B | NULL | 42 |
| C | NULL | 42 |
| C | 05.04.2020 | 42 |
| A | 23.02.2019 | 43 |
| B | 25.02.2019 | 43 |
| ... | ... | ...|
I have the following statement (it does not do exactly what I want and should be modified):
SELECT result, date, id
-- some joins to get all data from different tables, I hope it is not relevant for this
FROM table_1
WHERE result LIKE '%A%'
AND NOT date = NULL
AND id IN
(
SELECT id
FROM table_1
-- some joins which I also hope are not relevant...
WHERE result LIKE '%B%'
AND NOT date = NULL
);
What is displayed by the query:
| result | date | id |
|--------|------------|----|
| A | 12.04.2020 | 42 |
| A | 23.02.2019 | 43 |
| ... | ... | ...|
What I want to be displayed:
| result | date | id |
|--------|------------|----|
| A | 15.04.2020 | 42 |
| A | 25.02.2019 | 43 |
| ... | ... |... |
The problem:
I don't want the date '12.04.2020' which is the date from the result = A row, but '15.04.2020', which is the date from:
| B | 15.04.2020 | 42 |
So, I need the date which is selected in the subquery:
(
SELECT id
FROM table_1
-- some joins which I also hope are not relevant...
WHERE result LIKE '%B%'
AND NOT date = NULL
)
The real world problem is much more complex, but the relevant part for me should be the same as presented here.
How do I do that?
Thank you very much in advance!
Solution:
(Thanks to @markp-fuso who gave the correct and precise answer, see below, and thanks to @Andy3B who understood my problem - due to my poor description - and gave basically the right answer!)
A self join.
SELECT t1.result, t2.date, t1.id
FROM table_1 t1
JOIN table_1 t2 ON t1.id = t2.id
WHERE t1.result LIKE '%A%'
AND t1.date IS NOT NULL
AND t2.result LIKE '%B%'
AND t2.date IS NOT NULL;
Assumptions:
id
value there is at most 1x row where result=A
and at most 1x row where result=B
(otherwise OP needs to provide more details on which row(s) are desired if there are 2+ matching rows)result=B
then display a NULL (otherwise OP will need to provide more details if a matching result=B
row does not exist)We'll let the main query find all the rows with result=A
, and a sub-query (in the select/projection list) to find the matching row with result=B
, eg:
select t1.result,
(select max(t2.date)
from table_1 t2
where t2.id = t1.id
and t2.result like '%B%'
and t2.date is not NULL) as 'date',
t1.id
from table_1 t1
where t1.result like '%A%'
and t1.date is not NULL
NOTE: The max(t2.date)
assumes that we want the 'latest' date
if there are 2+ matching rows with result=B
; this also assumes the date
column is of type (small)datetime
Updating Andy3B's answer to pull values from 2x different rows (the difference in queries is with the first line, the select/projection list):
SELECT T1.result, T2.date, T1.id
FROM table_1 T1
JOIN table_1 T2 ON T2.ID = T1.ID
WHERE
T1.result LIKE '%A%'
AND T1.date IS NOT NULL
AND T2.result LIKE '%B%'
AND T2.date IS NOT NULL
NOTE: The equijoin assumes there will always be (at least) one row with result=B
to go with each row where result=A
.