How to make this 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 2000 Forums

SQL Server Development (2000)

How to make this query

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-11-09 : 00:20:17

Hello All,

I have one table Registration with the fields for Nameof the person, PermanentCountry,PresentCountry & another table
Nation with the fields Country Code & CountryName

Registration
------------

Name.......PerCountry..PreCountry

Joe......101.........102
Ani......101.........103
Lala.....104.........105

Nation
------]

CountryCode ...... CountryName

101 ...... US
102 ...... England
103 ...... India
104 ...... UAE
105 ...... Austria

I have to display it like

Name Permannent Country Present Country

Joe US England
Ani US India
Lala UAE Austria

How could I do it?

Regards & Thanks
Ceema

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 01:27:15

SELECT Registration.Name, PerNation.CountryName AS [Permannent Country], PreNation.CountryName AS [Present Country]
FROM Registration
INNER JOIN Nation AS PerNation ON PerNation.CountryCode = Registration.PerCountry
INNER JOIN Nation AS PreNation ON PreNation.CountryCode = Registration.PreCountry
ORDER BY .......

Peter Larsson
Helsingborg, Sweden

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-11-09 : 02:33:21

But the problem is that some times I will have some values in Registration field like

Nana......Null.........102
Mum......101.........Null
Soya.....104.........105

I tried left join but it's not wo0rking for the query

Regards
Ceema

quote:
Originally posted by Peso

SELECT Registration.Name, PerNation.CountryName AS [Permannent Country], PreNation.CountryName AS [Present Country]
FROM Registration
INNER JOIN Nation AS PerNation ON PerNation.CountryCode = Registration.PerCountry
INNER JOIN Nation AS PreNation ON PreNation.CountryCode = Registration.PerCountry
ORDER BY .......

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 02:49:04

If you had told use this requirement from the beginning, I would not have wasted time on you, answering again.
Simple replace INNER JOIN with LEFT JOIN.

SELECT Registration.Name, PerNation.CountryName AS [Permannent Country], PreNation.CountryName AS [Present Country]
FROM Registration
LEFT JOIN Nation AS PerNation ON PerNation.CountryCode = Registration.PerCountry
LEFT JOIN Nation AS PreNation ON PreNation.CountryCode = Registration.PreCountry
ORDER BY .......

Here is sample data and output

declare @Registration table (Name varchar(4), PerCountry tinyint, PreCountry tinyint)

insert	@registration
select	'Joe', 101, 102 union all
select	'Ani', NULL, 103 union all
select	'Peso', 106, 107 union all
select	'Lala', 104, NULL

declare @Nation table (CountryCode tinyint, CountryName varchar(7))

insert	@nation
select	101, 'US' union all
select	102, 'England' union all
select	103, 'India' union all
select	104, 'UAE' union all
select	105, 'Austria' union all
select	106, 'SE' union all
select	107, 'Sweden'

SELECT reg.Name, per.CountryName AS [Permannent Country], pre.CountryName AS [Present Country]
FROM @registration AS reg
LEFT JOIN @Nation AS per ON per.CountryCode = reg.PerCountry
LEFT JOIN @Nation AS pre ON pre.CountryCode = reg.PreCountry

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources