correct row ID but not the text

[JRBower]

I'm having trouble displaying text for 'Nationality' in a table. I can display the correct row ID (using the below SQL) but not the text. I'm sure I'm overlooking something simple:

Here's my SQL code:

SELECT    Recruiters.RecruiterID, Recruiters.FirstName, Recruiters.LastName, JobTitles.JobTitle, Recruiters.City, States.State, Countries.Country, Recruiters.Nationality
FROM      dbo.Recruiters, dbo.JobTitles, dbo.States, dbo.Countries 
WHERE     dbo.Recruiters.JobTitleID = dbo.JobTitles.JobTitleID
  AND     dbo.Recruiters.StateID = dbo.States.StateID
  AND     dbo.Recruiters.CountryID = dbo.Countries.CountryID

And here are my look up tables:

SELECT    StateID, State
FROM      dbo.States

SELECT    CountryID, Country
FROM      dbo.Countries 

SELECT    CountryID, Nationality
FROM      dbo.Countries

If I try adding:

AND     dbo.Recruiters.Nationality = dbo.Countries.Nationality

I get an error.

As you can see my countries table has Countries in one column and Nationalities in another. I'd rather not have to make a separate look up table for nationalities if possible.

Thanks for your help.

James

[ck9663]

I'm having trouble displaying text for 'Nationality' in a table. I can display the correct row ID (using the below SQL) but not the text. I'm sure I'm overlooking something simple:

Here's my SQL code:

SELECT    Recruiters.RecruiterID, Recruiters.FirstName, Recruiters.LastName, JobTitles.JobTitle, Recruiters.City, States.State, Countries.Country, Recruiters.Nationality
FROM      dbo.Recruiters, dbo.JobTitles, dbo.States, dbo.Countries 
WHERE     dbo.Recruiters.JobTitleID = dbo.JobTitles.JobTitleID
  AND     dbo.Recruiters.StateID = dbo.States.StateID
  AND     dbo.Recruiters.CountryID = dbo.Countries.CountryID

And here are my look up tables:

SELECT    StateID, State
FROM      dbo.States

SELECT    CountryID, Country
FROM      dbo.Countries 

SELECT    CountryID, Nationality
FROM      dbo.Countries

If I try adding:

AND     dbo.Recruiters.Nationality = dbo.Countries.Nationality

I get an error.

As you can see my countries table has Countries in one column and Nationalities in another. I'd rather not have to make a separate look up table for nationalities if possible.

Thanks for your help.

James

by the looks of this, your Nationality field is in the Countries table, not in Recruiters.your code (Recruiters.Nat ionality) will give you an error.

try:

SELECT    Recruiters.RecruiterID, Recruiters.FirstName, Recruiters.LastName, JobTitles.JobTitle, Recruiters.City, States.State, Countries.Country, Cou ntries.Nationality
FROM      dbo.Recruiters, dbo.JobTitles, dbo.States, dbo.Countries 
WHERE     dbo.Recruiters.JobTitleID = dbo.JobTitles.JobTitleID
  AND     dbo.Recruiters.StateID = dbo.States.StateID
  AND     dbo.Recruiters.CountryID = dbo.Countries.CountryID

-- CK

[JRBower]

by the looks of this, your Nationality field is in the Countries table, not in Recruiters.your code (Recruiters.Nat ionality) will give you an error.

try:

SELECT    Recruiters.RecruiterID, Recruiters.FirstName, Recruiters.LastName, JobTitles.JobTitle, Recruiters.City, States.State, Countries.Country, Countries.Nationality
FROM      dbo.Recruiters, dbo.JobTitles, dbo.States, dbo.Countries 
WHERE     dbo.Recruiters.JobTitleID = dbo.JobTitles.JobTitleID
  AND     dbo.Recruiters.StateID = dbo.States.StateID
  AND     dbo.Recruiters.CountryID = dbo.Countries.CountryID

-- CK

Thanks for your assistance CK. Actually my original code was as you suggested (Countries.Nati onality) but for some reason it's not producing the right nationality. When I changed it to Recruiters.Nati onality the correct row was selected but only the ID number would dislplay. What seems intuitive to me (and follows the same logic as

AND     dbo.Recruiters.CountryID = dbo.Countries.CountryID

is to add:

AND     dbo.Recruiters.Nationality = dbo.Countries.Nationality

since I have a Nationality column (int) in my Recruiters table. However, when I execute the code it throws an error.

Is there another approach to solving this problem?

Thanks again,
James

[ck9663]

Thanks for your assistance CK. Actually my original code was as you suggested (Countries.Nati onality) but for some reason it's not producing the right nationality. When I changed it to Recruiters.Nati onality the correct row was selected but only the ID number would dislplay. What seems intuitive to me (and follows the same logic as

AND     dbo.Recruiters.CountryID = dbo.Countries.CountryID

is to add:

AND     dbo.Recruiters.Nationality = dbo.Countries.Nationality

since I have a Nationality column (int) in my Recruiters table. However, when I execute the code it throws an error.

Is there another approach to solving this problem?

Thanks again,
James

would you mind posting here the code that you have that shows the ID and post the other code that give you an error and also the error that you're seeing...


-- CK

[JRBower]

would you mind posting here the code that you have that shows the ID and post the other code that give you an error and also the error that you're seeing...


-- CK

Hi CK,
Thanks for your attention. I decided to just make another lookup table which solved the problem.

Cheers,
James