sql inner join with inner query

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

sql inner join with inner query

Author

Topic

madhan
Yak Posting Veteran

59 Posts

Posted - 2015-02-11 : 12:48:42

[code]
Select XXX_Columns
from Rolls
inner join (SELECT RollKey, SUM(ReceivedWeight) ReceivedWeight, SUM(ReceivedLineal) ReceivedLineal FROM xxxJournal GROUP BY RollKey) rj ON r.RollKey = rj.RollKey
[/code]

in the above query I am trying to do the below join

INNER JOIN xxxJournalShifts rjs ON rj.RollstockJournalKey = rjs.RollstockJournalKey

but it fails becasue xxxJournal is created in inner query and I can't access the rj.RollstockJournalKey in outer query. How would I access it in outer query, please?
By the way I don't want to change the subquery groupby based on Rollkey and RollstockJournalKey, Thanks

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-11 : 12:54:44

Seems like you are referring alias "r" which is not defined

madhan
Yak Posting Veteran

59 Posts

Posted - 2015-02-11 : 12:55:53

oh... Rolls as r

[Code]
Select XXX_Columns
from Rolls r
inner join (SELECT RollKey, SUM(ReceivedWeight) ReceivedWeight, SUM(ReceivedLineal) ReceivedLineal FROM xxxJournal GROUP BY RollKey) rj ON r.RollKey = rj.RollKey
[/Code]

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-11 : 12:57:17

exactly

madhan
Yak Posting Veteran

59 Posts

Posted - 2015-02-11 : 12:59:29

I am trying to add join condition

INNER JOIN xxxJournalShifts rjs ON rj.RollstockJournalKey = rjs.RollstockJournalKey

that fails, please help me on this part

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-11 : 13:04:30

Is field RollstockJournalKey in both tables?
Are they the same datatype?
What error message do you get?
Show us your query that fails, table structure and sample data.

Edit: corrected spelling.

madhan
Yak Posting Veteran

59 Posts

Posted - 2015-02-11 : 17:41:58

Thanks for trying to help me on this one. I talked with other developer and he explained me on what needs to be solved. I misunderstood before.
fixed it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources