SubQuery Join on Where in Main & Case

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 Join on Where in Main & Case

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-06-18 : 10:14:39

Good day, i need help.

I have a main query which i want join on subquery but subquery where clause must filter on a field in main query.

select Main.Item
,case cust ='' then cust2
when cust2='' then cust3
from table

left join
(select cust,cust2,cust3 from table etc
where item = Main.Item) B
on case Main.cust <>'' then B.cust
when Main.cust2<>'' then B.cust2

There are two things i need help on,
1 - subquery join on main query field
2 - join on case

Please help - Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-18 : 10:47:32

Pls post the full query you used. What is 'Main'? I dont see that in your query here. What is your exact requirement? Can you explain with sample data and expected output.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-06-18 : 14:50:15

Hi, i would like to join on a subquery based on a Case

select
case when Region <> '' and Region is not null
then PromotionRegion
when rtrim(CustDiscCode) <> '' then rtrim(CDISC.CustDisc)
when rtrim(Customer) <> '' then rtrim(CUST.CustomerName)
else 'None' end as Customer_Detail
from
(select ... various tables) v1

left join
(select Region,CustomerCode,ItemDiscount,ItemCode, sum(SalesVolume)
from dbo.Sales S where region = v1.region) v2
on case...

I want the v2 subquery to be filtered on the case on v1
?

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-06-18 : 16:40:48

Apologies, i thought of another method.

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources