Can I do This

Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
AhmedAhmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Sorry,correction
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=C.Code)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahmed,
Try:
select code, name, monthdate, code,
(select sum(amount)
from b
where b.code = a.code
and month(b.date) = a.monthdate) as total
from a;
If you're after applying a table expression querying B and returning a
rowset, to each row from A, there's no set-based way to achieve this in SQL
Server 2000.
You'll have to use itterative logic. SQL Server 2005 solves this by
introducing the APPLY operator, e.g.,
-- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each Store
USE pubs;
SELECT ST.stor_id, CA.*
FROM dbo.Stores AS ST
CROSS APPLY
(SELECT TOP(2) ord_num, title_id, ord_date, qty
FROM dbo.Sales AS SL
WHERE SL.stor_id = ST.stor_id AND qty >= 10
ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on
a value in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON
A.Code=C.Code
thanks in advance
Ahmed|||I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahemd
Yes , you are right , please take a look at Itzik's solution
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=B.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:u5bldm1lFHA.32
88@.TK2MSFTNGP09.phx.gbl...
I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Thanks BG
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OLrJph1lFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Ahmed,
> Try:
> select code, name, monthdate, code,
> (select sum(amount)
> from b
> where b.code = a.code
> and month(b.date) = a.monthdate) as total
> from a;
> If you're after applying a table expression querying B and returning a
> rowset, to each row from A, there's no set-based way to achieve this in
> SQL Server 2000.
> You'll have to use itterative logic. SQL Server 2005 solves this by
> introducing the APPLY operator, e.g.,
> -- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each
> Store
> USE pubs;
> SELECT ST.stor_id, CA.*
> FROM dbo.Stores AS ST
> CROSS APPLY
> (SELECT TOP(2) ord_num, title_id, ord_date, qty
> FROM dbo.Sales AS SL
> WHERE SL.stor_id = ST.stor_id AND qty >= 10
> ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Dear all
> I need to make a correlated subquery that has the where clause depend
> on a value in the master query, ex
> Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
> I want to perform SQL like this
> SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
> B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C
> ON A.Code=C.Code
>
> thanks in advance
> Ahmed
>