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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ryos
Forum Newbie
Posts: 16
Joined: Tue Feb 14, 2006 4:55 pm

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

Post 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!
Last edited by ryos on Mon Oct 01, 2007 12:52 pm, edited 1 time in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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
ryos
Forum Newbie
Posts: 16
Joined: Tue Feb 14, 2006 4:55 pm

Post by ryos »

That does it. Thanks a million! :)
Post Reply