Page 1 of 1

[Solved] Left join, limiting rows returned from left table?

Posted: Sat Sep 29, 2007 7:38 pm
by ryos
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!

Posted: Sat Sep 29, 2007 10:11 pm
by califdon

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

Posted: Mon Oct 01, 2007 12:51 pm
by ryos
That does it. Thanks a million! :)