March 12, 2019 at 7:41 am
I wanted to retrieve single rows of two columns based on a certain criteria after joining multiple tables. To explain it with example, I've this:
SELECT c.column1, c.column2, d.column3, d.column4
FROM table1 a
JOIN table2 b ON a.id=b.id
JOIN table3 c ON b.tabid = c.tabid
LEFT JOIN table4 d ON c.pmid=d.pmid
WHERE a.id = @id
In the case column1 and column2 are not NULL, I want two of them be retrieved as
SELECT c.column1, c.column2
FROM table1 a
JOIN table2 b ON a.id=b.id
JOIN table3 c ON b.tabid = c.tabid
LEFT JOIN table4 d ON c.pmid=d.pmid
WHERE a.id = @id
Otherwise, I would like to have
SELECT d.column3, d.column4
FROM table1 a
JOIN table2 b ON a.id=b.id
JOIN table3 c ON b.tabid = c.tabid
LEFT JOIN table4 d ON c.pmid=d.pmid
WHERE a.id = @id
I would use IF clause with COUNT function to first see individually whether the columns are null or not, then would use a plain SELECT statement to retrieve. But it will be a triple effort of reading from the same table 3 times (to check counts of each column is > 0 or not; in the case both are true, to select from those columns). I believe it can be enhanced better.
I was also considering to use two separate common table expressions to use with CASE. But ended up with syntactical error.
Any guidance will be appreciated. Thank you!
March 12, 2019 at 8:52 am
Sounds like you already spelled out the solution 🙂 Use a case statement https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017
March 12, 2019 at 9:12 am
Sounds like you want some logic like this:SELECT IIF(c.column1 IS NULL AND c.column2 IS NULL,d.column3,c.column1) col1,
IIF(c.column1 IS NULL AND c.column2 IS NULL,d.column4,c.column2) col2
FROM table1 a
JOIN table2 b ON a.id=b.id
JOIN table3 c ON b.tabid = c.tabid
LEFT JOIN table4 d ON c.pmid=d.pmid
WHERE a.id = @id
March 12, 2019 at 10:20 am
Here is another option. This approach probably becomes more attractive as you add more columns to the conditions.
SELECT c.column1, c.column2
FROM table1 a
JOIN table2 b ON a.id=b.id
JOIN table3 c ON b.tabid = c.tabid
LEFT JOIN table4 d ON c.pmid=d.pmid
CROSS APPLY
(
SELECT c.column1, c.column2
WHERE c.column1 IS NOT NULL OR c.column2 IS NOT NULL
UNION ALL
SELECT c.column3, c.column4
WHERE c.column1 IS NULL AND c.column2 IS NULL
) e(column1, column2)
WHERE a.id = @id
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply