Sql server 2000- retrieve single record from mutliple tables

without using views/temp tables?

[rojer_31]

Hi,

I have been breaking my head over this one:

I have 2 tables,
user_main - for main user details
country - for holding a list of all possible countries

In the user_main table, I have 2 fields
1. CurrentCountryId and 2. PermanentCountryId
(Obviously other fields also exist, but they are not relevant)

The country table holds:
1. id 2.countryname
What I want to do is retrieve all the details of a particular user profile. To get any one of the country(present or permanent) is not an issue, but when I want to get both of them in a single record, :-(

I can get 2 records including duplicate info for all fields except the countryname or I get only one countryname. Could someone help me here.

If at all possible, I would look for a sigle query to do it. If that is impossible, then can someone show me how best to do this in other ways(views, temporary tables)?, since I am new to mssql (have been using mysql until now).

Any help greatly appreciated :-)
rojer_31

[Demaestro]

Try

Select
..u.user_id,
..curr.country_name as current_country,
..perm.country_name as permanent_country
from
..user_main u,
..country perm,
..country curr
where
..u.CurrentCountryId = curr.country_id
..and u.PermanentCountryId = perm.country_id

I think that is what you are wanting. Notice that we join against the country table twice, aliasing it to coropsond to each country id that you have in your user table.

If this isn't what you needed post again and I will take another stab at it with you.

[rojer_31]

Hi,

I *almost* thought of that one :D But I cannot test it right now though, You'll have to wait a few hours for a reply on this one..

rojer_31

[rojer_31]

Yes. It works!

Thank you :)

[Demaestro]

My pleasure.