Hello,
I basically have two queries that I want to fuse together, and I don't quite see a way to do it the way I want to.
Left query:
SELECT some fields FROM table1 WHERE language_id=1;
Right query:
SELECT some fields FROM table2 WHERE table2.products_id = table1.productsID
...so that every row returned from the left query contains the fields from the row from table2 that match the WHERE clause. The following would give me what I want:
SELECT table1.some_fields, table2.some_fields FROM table1 JOIN table2 ON table1.language_id=1 AND table2.products_id = table1.productsID
...except that I want to be able to return all rows from table1 that match the WHERE clause of the left query, regardless of whether or not there is a corresponding row in the right table. I get close by changing the JOIN to a LEFT JOIN, like so:
SELECT table1.some_fields, table2.some_fields FROM table1 LEFT JOIN table2 ON table1.language_id=1 AND table2.products_id = table1.productsID
...BUT! The table1.language_id=1 does not in fact exclude the rows where table1.language_id != 1.
Is there a way to do what I want using straight SQL? Or do I need post processing? (BTW, I'm using MySQL 4 and PHP 4.)
Thanks in advance for any help!
[Solved] Left join, limiting rows returned from left table?
Moderator: General Moderators
[Solved] Left join, limiting rows returned from left table?
Last edited by ryos on Mon Oct 01, 2007 12:52 pm, edited 1 time in total.
Code: Select all
SELECT table1.some_fields, table2.some_fields FROM table1 LEFT JOIN table2 ON table2.products_id = table1.productsID WHERE table1.language_id=1