How do I join 4 table in procedure ?

Question

0.00/5 (No votes)

See more:

hi ...
i want join 3 table in procedure ...
butt error in group by !?

this query :

SQL

USE [AnbarDB]GO
/****** Object:  StoredProcedure [dbo].[Kardex]    Script Date: 03/09/2015 10:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Kardex]
@Kala nvarchar(50)
 
 
AS
BEGIN
select       
       dbo.TAnbar.Anbar as anbar,
       dbo.TAnbar.Kalatype as noekala,
       dbo.TKala.Cod as cod,
       dbo.TKala.Kala as kala,
       dbo.TKala.Vahed as vahed,
       dbo.TKala.Toz as tozi,
       dbo.TKala.Mojodi as mojodi,
       dbo.TKala.Pic as pict,
       COUNT(dbo.TBuy.Kala) as tedadkala,
       SUM(dbo.TBuy.Andaze) as andaz,
       SUM(dbo.TBuy.Kol) as kalakol,
       COUNT(dbo.TSel.Kala) as tedadkala2,
       SUM(dbo.TSel.Andaze) as andaz2,
       SUM(dbo.TSel.Kol) as kalakol2       
       From dbo.TAnbar inner join dbo.TKala on dbo.TAnbar.Anbar = dbo.TKala.Anbar
       inner join dbo.TBuy on dbo.TKala.Kala = dbo.TBuy.Kala
       inner join dbo.TSel on dbo.TKala.Kala = dbo.TSel.Kala
       where  dbo.TKala.Kala = @Kala
       group by dbo.TKala.Kala
END

and this error :

C#

Msg 8120, Level 16, State 1, Procedure Kardex, Line 7
Column 'dbo.TAnbar.Anbar' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

and this tables :

pic[^]

Posted 8-Mar-15 23:36pm

ghasem110deh

Add a Solution

Comments

_Asif_ 9-Mar-15 5:48am

Why are you grouping the result over dbo.TKala.Kala? The query will work if you remove the whole group by clause

CHill60 9-Mar-15 6:04am

Anything in the SELECT clause must also appear in the GROUP BY clause - it might be easier if you explain what you are trying to do as the counts here don't look right

Herman<T>.Instance 9-Mar-15 6:26am

set it as answer!

CHill60 9-Mar-15 6:34am

:) I'll give the OP an opportunity to come back to me with their actual problem first ... I'm thinking of guiding them towards using CTE for the for counts and sums then joining to the other tables for the other data

Herman<T>.Instance 9-Mar-15 6:54am

ok!

ZurdoDev 9-Mar-15 9:38am

Isn't the error clear? If you want to use group by each column needs to be an aggregate or in the group by statement.

2 solutions

Solution 2

thanks guys ...
very much !
and dear CHill60 ...

Posted 9-Mar-15 21:25pm

ghasem110deh

Comments

Deepu S Nair 10-Mar-15 3:28am

Don't post your comment as solution.Use 'Have a Question or Comment?' link.

Rajesh waran 10-Mar-15 3:29am

Try to post it as comment, not as an Answer.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-03-09T07:11:00

Anything in the GROUP BY clause must also appear in the SELECT clause.

There are several ways of rearranging this with sub-queries etc but below is a solution using CTEs (Common Table Expressions[^])

-- Get the grouped data from TBuy into CTE T1...
With T1(Kala, tedadkala, andaz, kalakol) 
AS
(
    SELECT Kala, Count(Kala), SUM(Andaze), SUM(Kol)
    from TBuy GROUP BY Kala
),

-- Get the grouped data from TSel into CTE T2...
T2(Kala, tedadkala2, andaz2, kalakol2)
AS
(
    SELECT Kala, Count(Kala), SUM(Andaze), SUM(Kol)
    from TSel GROUP BY Kala
)
-- Use the CTEs in the overall query
select       
       dbo.TAnbar.Anbar as anbar,
       dbo.TAnbar.Kalatype as noekala,
       dbo.TKala.Cod as cod,
       dbo.TKala.Kala as kala,
       dbo.TKala.Vahed as vahed,
       dbo.TKala.Toz as tozi,
       dbo.TKala.Mojodi as mojodi,
       dbo.TKala.Pic as pict,
	tedadkala,
    andaz,
    kalakol,
    tedadkala2,
    andaz2,
    kalakol2 
 
From dbo.TAnbar 
inner join dbo.TKala on dbo.TAnbar.Anbar = dbo.TKala.Anbar
inner join T1 on T1.Kala = TKala.Kala
inner join T2 on TKala.Kala = T2.Kala
where  dbo.TKala.Kala = @kala
--NB NO group by here