One table is for a list of products, the sub-query is for the customers who have purchased products. I basically want to display the products in a table and show how many customers have purchased each product -- thus the quasi-field.
I could use an actual field and just update it accordingly, but that would require a fare amount of retro-fitting my code and the list isn't that popular so...I figure I'll just calculate the field count dynamically and be done with it.
Just realized as I try and get this query working. The sub-query depends on the PKID of each record queries in the primary query -- how do I get that information into the sub-query? :S
You can reference the columns you wish to join in the WHERE clause just like you would any join. However, as I suspected there is no need for a subquery at all..
SELECT id, productname, COUNT(products_orders.id) AS `purchased` FROM products
LEFT JOIN products_orders ON products_orders.product_id = products.id
GROUP BY products.id
SELECT id, productname, IF(products_orders.id <> NULL, COUNT(products_orders.id), 0) AS `purchased` FROM products
LEFT JOIN products_orders ON products_orders.product_id = products.id
GROUP BY products.id
I can't remember by default whether a JOIN is a LEFT or INNER, so I always explicitly state it.