Query in a query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query in a query

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-04 : 14:42:45

Hello All,

This is a query:

INNER JOIN BookLedger BL
LEFT JOIN(
SELECT PassID, SUM(Amount) AS PassAmount
FROM PassTranDetail GROUP BY PassID) PA
ON PA.PassID=BL.PassID

Now I have to add one more table to my query to get the TTID column from PassTranDetailline table (left join). To do that I have join PassTranDetail.TID with the PassTranDetailLine.TID column.

But my problem is I can not selecting PassTranDetail.TID in above query because of the group by should be only on PassID.

Can any one tell me how to write the query inside the query to achieve these results.

Thanks,
-S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-04 : 14:48:55

Your question is not clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-04 : 15:01:09

Sorry for not being very clear in the begining.

The query is selecting PassID and sum(Amount) from PassTranDetail. But I wanted to get one more column from this table TID and join this TID column with another table PassTranDetailLine to get TTID column value in my final select.

I can not select TID in the query because I would have to add it in my group by. So now my question is how do I modify the query to inner join with PassTranDetail again to get the TID column so that I can further join it with PassTranDetailline table.

Please let me know if it is still not clear.

-S

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-04 : 16:47:53

Let me ask it in more simpler way.

If I am using a table in a join that has a max(amount) with group by clause. Now from the same table I have to get addtional column without using in the same join because that has a group by clause. How do I join the same table again in the query?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-05 : 07:05:42

quote:

The query is selecting PassID and sum(Amount) from PassTranDetail. But I wanted to get one more column from this table TID and join this TID column with another table PassTranDetailLine to get TTID column value in my final select.

We aren't going to be able to answer you based on the information provided - we know nothing about your data -- does TID need to be aggregated somehow? or just added to the group by clause == we have *no* way of knowing. Could you please provide some sample data?

If you provide sample data and expected results we will be able to help.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

andrewz00
Starting Member

15 Posts

Posted - 2009-05-05 : 15:17:30

i think i understand... you want to take the sum(xxx) grouped by ID and you also want a sum or count or a field from the same table grouped by a different ID?

i would suggest using two seperate queries..

INNER JOIN BookLedger BL
LEFT JOIN(
SELECT PassID, SUM(Amount) AS PassAmount
FROM PassTranDetail GROUP BY PassID) PA
ON PA.PassID=BL.PassID

LEFT JOIN(
SELECT TTID,sum(XXX) as XXXAmount
from PAssTrainDetail Group BY TTID ) XA
on BL.TTID = XA.TTID

at least thats what i understand from your question and thats how i would go about it. peso may come along with 2 lines and a better solution for you tho.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources