Ggtcf

I have a table with a column called ReportTypeId. I want to select rows where ReportTypeId = 1, but if no rows exist for this, then I want ReportTypeId = 2. I am trying to use a WHEN EXISTS, but I cannot figure out how to select more then one column. I want to write a query that looks something like this:

SELECT CASE 
 WHEN EXISTS (SELECT PerformanceReport FROM ReportData
 WHERE (ReportId = 79 and ReportTypeId = 1))

 THEN (select * from ReferenceData 
 where ReportTypeId = 1)

 ELSE (select * from ReferenceData
 where ReportTypeId = 2)
 END

But because I am trying to return more than one column it doesn't work. Is there a way to create a query that bases it's WHERE statement based on whether the data exists or not?

If RecordTypeId can be ordered**, then you can use the following

SELECT PerformanceReport 
FROM ReferenceData
WHERE ReportTypeId = (
 SELECT MIN(ReportTypeId) 
 FROM ReportData 
 WHERE ReportTypeId IN (1, 2)
 AND ReportId = 79
)

** by ordered, I mean that the MIN aggregate function will return the expected result. For integers this makes sense, however, if your report type ids are text uuids, then MIN would still work but won't give you the expected result, because it will return the minimum id in lexical order.

You can use the following solution:

SELECT * 
FROM ReferenceData
WHERE (
 ReportTypeId = 1 
 AND EXISTS (SELECT * FROM ReportData WHERE ReportTypeId = 1 AND ReportId = 79)
) OR (
 ReportTypeId = 2
 AND NOT EXISTS (SELECT * FROM ReportData WHERE ReportTypeId = 1 AND ReportId = 79)
)

You can optimize this with a JOIN:

SELECT ReferenceData.* 
FROM ReferenceData JOIN (
 SELECT EXISTS (SELECT * FROM ReportData WHERE ReportTypeId = 1 AND ReportId = 79) AS state
) isAvailable
WHERE (
 ReportTypeId = 1 AND isAvailable.state = 1
) OR (
 ReportTypeId = 2 AND isAvailable.state = 0
)

You can add multiple checks using the JOIN:

SELECT ReferenceData.* 
FROM ReferenceData JOIN (
 SELECT EXISTS (SELECT * FROM ReportData WHERE ReportTypeId = 1 AND ReportId = 79) AS state
) avail1 JOIN (
 SELECT EXISTS (SELECT * FROM ReportData WHERE ReportTypeId = 2 AND ReportId = 79) AS state
) avail2 JOIN (
 SELECT EXISTS (SELECT * FROM ReportData WHERE ReportTypeId = 3 AND ReportId = 79) AS state
) avail3
WHERE (
 ReportTypeId = 1 AND avail1.state = 1
) OR (
 ReportTypeId = 2 AND avail1.state = 0 AND avail2.state = 1
) OR (
 ReportTypeId = 3 AND avail1.state = 0 AND avail2.state = 0 AND avail3.state = 1
) OR (
 ReportTypeId = 4 AND avail1.state = 0 AND avail2.state = 0 AND avail3.state = 0
)

demo on dbfiddle.uk

You seem to want:

SELECT refd.* 
FROM ReferenceData refd
WHERE (refd.ReportType = 1 AND 
 EXISTS (SELECT 1
 FROM ReportData repd
 WHERE repd.ReportId IN (1, 79)
 )
 ) OR
 (refd.ReportType = 2 AND 
 EXISTS (SELECT 1
 FROM ReportData repd
 WHERE repd.ReportId NOT IN (1, 79)
 )
 )