How do I combine 2 select statements into one

Question

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 lt  gt  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 lt  gt  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 gt  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 gt  2009-02-12 18 00 00  AND NOT  BoolField05 1

User · Answer

If they are from the same table  I think UNION is the command you re looking for    If you d ever need to select values from columns of different tables  you should look at JOIN instead

User · Answer

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 gt  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 gt  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

User · Answer

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 gt  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 gt  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 gt  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

User · Answer

select t1   from   select   from TABLE Where CCC  D  AND DDD  X   as t1   select   from TABLE Where CCC lt  gt  D  AND DDD  X   as t2   Another way to do this

User · Answer

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  gt   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

User · Answer

The Union command is what you need   If that doesn t work  you may need to refine what environment you are in

User · Answer

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 lt  gt  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 lt  gt  D  AND DDD  X  AND exists select      then  Test2      end         from      TABLE  Where       CCC  D  AND DDD  X  AND exists select       or      CCC lt  gt  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

User · Answer

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 lt  gt  D  AND DDD  X

[sql] How do I combine 2 select statements into one?

Examples related to sql

Examples related to select

Examples related to conditional