I've got a problem that I just can't seem to work out, any help would be much appreciated. The DB server is MySQL. I have the following three tables:

Categories
-----------
cid (pk)
category


Merchants
-----------
mid (pk)
merchant


Products
-----------
pid (pk)
cid (fk)
mid (fk)
product


Basically, I want to search for all products whose category is television, the following SQL works fine for that:

SELECT p.product
FROM products AS p
INNER JOIN categories AS c USING (cid)
WHERE c.categories = 'television'

However, I would like to also display the merchant who is selling the television, so I added another inner join like so:

SELECT p.product
FROM products AS p
INNER JOIN categories AS c USING (cid)
INNER JOIN merchants AS m USING (mid)
WHERE c.categories = 'television'

but unfortunately I get the following error message:

"MySQL said: Unknown column 'temp.c.mid' in 'on clause'"

I think the problem is that I can't do two inner joins on a table if it has got two foreign keys on it, is there a way around this.

Many thanks.

I'm not familiar with MySQL but your statement looks strange (I'm not saying it's wrong). What's strange to me is you specify what column to use for join, but not what table this column belongs to. Then if you have 2 tables with the same column it can't work. Hopfully ANSI SQL is supported by MySQL then one of the following could work:

Thanks, you were right. It was because I was using "USING (cid)" instead of "ON c.cid = p.cid", although it worked fine for a table with one foreign key.

Both of your examples worked ok.

Cheers.