Subquery field added into join subquery where clau

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)

Subquery field added into join subquery where clau

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-07-22 : 09:10:05

Hi, I need help.

I have Subquery where i want to join to another query and pass the subquery field as a where clause in the join sub query:

the join subquery is to return the maximum count but i can only add the region where clause afterwards but can't seem to pass to join subquery from outside query

select region from table1
join (
select top 1 v_region as Region
,Channel
,count(CustomerChannel) as ChanCnt
from dbo.Master

where v_Region = outside query field REGION
group by Region, Channel
order by count(Customer) desc
) as REG on Region = v_region

please assist

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-22 : 09:55:52

Maybe:

SELECT TOP 1
    M.v_region AS Region
    ,M.Channel
    ,COUNT(M.CustomerChannel) AS ChanCnt
FROM dbo.Master M
WHERE EXISTS
(
    SELECT *
    FROM table1 T
    WHERE T.Region = M.v_region
)
GROUP BY M.v_region, M.Channel
ORDER BY M.ChanCnt

otherwise post some sample data with expected results.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-22 : 10:04:34

Or maybe:

SELECT T.Region, D.Channel, D.ChanCnt
FROM table1 T
    LEFT JOIN
    (
        SELECT D1.Region
            ,D1.Channel
            ,D1.ChanCnt
            ,ROW_NUMBER() OVER (PARTITION BY D1.Region ORDER BY D1.ChanCnt DESC) AS RowNum
        FROM
        (
            SELECT
                M.v_region AS Region
                ,M.Channel
                ,COUNT(M.CustomerChannel) AS ChanCnt
            FROM dbo.Master M
            GROUP BY M.v_region, M.Channel
        ) D1
    ) D
        ON T.Region = D.Region
            AND D.RowNum = 1

Difficult to guess without sample data and expected results.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-07-22 : 10:21:11

Thank You All

I found that Ifor example worked for me :)

Regards

jrivam
Starting Member

1 Post

Posted - 2009-08-12 : 15:01:43

was the first or the second the solution?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources