I am a noob when it comes to SQL syntax.
I have a table with lots of rows and columns of course :P Lets say it looks like this:
AAA BBB CCC DDD
-----------------------
Row1 | 1 A D X
Row2 | 2 B C X
Row3 | 3 C D Z
Now I want to create an advanced select statement that gives me this combined (pseudo SQLish here):
select 'Test1', * from TABLE Where CCC='D' AND DDD='X'
select 'Test2', * from TABLE Where CCC<>'D' AND DDD='X'
The output would be:
Test1, 1, A, D, X
Test2, 2, B, C, X
How would I combine those two select statements into one nice select statement?
Would it work if I complicated the SQL like below (because my own SQL statement contains an exists statement)? I just want to know how I can combine the selects and then try to apply it to my somewhat more advanced SQL.
select 'Test1', * from TABLE Where CCC='D' AND DDD='X' AND exists(select ...)
select 'Test2', * from TABLE Where CCC<>'D' AND DDD='X' AND exists(select ...)
My REAL SQL statement is this one:
select Status, * from WorkItems t1
where exists (select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01)
AND TimeStamp>'2009-02-12 18:00:00'
which gives me a result. But I want to combine it with a copy of this select statement with an added AND on the end and the 'Status' field would be changed with a string like 'DELETED'.
select 'DELETED', * from WorkItems t1
where exists (select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01)
AND TimeStamp>'2009-02-12 18:00:00'
AND NOT (BoolField05=1)
This question is related to
sql
select
conditional
You have two choices here. The first is to have two result sets which will set 'Test1' or 'Test2' based on the condition in the WHERE clause, and then UNION them together:
select
'Test1', *
from
TABLE
Where
CCC='D' AND DDD='X' AND exists(select ...)
UNION
select
'Test2', *
from
TABLE
Where
CCC<>'D' AND DDD='X' AND exists(select ...)
This might be an issue, because you are going to effectively scan/seek on TABLE twice.
The other solution would be to select from the table once, and set 'Test1' or 'Test2' based on the conditions in TABLE:
select
case
when CCC='D' AND DDD='X' AND exists(select ...) then 'Test1'
when CCC<>'D' AND DDD='X' AND exists(select ...) then 'Test2'
end,
*
from
TABLE
Where
(CCC='D' AND DDD='X' AND exists(select ...)) or
(CCC<>'D' AND DDD='X' AND exists(select ...))
The catch here being that you will have to duplicate the filter conditions in the CASE statement and the WHERE statement.
select t1.* from
(select * from TABLE Where CCC='D' AND DDD='X') as t1,
(select * from TABLE Where CCC<>'D' AND DDD='X') as t2
Another way to do this!
select Status, * from WorkItems t1
where exists (select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01)
AND TimeStamp>'2009-02-12 18:00:00'
UNION
select 'DELETED', * from WorkItems t1
where exists (select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01)
AND TimeStamp>'2009-02-12 18:00:00'
AND NOT (BoolField05=1)
Perhaps that'd do the trick. I can't test it from here though, and I'm not sure what version of SQL you're working against.
use a case into the select and use in the where close a OR
something like this, I didn't tested it but it should work, I think...
select case when CCC='D' then 'test1' else 'test2' end, *
from table
where (CCC='D' AND DDD='X') or (CCC<>'D' AND DDD='X')
Thanks for the input. Tried the stuff that has been mentioned here and these are the 2 I got to work:
(
select 'OK', * from WorkItems t1
where exists(select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01)
AND TimeStamp>'2009-02-12 18:00:00'
AND (BoolField05=1)
)
UNION
(
select 'DEL', * from WorkItems t1
where exists(select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01)
AND TimeStamp>'2009-02-12 18:00:00'
AND NOT (BoolField05=1)
)
AND
select
case
when
(BoolField05=1)
then 'OK'
else 'DEL'
end,
*
from WorkItems t1
Where
exists(select 1 from workitems t2 where t1.TextField01=t2.TextField01 AND (BoolField05=1) )
AND TimeStamp=(select max(t2.TimeStamp) from workitems t2 where t2.TextField01=t1.TextField01)
AND TimeStamp>'2009-02-12 18:00:00'
Which would be the most efficient of these (edit: the second as it only scans the table once), and is it possible to make it even more efficient? (The BoolField=1) is really a variable (dyn sql) that can contain any where statement on the table.
I am running on MS SQL 2005. Tried Quassnoi examples but did not work as expected.
The Union command is what you need. If that doesn't work, you may need to refine what environment you are in.
I think that's what you're looking for:
SELECT CASE WHEN BoolField05 = 1 THEN Status ELSE 'DELETED' END AS MyStatus, t1.*
FROM WorkItems t1
WHERE (TextField01, TimeStamp) IN(
SELECT TextField01, MAX(TimeStamp)
FROM WorkItems t2
GROUP BY t2.TextField01
)
AND TimeStamp > '2009-02-12 18:00:00'
If you're in Oracle or in MS SQL 2005 and above, then you could do:
SELECT *
FROM (
SELECT CASE WHEN BoolField05 = 1 THEN Status ELSE 'DELETED' END AS MyStatus, t1.*,
ROW_NUMBER() OVER (PARTITION BY TextField01 ORDER BY TimeStamp DESC) AS rn
FROM WorkItems t1
) to
WHERE rn = 1
, it's more efficient.
Source: Stackoverflow.com