Home » SQL & PL/SQL » SQL & PL/SQL » Join two SQL statements
Join two SQL statements [message #416303] Fri, 31 July 2009 09:11 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
I have two select statements below:

/** select rows from table B that ARE NOT IN table A **/

select SUM(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) column_1
from B LEFT JOIN A on a.id = b.id
where a.id is null;

/** select rows from table B that ARE IN table C **/

select SUM(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) column_2
from c, b
where c.id = b.id;



Would you please to help me to join them in one statement?

select SUM(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) column_1
             , SUM(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) column_2
from ....
     ....


I would appreciate that.
TN
Re: Join two SQL statements [message #416310 is a reply to message #416303] Fri, 31 July 2009 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just put the queries in the from clause.

select column_1, column_2
from (query1), (query2)

Regards
Michel
Re: Join two SQL statements [message #416316 is a reply to message #416303] Fri, 31 July 2009 12:08 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Can I also ask a question? =)

/** select rows from table B that ARE NOT IN table A **/

select SUM(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) column_1
from B LEFT JOIN A on a.id = b.id
where a.id is null;


Correct me if I'm wrong, but why join on A, if you have a filter of "a.id is null" or the join on ID, this is just gonna end up having all rows of B right?

Hope my question is clear =)
Re: Join two SQL statements [message #416317 is a reply to message #416303] Fri, 31 July 2009 12:23 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thanks much, Michel.

However, I tried that before but it gave me a wrong output. Here is my code. Please take a look and see if I made any mistake when join them together.


select 
       SUM(CASE WHEN b1.ID IS NOT NULL THEN 1 ELSE 0 END) ID_B
       , SUM(CASE WHEN b2.ID IS NOT NULL THEN 1 ELSE 0 END) ID_B1
from B b1 LEFT JOIN A a1 on b1.id = a1.id
     , c c2, b b2
where a1.id is null 
and c2.id = b2.id;



Thanks.
LN
Re: Join two SQL statements [message #416318 is a reply to message #416317] Fri, 31 July 2009 12:28 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Whats correct/wrong output? Post what you want the results to be, and post some test data.


I think Michel meant that you put them as inline view, not "join" them.
Re: Join two SQL statements [message #416319 is a reply to message #416303] Fri, 31 July 2009 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>select SUM(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) column_1
>from B LEFT JOIN A on a.id = b.id
>where a.id is null

Query above does NOT accomplish goal stated below

>/** select rows from table B that ARE NOT IN table A **/

The easiest way to accomplish stated goal above is using MINUS
Re: Join two SQL statements [message #416320 is a reply to message #416317] Fri, 31 July 2009 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lamnguyen14 wrote on Fri, 31 July 2009 19:23
Thanks much, Michel.

However, I tried that before but it gave me a wrong output. Here is my code. Please take a look and see if I made any mistake when join them together.


select 
       SUM(CASE WHEN b1.ID IS NOT NULL THEN 1 ELSE 0 END) ID_B
       , SUM(CASE WHEN b2.ID IS NOT NULL THEN 1 ELSE 0 END) ID_B1
from B b1 LEFT JOIN A a1 on b1.id = a1.id
     , c c2, b b2
where a1.id is null 
and c2.id = b2.id;



Thanks.
LN

This is not what I said. Reread my post.

Regards
Michel

Re: Join two SQL statements [message #416321 is a reply to message #416303] Fri, 31 July 2009 12:44 Go to previous message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
I got it.

Thank you very much, Michel.
Previous Topic: Number weeks between two dates which are in same year or different year. (merged)
Next Topic: Loop through table user_tab_cols and execute dynamic SQL for each table
Goto Forum:
  


Current Time: Tue Dec 06 04:38:32 CST 2016

Total time taken to generate the page: 0.20785 seconds