Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query

Status
Not open for further replies.

NotSQL

Technical User
May 17, 2005
205
GB
Sorry to bother you all again.

Im trying to join these two queries together, can you help.

I need all the accounts in query one and a count of meetings that have have taken place which are in query two... I know i need to join on accountName but am unsure how to join the 2 queies

Select AccountName
From dbo.Sales
Group By AccountName

Select AccountName, ISNULL(Count(AccountAccount),0) As NumberOFMeeting
From dbo.V_Time
Group by AccountName
 
It looks like you want all records from sales even if no record is in v_time.

Look at LEFT JOIN also you might want to re-structure your count.

Something like
Code:
SELECT S.AccountName, V.NumberOfMeeting
FROM dbo.Sales S 
LEFT JOIN (
     SELECT AccountName, COUNT(*) AS NumberOfMeeting
     FROM dbo.V_Time 
     GROUP BY AccountName
) AS V 
ON S.AccountName = V.AccountName


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
Since accountname exist in both queries I am guessing you want to pickup AccountName's that are missing from V_Time?

Simi
 
It's hard to see what you are trying to do, because it appears as though you have everything you need from the V_Time table. But... that's because I may not understand the relationships between your tables. One way you could do this is to use a derived table technique, which is basically to embed a query within another one and then treat that query as though it were a table, like this:

Code:
Select AccountName, MeetingCount.NumberOfMeeting
From   dbo.Sales
       Inner Join (
         Select   AccountName,
                  ISNULL(Count(AccountAccount),0) As NumberOFMeeting
         From     dbo.V_Time
         Group by AccountName
         ) as MeetingCount
         On Sales.AccountName = MeetingCount.AccountName
Group By Sales.AccountName, MeetingCount.NumberOfMeeting

This code is not tested, so there may be a minor syntax problem.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
This is what im try to get to, is this the best way of writting this?

Select t1.AccountName, IsNull(t2.NumberOFMeeting,0) as NumberOFMeeting
From
(Select AccountName
From Sales_Matrix
Group By AccountName)t1 left outer join (Select MaginusAccount, ISNULL(Count(AccountName),0) As NumberOFMeeting
From V_TimeCard
Group by AccountName) t2 on t1.AccountName = t2.AccountName
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top