Search code examples
sqlsybase

SQL - Display value selected in WHERE <column> IN clause


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; 

Solution

  • Assumptions:

    • for a given 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)
    • if there is no matching row with 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.