MySql mutliple tables

Question

I've been looking into JOIN, subqueries and other ways of doing this, but I can't work out the best way to do this is...

I have a table (ps_category_product): id_product, id_category

I want to perform a query on it like: SELECT id_product FROM ps_category_product WHERE id_category='$this_cat'

BUT, I only want to perform this query where the ID's are returned by a query on another table (ps_product): id_product, active

SELECT id_product FROM ps_product WHERE active='1'

Can anyone help me with getting these two queries working together?

Answer 1

Use like this,

SELECT a.id_product FROM ps_category_product as a, ps_product as b WHERE a.id_category='$this_cat' and a.id_product = b.id_product and b.active='1'

Answer 2

What about something like this:

SELECT pp.id_product FROM ps_category_product pcp
INNER JOIN ps_product pp ON pp.id_product = pcp.id_product
WHERE pp.active = '1'