Combining select statements

[dolodolo]

Hi,

I am running two separate select statements and would like to have the records from both statements be grouped and sorted together. For example:

Table 1 and Table 2 are joined on the field hmy
in the first select statement the join is an INNER JOIN
in the second select statement the join is an OUTER LEFT JOIN to capture records that are in Table 1 without corresponding records in Table 2.

Please help.

Thanks

 

[mkrausnick]

Combining records from two different select statements requires a UNION operator:

select <list_of_fields> from <list_of_joined_tables>
UNION
select <list_of_fields> from <_another_list_of_joined_tables>

Notes:
1. The <list_of_fields> must be identical in both cases. The list_of_tables does not.

2. You will get duplicates unless you program them out. You can either add a sub-select to the second select that screens-out the records selected in the first select (using a "NOT IN" or "NOT EXISTS"), or wrap the whole thing in a SELECT DISTINCT to eliminate the duplicates:

SELECT DISTINCT * FROM (
select <list_of_fields> from <list_of_joined_tables>
UNION
select <list_of_fields> from
<_another_list_of_joined_tables> )



Mike Krausnick
Dublin, California

 

[gmmastros]

You will get duplicates unless you program them out.

Not with the UNION operator. Union automatically filters out distinct data. There is another operator Union All that does NOT filter out duplicates. If you know there will not be duplicates, then you should use the UNION ALL operator because it is faster.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mkrausnick]

Right you are George. I knew that once, back in the cobwebs of my brain!

Mike Krausnick
Dublin, California

 

[markros]

Simply use LEFT JOIN and if you need some indicator if it's the existing record or a new one, use

cast(case when T2.PK IS NULL then 'New' else 'Existing' end) as char(20)) as RecordIndicator

 

[dolodolo]

Here's my script - I'm still getting an error:

select
j.scode job,
(substring(c.scode,1,2) + '-' + substring(c.scode,3,3)) category,
jd.hmy jobdetl_hmy,
sum(camt) sum_revisions,
jd.srevision jd_srevision_total,
sum(camt) - (jd.srevision) variance
from
CMrevision cm
Inner Join Jobdetl as jd on jd.hmy = cm.hjd and jd.hjob = cm.hjob and jd.hcateg = cm.hcat
Inner Join Job as j on jd.hjob = j.hmy
Inner Join Category as c on c.hmy = jd.hcateg
Inner Join Jobtype as jt on jt.hmy = j.ijobtype

where
cm.bpost = -1
group by j.scode, cm.hjd, jd.hmy, c.scode, jd.srevision
Having (sum(camt) - jd.srevision) <> 0
order by j.scode
UNION
select
j.scode job,
(substring(c.scode,1,2) + '-' + substring(c.scode,3,3)) category,
jd.hmy jobdetl_hmy,
isnull(sum(cm.camt),0) sum_revisions,
jd.srevision jd_srevision_total,
isnull(sum(camt),0) - (jd.srevision) variance
from jobdetl as jd
Left Join cmrevision as cm on jd.hmy = cm.hjd
INNER JOIN job as j on jd.hjob = j.hmy
INNER JOIN category as c on jd.hcateg = c.hmy
where
cm.hjd is null and
isnull(jd.srevision,0) <> 0
group by j.scode, c.scode, jd.hmy, jd.srevision
order by j.scode, c.scode;

Any thoughts? Thanks guys!

 

[markros]

Does each individual select work correctly?

 

[dolodolo]

Yes it does.

 

[markros]

Try in both cases add cast (...) as character(20)) as Category and in other case where the resulting type may not be obvious.

 

[gmmastros]

Remove the order by in your first query (just above the UNION statement).

When you union queries together, there can be only 1 order by. It goes at the end and affects the final results of the individual queries after unioned together.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, that's too.

 

[dolodolo]

Thanks guys - that worked!!!!