What's Wrong with My SELECT Query?

[Tehuti]

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?

[stajer]

I assume the problem is your query is only returning entries from the USA.

This will get all entries:

select table1.id, table1.name, table1.country, table2.country_usa
from table1 left join table2 on table1.usa_id = table2.usa_id;

But, you should reconsider your table structure. Try this:

Table 1:
ID
name
countryID

table 2:
countryID
countryName

Table 2 is a list of all countries, including the USA.

Your original query would work on this table.

[Tehuti]

Hi, Stajer,

Thanks for the response!

You're right: my query is only returning entries from the USA. I tried your suggestion and it worked. Thank you for that!

However, the post above was only an example. The truth is, my tables are far more complicated. I adapted your suggestion to my real database and produced the query below. The problem is, it doesn't work! Can you see any problems with it?

The parts rendered in bold are those which seem to be faulty. They sometimes show NULL values when they are not supposed to. The strange thing is, when I take out the parts that work, the parts in bold that are faulty get fixed!

SELECT

tcoupon.anch,
tcoupon.gotourl,
tcoupon.code,
DATE_FORMAT(tcoupon.expdate, '%d %M, %Y') AS formated_date,

tadv.adv,
tadv.advlogo,
tadv.advurl,

tcat.cat,
tcat.caturl,

texpun.expun,

tinstr.instr,
tinstr.blockanch

FROM tcoupon left join (tadv, tcat, texpun, tinstr) on

(tcoupon.advid = tadv.advid AND
tcoupon.catid = tcat.catid AND
tcoupon.expunid = texpun.expunid AND
tcoupon.instrid = tinstr.instrid);

[Tehuti]

The above is turning into a major problem. I can't seem to select all the data that I want. There are always NULL values where there shouldn't be.

I've been told that it's something to do with the table join. I've tried every type but not a single one has worked. The only thing that seems to work is if I remove one of my tables (texpun) from the query.

Can anyone help, please? What could be the problem?

Just in case you need it, here's my database:

--------------------

tcoupon

id (primary key)
type
date
anch
gotourl
code
expdate
count
expunid (foreign key)
catid (foreign key)
advid (foreign key)
instrid (foreign key)

-------------

tadv

advid (primary key)
adv
advdate
advlogo
advurl

-------------

tinstr

instrid (primary key)
instr
blockanch

-------------

tcat

catid (primary key)
cat
catdate
caturl

-------------

texpun

expunid (primary key)
expun

[Tehuti]

Thanks, guys, but I no longer need help regarding this matter.