What's Wrong with My SELECT Query?

Show me how to do this and I'll add to your reputation.

Imagine this simple table:

• id
• name
• country

However, since most people will be from the USA, and I don't want to keep repeating "USA" in my table, I put it in its own table, thus:

table1

• id
• name
• country
• usa_id

table2

• usa_id
• country_usa

Now, how do I select the id, name and country of everyone, whether they're from the USA or not?

I've tried this but it doesn't work:

select table1.id, table1.name, table1.country, table2.country_usa from table1, table2 where table1.usa_id = table2.usa_id;

Anyone know how to do it?

 

I believe if you want to get all the fields you put SELECT * FROM

 

The above is just an example. In my real table, I can't select all, as I don't want all. I only want to select what I explained.

Do you know how I can do it?

 

Sorry I can't help you, I don't know. I'm still getting used to just working with 1 table at time, not 2.
best probably to go to phpfreaks.com forum or someplace where there is more seasoned programmers around that can help you.

 

Thanks for the effort. I added points to your reputation.

 

select table1.id, table1.name, table1.country, table2.country_usa from table1, table2 where table1.usa_id = table2.usa_id AND table2.country_usa="USA"

 

Thanks for the attempt, Phynder, but it didn't work. I added points to your account, nonetheless.

Guys, I now know how to do this, so I no longer need any help. Here's the correct syntax:

SELECT table1.id, table1.name, table1.country, table2.country_usa
FROM table1
LEFT JOIN table2
ON table1.usa_id = table2.usa_id;

 

You left me wondering what you want to achieve since now you are encoding information into your column names. This usually means something is not correct / optimal since information should reside in the rows of the table and not in the column names.
Translating this I think you want to have a country table that contains an id, countryname this would take your data into a nth-normal form.
But if you only want to segregate you rows into USA and NON-USA it is more efficient in terms of storage and query speed to create an boolean column named usa in table1. TRUE meaning a USA ROW and false meaning a NON-USA row.
Dropping the join will also increase query speed/scalability when using large datasets.
Hope this helps

 

Interesting advice. Thanks!

Reputation points added to your account.

 

You could also use "inner join"