?
Solved

LEFT JOIN Two Select Statements on the Same Table

Posted on 2007-10-19
4
Medium Priority
?
3,873 Views
Last Modified: 2010-08-05
I'm having the following issue with multiple select statements on the same table.
Trying to get them to return the count produced by each statement as one result.

Select 1

SELECT     COUNT(CASE_ID) AS ORDER_COUNT, LEFT(INS_ZIP, 5) AS ZIP
FROM         Case
WHERE     (EXP_DT > '01/01/2007') AND (EXP_DT < '10/01/2007') AND (INS_ZIP IS NOT NULL) AND (INS_ZIP <> '') AND (LEN(INS_ZIP) = 5)
GROUP BY LEFT(INS_ZIP, 5)

Select 2

SELECT     COUNT(CASE_ID) AS COMP_COUNT, LEFT(INS_ZIP, 5) AS ZIP
FROM         Case
WHERE     (COMP_DT > '01/01/2007') AND (COMP_DT < '10/01/2007') AND (INS_ZIP IS NOT NULL AND INS_ZIP <> '' AND len(INS_ZIP) = 5)
GROUP BY LEFT(INS_ZIP, 5)

I'd like to LEFT JOIN these two statements together based on INS_ZIP.
Like:
ZIP         ORDER_COUNT      COMP_COUNT
17042    10                           5

Any help would be greatly appreciated.




0
Comment
Question by:cbish21577
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20111996
select coalesce(q1.zip, q2.zip) as zip, q1.order_count , q2.comp_count
from (
SELECT     COUNT(CASE_ID) AS ORDER_COUNT, LEFT(INS_ZIP, 5) AS ZIP
FROM         Case
WHERE     (EXP_DT > '01/01/2007') AND (EXP_DT < '10/01/2007') AND (INS_ZIP IS NOT NULL) AND (INS_ZIP <> '') AND (LEN(INS_ZIP) = 5)
GROUP BY LEFT(INS_ZIP, 5)
) as q1
full outer join (
SELECT     COUNT(CASE_ID) AS COMP_COUNT, LEFT(INS_ZIP, 5) AS ZIP
FROM         Case
WHERE     (COMP_DT > '01/01/2007') AND (COMP_DT < '10/01/2007') AND (INS_ZIP IS NOT NULL AND INS_ZIP <> '' AND len(INS_ZIP) = 5)
 GROUP BY LEFT(INS_ZIP, 5)
) q2
on q2.zip = q2.zip
0
 

Expert Comment

by:jtaylor78
ID: 20112051
This method uses a derived table to calculate the COMP_COUNT

SELECT     COUNT(t1.CASE_ID) AS ORDER_COUNT, LEFT(t1.INS_ZIP, 5) AS ZIP, t3.COMP_COUNT
FROM         [Case] as t1
left join (SELECT     COUNT(t2.CASE_ID) AS COMP_COUNT, LEFT(t2.INS_ZIP, 5) AS ZIP
            FROM         [Case] as t2
            WHERE     (t2.COMP_DT > '01/01/2007') AND (t2.COMP_DT < '10/01/2007') AND (t2.INS_ZIP IS NOT NULL AND t2.INS_ZIP <> '' AND len(t2.INS_ZIP) = 5)
            GROUP BY LEFT(t2.INS_ZIP, 5)) as t3 on t3.ZIP = LEFT(t1.INS_ZIP, 5)
WHERE     (t1.EXP_DT > '01/01/2007') AND (t1.EXP_DT < '10/01/2007') AND (t1.INS_ZIP IS NOT NULL) AND (t1.INS_ZIP <> '') AND (LEN(t1.INS_ZIP) = 5)
GROUP BY LEFT(t1.INS_ZIP, 5), t3.COMP_COUNT
0
 
LVL 1

Expert Comment

by:PatTheDBA
ID: 20112133
Angelll, this looks great but you typo'd the on clause.  It should be "on q1.zip = q2.zip"
0
 

Author Comment

by:cbish21577
ID: 20112188
Thanks angelIII that did what i needed and i corrected that typo below.
jtaylor78 your solution gave the COMP_COUNT as all null but thanks regardless.

Final Statement:
select coalesce(q1.zip, q2.zip) as zip, q1.order_count , q2.comp_count
from (
SELECT     COUNT(CASE_ID) AS ORDER_COUNT, LEFT(INS_ZIP, 5) AS ZIP
FROM         Case
WHERE     (EXP_DT > '01/01/2007') AND (EXP_DT < '10/01/2007') AND (INS_ZIP IS NOT NULL) AND (INS_ZIP <> '') AND (LEN(INS_ZIP) = 5)
GROUP BY LEFT(INS_ZIP, 5)
) as q1
full outer join (
SELECT     COUNT(CASE_ID) AS COMP_COUNT, LEFT(INS_ZIP, 5) AS ZIP
FROM         Case
WHERE     (COMP_DT > '01/01/2007') AND (COMP_DT < '10/01/2007') AND (INS_ZIP IS NOT NULL AND INS_ZIP <> '' AND len(INS_ZIP) = 5)
 GROUP BY LEFT(INS_ZIP, 5)
) q2
on q1.zip = q2.zip
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question