XantorAmnobius XantorAmnobius - 5 days ago 5
SQL Question

SQL Select any other records if exist and select default if no others

I wish to select any other multiple records if it exists (not including the value 9999) - if NO other records exist except one with the value 9999 then only must that one be returned.

In other words, imagine the following:

| FIELD1 | FIELD2 | FIELD3 |
1.| AAA | BBB | 1234 |
2.| AAA | BCB | 1234 |
3.| AAA | ABB | 1234 |
4.| AAA | BDB | 9999 |
5.| AAA | SDD | 1234 |


So, I only want row 1,2,3 and 5 where FIELD1 = 'AAA' and NOT row 4. IF no other rows existed where FIELD1 = 'AAA' and FIELD3 = '9999' then it should return only that one.

Answer

If I've got it right:

SELECT * 
FROM TestTable as T
WHERE 
(
  (
   (Field3 <>9999) 
    AND 
    EXISTS (SELECT * FROM TestTable 
             WHERE Field1 = T.Field1 AND Field3 <>9999)
  )
  OR
  ( 
    (Field3 = 9999) 
    AND 
    NOT EXISTS (SELECT * FROM TestTable 
                 WHERE Field1 = T.Field1 AND Field3 <>9999)
   ) 
 );

For the table:

AAA BBB 1234
AAA BCB 1234
AAA ABB 1234
AAA BDB 9999
AAA SDD 1234
BBB BDB 9999
CCC BBB 1234
CCC BCB 1234
CCC ABB 1234
CCC BDB 9999
CCC SDD 1234

Output will be:

AAA BBB 1234
AAA BCB 1234
AAA ABB 1234
AAA SDD 1234
BBB BDB 9999
CCC BBB 1234
CCC BCB 1234
CCC ABB 1234
CCC SDD 1234
Comments