trying to add a table to an existing mysql query

mysql, php, inner join

[sasori]

I have this query:
SELECT * FROM products INNER JOIN products_categories ON products_categories.pid=products.pid AND products_categories.cid='270'
--------------------
I have to add a field from a different table that also uses 'pid'. However, when I try:
--------------------
SELECT products.*, psp.price AS sprice FROM products, psp INNER JOIN products_categories ON products_categories.pid=products.pid AND products_categories.cid='270'
-------------------
I get an error:
Unknown column 'products.pid' in 'on clause'

where can I put the psp table in this query? The INNER JOIN is necessary because there may be more than one category per pid.

Thanks for helping.

[Demaestro]

It looks like it is the order you are joining.

You are wanting to join the tables product_categories and products but your statement reads like you are joining the psp table.

SELECT
products.*, psp.price AS sprice
FROM
products,
psp INNER JOIN products_categories ON products_categories.pid=products.pid
AND products_categories.cid='270'

By changing the order your tables appear in the statement you should be able to fix it.... like this

SELECT
products.*, psp.price AS sprice
FROM
psp,
products INNER JOIN products_categories ON products_categories.pid=products.pid
AND products_categories.cid='270'

Notice how the products table is part of the inner join now?

The way you had it psp and product_category were the inner join but you were joining on products.pid because the products table isn't part of that join statement it doesn't see the products table.... hope this makes sense and that it works this way...

post back and let us know how it goes.

[sasori]

Thanks for the help.
That definitely got a result, but the sprice values are not correct. Its showing the first entry in the psp table, where pid =1 and price = 40. I've confirmed its that field.

I tried to include psp.pid in the inner join, without success.

What could be missing?

Thanks!

[sasori]

ok, I've added the psp.pid to the select clause and to the where clause and got it working.

wait, are those 'clauses'?

Thanks!