Merging 2 queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merging 2 queries

Author

Topic

ninaa
Starting Member

1 Post

Posted - 2011-10-18 : 12:53:11

select top 25
is_issue_no as [Issue Number],
cm_full_name as [Client City]
from
issues i
left join customers cm on i.is_customer_id=cm.cm_customer_id
left join callbacks cb on i.is_issue_no=cb.cb_issue_no
where
(is_assigned_group_id=3 or is_assigned_group_id=4 or is_assigned_group_id=7 or is_assigned_group_id=10 or is_assigned_group_id=8)

RESULT::::

Issue Number Client City
3 HARVEY2
9 LA MOURE
15 HAYFIELD
22 SALISBURY
23 DE SMET

select CB_COMPLETEDDATE as [Date Last Called] , cm.cm_full_name as [Client City]
from [callbacks] cb
inner join issues i on cb.cb_issue_no=i.is_issue_no
left join customers cm on i.is_customer_id=cm.cm_customer_id
where CB_COMPLETEDDATE != 'NULL' and CB_COMPLETEDDATE < DateAdd(d, -7, GETDATE())
--and cm_full_name= 'columbus'
group by cm_full_name

RESULT::::

Date Last Called Client City
10/3/2011 BRYANT
4/21/2011 BRYON
10/3/2011 COKATO
9/20/2011 DALTON1
9/27/2011 DE SMET
9/15/2011 HARVEY2
10/11/2011 HAYFIELD
10/7/2011 KIESTER
9/14/2011 LA MOURE

--The first query gives me a list of issue no and the client city associated to that issue.
--The second query gives me when the Client in that city was last called.

--I need a list of isssue no with the client city as given by first query, but only those clients that are listed in the result of
-- query no 2. Anyway that I can achieve it?

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-10-18 : 13:51:52

Only select the name from the second query an put the query within an IN in the where statement.

select top 25
is_issue_no as [Issue Number],
cm_full_name as [Client City]
from
issues i
left join customers cm on i.is_customer_id=cm.cm_customer_id
left join callbacks cb on i.is_issue_no=cb.cb_issue_no
where
(is_assigned_group_id=3 or is_assigned_group_id=4 or is_assigned_group_id=7 or is_assigned_group_id=10 or is_assigned_group_id=8)
and
(
cm_full_name in
(
select cm.cm_full_name
from [callbacks] cb
inner join issues i on cb.cb_issue_no=i.is_issue_no
left join customers cm on i.is_customer_id=cm.cm_customer_id
where CB_COMPLETEDDATE != 'NULL' and CB_COMPLETEDDATE < DateAdd(d, -7, GETDATE())
--and cm_full_name= 'columbus'
group by cm_full_name
)

Likes to run, hates the runs!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 05:12:29

another way is to use join which will perform better than IN for large datasets


select top 25
is_issue_no as [Issue Number],
cm_full_name as [Client City],
t.[Date Last Called]
from
issues i
left join customers cm on i.is_customer_id=cm.cm_customer_id
left join callbacks cb on i.is_issue_no=cb.cb_issue_no
inner join (
select CB_COMPLETEDDATE as [Date Last Called] , cm.cm_full_name as [Client City]
from [callbacks] cb
inner join issues i on cb.cb_issue_no=i.is_issue_no
left join customers cm on i.is_customer_id=cm.cm_customer_id
where CB_COMPLETEDDATE != 'NULL' and CB_COMPLETEDDATE < DateAdd(d, -7, GETDATE())
--and cm_full_name= 'columbus'
group by cm_full_name
) t
on t.[Client City]= cm_full_name
where
(is_assigned_group_id=3 or is_assigned_group_id=4 or is_assigned_group_id=7 or is_assigned_group_id=10 or is_assigned_group_id=8)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources