Solved

# LEFT JOIN Two Select Statements on the Same Table

Posted on 2007-10-19
3,869 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 142

Accepted Solution

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

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

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

Author Comment

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to recover a database from a user managed backup
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.