Subquery in a Stored Procedure

[Tiger955]

Hi!

I was not sure if I should open the thread in Queries or in SQL-Server as it would fit into both..

My problem is that out of two tables I need a result set presenting the number of orders items per PLU and the number of items still on stock.

I cannot do in ONE stored procedure.

My query presenting the orders.

[COLOR=blue][FONT=Consolas]SELECT [/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblOrderDetails[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]PLU[/FONT][/COLOR][COLOR=gray][FONT=Consolas],[/FONT][/COLOR][FONT=Consolas] [COLOR=teal]tblArtikel[/COLOR][COLOR=gray].[/COLOR][COLOR=teal]Description[/COLOR][COLOR=gray],[/COLOR] [COLOR=fuchsia]SUM[/COLOR][COLOR=gray]([/COLOR][COLOR=teal]tblOrderDetails[/COLOR][COLOR=gray].[/COLOR][COLOR=teal]Pieces[/COLOR][COLOR=gray])[/COLOR] [COLOR=blue]AS[/COLOR] [COLOR=teal]Pieces[/COLOR][/FONT]
[COLOR=blue][FONT=Consolas]FROM[/FONT][/COLOR][FONT=Consolas] [COLOR=teal]tblOrderDetails[/COLOR][COLOR=gray] INNER[/COLOR] [COLOR=gray]JOIN[/COLOR][/FONT]
[FONT=Consolas]    [COLOR=teal]tblArtikel[/COLOR] [COLOR=blue]ON[/COLOR] [COLOR=teal]tblOrderDetails[/COLOR][COLOR=gray].[/COLOR][COLOR=teal]PLU[/COLOR] [COLOR=gray]=[/COLOR] [COLOR=teal]tblArtikel[/COLOR][COLOR=gray].[/COLOR][COLOR=teal]PLU[/COLOR] [/FONT]
[COLOR=blue][FONT=Consolas]GROUP[/FONT][/COLOR][FONT=Consolas] [COLOR=blue]BY[/COLOR] [COLOR=teal]tblOrderDetails[/COLOR][COLOR=gray].[/COLOR][COLOR=teal]PLU, tblArtikel[/COLOR][COLOR=gray].[/COLOR][COLOR=teal] Description[/COLOR] [/FONT]

gives for example

30002 BA4875 3
30012 UK8798 15

My Stock is queried with

[COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=blue][FONT=Consolas]SELECT[/FONT][/COLOR][FONT=Consolas] [COLOR=fuchsia]SUM[/COLOR][COLOR=gray]([/COLOR][COLOR=teal]tblStock[/COLOR][COLOR=gray].[/COLOR][COLOR=teal]Pieces[/COLOR][COLOR=gray])[/COLOR] [COLOR=blue]AS[/COLOR] [COLOR=teal]Ls[/COLOR][COLOR=gray],[/COLOR] [COLOR=teal]tblStock[/COLOR][COLOR=gray].[/COLOR][COLOR=teal]PLU[/COLOR][/FONT]
[FONT=Consolas][COLOR=teal]from tblStock[/COLOR][/FONT]
[COLOR=blue][FONT=Consolas]GROUP[/FONT][/COLOR][FONT=Consolas] [COLOR=blue]BY[/COLOR] [COLOR=teal][COLOR=teal]tblStock[/COLOR][/COLOR][COLOR=gray].[/COLOR][COLOR=teal]PLU[/COLOR][COLOR=gray])[/COLOR][/FONT]

So, how can I combine both resultsets in one SP?

I always get error Msg 116, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

I know where the error is, but without the PLU in the stock-query I do not know how to combine both resultsets.


Pls help!
Thanks
Michael