select subquery - SQL Server Forums

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)

select subquery

Author

Topic

j-in-nz
Starting Member

17 Posts

Posted - 2007-03-25 : 20:47:51

3 tables
Province
1.Prov1
2.Prov2
District
ProvId DistrictId Name
2 1 District1
2 2 District2
2 3 District3
Pages
DistID Page Title
2 district2IntroPage Intro
3 district3AboutPage About
3 district3IntroPage Intro

I want to return all rows from District table where title = 'Intro' from Pages table.

result
District1 null
District2 null
District3 district3IntroPage

do I need a select within a select. Once I add the where criteria, I do not get all district values.
any help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 20:50:51

[code]
select d.Name, p.Page
from District d left join Pages p
on d.DistrictId = p.DistID
[/code]

j-in-nz
Starting Member

17 Posts

Posted - 2007-03-25 : 21:01:33

Hi KH,

Thank u for your reply, that works but once I add the where clause, i don't get all from District table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 21:04:25

can you post your query ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 21:46:27

[code]
select d.Name, p.Page
from District d left join Pages p
on d.DistrictId = p.DistID
and p.title = 'Intro'
[/code]

j-in-nz
Starting Member

17 Posts

Posted - 2007-03-25 : 21:49:46

select d.Name, p.Page
from District d left join Pages p
on d.DistrictId = p.DistID
where p.Title = 'Intro'

I get a result of :
District3 district3IntroPage

which is not what I want. So do I need something like this:
select d.Name,(Select Page from Pages where page.districtID = district.DistrictID and Title='Intro') as Page
from District d left join Pages p
on d.DistrictId = p.DistID

this returned the fol. error:
Subquery returned more than 1 value.

j-in-nz
Starting Member

17 Posts

Posted - 2007-03-25 : 21:53:43

Thanks a million KH your query did the trick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources