Tricky MySQL query: join, union, subquery or other???

[cubik]

[pre]i have two tables:

 

table_1: customers

+-------------+-----------------+

| customer_id | name            |

+-------------+-----------------+

|          1 | Joe Doe        |

|          2 | Fred Hitman    |

|          3 | Sam Coolguy    |

|          4 | Jennifer Art    |

+-------------+-----------------+

 

table_2: orders

+-----------+-------------+--------+

| order_id  | customer_id | status |

+-----------+-------------+--------+

|        1 |          2 | Open  |

|        2 |          1 | Closed |

|        3 |          4 | Open  |

|        4 |          1 | Open  |

|        5 |          1 | Closed |

|        6 |          3 | Closed |

+-----------+-------------+--------+

 

i need a MySQL query that would return the following result:

 

+-------------+----------+--------+

| customer_id | order_id | status |

+-------------+----------+--------+

|          1 |        4 | Open  |

|          2 |        1 | Open  |

|          3 |    Null | Null  |

|          4 |        3 | Open  |

+-------------+----------+--------+

 

table_1 must return all the rows and

when table_2 must be filter on the column 'status' (where status = 'Open') and then be 'join' to table_1

Null should be displayed if  customer_id doesn't exist in table_2 or exist but 'status' != 'Open'

 

it shouldn't be that complicated but i couldn't find anything similar to my problem.

 

Any idea would be greatly appreciated.

 

Thank you.

 

 

 

[/pre]

[fenway]

You should be able to left join the orders table to the customers table and add the status = 'open' to your on clause.

[cubik]

[pre]

it doesn't work, if do :

 

select * from customers as c

left join orders as o ON c.customer_id = o.customer_id

where o.status = 'Open'

 

then the customer_id #3 is not in the result.

 

i need ALL the customers in the result set but with Null data if the clause on table_2 is not filled.

 

Thank you anyway.

[/pre]

[fenway]

I said to put o.status = 'open' in the *on* clause, not the *where* clause.

[cubik]

you are the best !!!

it's working perfectly !!!

 

thank you so much!!!!