Left Join & Nested Query? - 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)

Left Join & Nested Query?

Author

Topic

bodmin
Starting Member

5 Posts

Posted - 2008-10-20 : 05:02:16

I have 2 tables:

tblCities:
city_id
ename //-- english name
g_code

tblCity_to_lang:
city_id
name //--names in other languages
lang_id

I need to get all English names from the first table and names from the second table by city_id, if exists. If not, leave the field blank; (left join);

Names from the second table i have to get by lang_id (nested query?)

I tried:
SELECT tblCities.*,tblCity_to_lang.name,tblCity_to_lang.lang_id
from tblCities Left join tblCity_to_lang
on tblCities.city_id=tblCity_to_lang.city_id
where tblCity_to_lang.name in
(select tblCity_to_lang.name from tblCity_to_lang where tblCity_to_lang.lang_id=3)

it is not working.
Need help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 05:11:47

[code]SELECT c.city_id,c.ename,COALESCE(name,'')
FROM tblCities c
LEFT JOIN tblCity_to_lang l
ON l.city_id=c.city_id[/code]

also make sure you post in right forum next time. this is .NET forum.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-20 : 05:22:35

moved to appropriate forum.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

bodmin
Starting Member

5 Posts

Posted - 2008-10-20 : 05:48:14

quote:
Originally posted by visakh16

SELECT c.city_id,c.ename,COALESCE(name,'')
FROM tblCities c
LEFT JOIN tblCity_to_lang l
ON l.city_id=c.city_id

also make sure you post in right forum next time. this is .NET forum.

Thanks,

But there is no selection by lang_id from tblCity_by_lang. Should be done first.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 06:02:26

then add that too
SELECT c.city_id,c.ename,COALESCE(l.lang_id,0),COALESCE(name,'')
FROM tblCities c
LEFT JOIN tblCity_to_lang l
ON l.city_id=c.city_id

bodmin
Starting Member

5 Posts

Posted - 2008-10-20 : 06:22:37

quote:
Originally posted by visakh16

then add that too
SELECT c.city_id,c.ename,COALESCE(l.lang_id,0),COALESCE(name,'')
FROM tblCities c
LEFT JOIN tblCity_to_lang l
ON l.city_id=c.city_id

Still returning all rows from tblCity_to_lang, I need only parameterized values, say lang_id=3

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 06:57:06

then add that too

DECLARE @langid int
SET @langid=3 --(pass any value you want here)
SELECT c.city_id,c.ename,COALESCE(l.lang_id,0),COALESCE(name,'')
FROM tblCities c
LEFT JOIN tblCity_to_lang l
ON l.city_id=c.city_id
WHERE l.lang_id=@langid

bodmin
Starting Member

5 Posts

Posted - 2008-10-20 : 07:03:38

in this case left join is unusefull, I need ALL cities from the left table and corrsponding cities from the right table, chosen by Lang_id

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:43:51

quote:
Originally posted by bodmin

in this case left join is unusefull, I need ALL cities from the left table and corrsponding cities from the right table, chosen by Lang_id

Thank you

modify as below

DECLARE @langid int
SET @langid=3 --(pass any value you want here)
SELECT c.city_id,c.ename,COALESCE(l.lang_id,0),COALESCE(name,'')
FROM tblCities c
LEFT JOIN tblCity_to_lang l
ON l.city_id=c.city_id
AND l.lang_id=@langid

bodmin
Starting Member

5 Posts

Posted - 2008-10-20 : 16:09:11

quote:
Originally posted by visakh16

quote:
Originally posted by bodmin

in this case left join is unusefull, I need ALL cities from the left table and corrsponding cities from the right table, chosen by Lang_id

Thank you

modify as below
DECLARE @langid int
SET @langid=3 --(pass any value you want here)
SELECT c.city_id,c.ename,COALESCE(l.lang_id,0),COALESCE(name,'')
FROM tblCities c
LEFT JOIN tblCity_to_lang l
ON l.city_id=c.city_id
AND l.lang_id=@langid

Thanks a lot!!!
Now it works great!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 23:40:46

quote:
Originally posted by bodmin

quote:
Originally posted by visakh16

quote:
Originally posted by bodmin

in this case left join is unusefull, I need ALL cities from the left table and corrsponding cities from the right table, chosen by Lang_id

Thank you

modify as below
DECLARE @langid int
SET @langid=3 --(pass any value you want here)
SELECT c.city_id,c.ename,COALESCE(l.lang_id,0),COALESCE(name,'')
FROM tblCities c
LEFT JOIN tblCity_to_lang l
ON l.city_id=c.city_id
AND l.lang_id=@langid

Thanks a lot!!!
Now it works great!

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources