Solved

# LEFT JOIN Two Select Statements on the Same Table

Posted on 2007-10-19
Medium Priority
3,873 Views
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
Question by:cbish21577

LVL 143

Accepted Solution

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

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

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

Author Comment

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

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
Course of the Month8 days, 7 hours left to enroll