Combining two select statements

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

SQL Server 2000 Forums

Transact-SQL (2000)

Combining two select statements

Author

Topic

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-04-02 : 16:46:28

Hi All,

I have a SP returning the following result
The select statement for this is
Code:

  SELECT     dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount 
         FROM         dbo.Test2 INNER JOIN 
                            dbo.test3 INNER JOIN 
                            dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN 
                            dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN 
                            dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID 
         WHERE     (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId) 
         GROUP BY dbo.TEST1.[OFFICE NAME] 
         ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC

name id count

case1 226 320
case2 219 288
case3 203 163
case4 223 90
case5 224 73

i have another select stnat which returns like this
The select statement is

Code:

Select  test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] 
Group by test1.[Office Name] 
order by count(test1.[office name]) DESC

name count
case6 10
case2 56
case4 66
case1 74
case3 88
case7 100
case5 177

How can i combine this select stament with the SP, so that, i get a fourth column with

case1 226 320 74
case2 219 288 56
..........................
...........................

Hope i am not confusing you all
Please help me, if someone knows how to combine this?

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-02 : 16:58:04

I dont see a join condition for Table2 and Table3? Also, your SELECT has only 2 columns and your result has 3 columns. Have you wondered why?

************************
Life is short. Enjoy it.
************************

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-02 : 18:00:53

[code]
select *
from
(
<your 1st query here. make sure all the column are named or alias>
) a
inner join
(
<your 2nd query here>
) b
on a.[office name] = b.[office name]
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 13:49:14

Have you tried khtan's suggestion?

Peter Larsson
Helsingborg, Sweden

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-04-03 : 14:09:02

Thanks
It worked fine, but i have now

test1 19 test1 3

I tried to remove the [office name] from second query,
But it will not work, right?
Is there any way to be like this

test1 19 3 ?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 14:17:14

You do know what * means?

select a.[Office name], a.AccountCount, b.ID, b.Col2
from
(
    <your 1st query here. make sure all the column are named or alias>
) a
inner join
(
   <your 2nd query here>
) b
on a.[office name] = b.[office name]

Peter Larsson
Helsingborg, Sweden

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-04-03 : 14:22:52

Thanks Peso,
These are small small things, which we newbies dont look for
Thanks for pointing out that
Will definitely try to look for every thing

Thanks for all your help and support

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-09 : 12:25:16

>>AND [Account ID] IN (SELECT [account id] FROM accounts WHERE clientID=(SELECT [client ID] FROM Contacts WHERE [contact id]= @contactId))AND NOT EXISTS(SELECT 'x' FROM dbo.viewCallsByInquiryAndAccount WHERE dbo.[viewCallsByOrderAndAccount].[CallID] = dbo.[viewCallsByInquiryAndAccount].[CallID])

SELECT TOP 5 [Office Name], [Account ID], COUNT([office name]) AS namecount
FROM dbo.tmp_AccountCasestest GROUP BY [office name], [Account id]
ORDER BY COUNT([office name]) DESC

Check this part of your code. You are saying Account_Id in (...)

So your subquery should only return Account_Id's. But you have officename, account_id, count(..) which is incorrect.

************************
Life is short. Enjoy it.
************************

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources