Suppose I have a table like this:
Example data:
x_id | type | user_id | other_id | date
----------------------------------------------
123 | AB | 999 | 001 | 10/12/12
124 | CD | 887 | 258 | 12/12/21
125 | CD | 651 | 702 | 03/04/11
126 | AB | 114 | 003 | 01/19/01
127 | EF | 573 | 777 | 02/08/17
128 | AB | 221 | 145 | 07/21/94
129 | CD | 999 | 001 | 10/12/12
130 | CD | 573 | 777 | 10/10/99
131 | EF | 114 | 003 | 03/02/97
132 | EF | 000 | 914 | 01/01/82
I want to select records with a type
of AB
, unless that type does not exist for a record. If that's the case, then I would want the oldest record, regardless of the type
. For example, this is the result I'm looking for:
Desired result:
x_id | type | user_id | other_id | date
----------------------------------------------
123 | AB | 999 | 001 | 10/12/12
124 | CD | 887 | 258 | 12/12/21
125 | CD | 651 | 702 | 03/04/11
126 | AB | 114 | 003 | 01/19/01
128 | AB | 221 | 145 | 07/21/94
130 | CD | 573 | 777 | 10/10/99
132 | EF | 000 | 914 | 01/01/82
I started out by seeing if I could simply return records where the type
of AB
does not exist via the query below:
SELECT t1.x_id
,t1.type
,MAX(t1.user_id)
,t2.other_id
,MIN(t1.date)
FROM TBL_1 t1
LEFT JOIN TBL_2 t2 ON t1.y_id = t2.y_id
WHERE NOT EXISTS (
SELECT * FROM TBL_1 t1a
WHERE t1.user_id = t1a.user_id
AND t1.type LIKE '