0

MYSQL Left join A.table and b.table while retaining a.table id when there is no b.table match.

SELECT * FROM sales_customer a LEFT JOIN sales_contact b
ON a.customer_id = b.customer_id
ORDER BY CASE WHEN company_name = '' THEN lname ELSE company_name END ASC

Gets me this:

Array (
  [0] => 54
  [customer_id] => 
)

When there is no b.table match.

I need this:

Array (
  [0] => 54
  [customer_id] => 29 
)

Any suggestions?

The solution below worked. Thanks for your help.

SELECT *, COALESCE(a.customer_id, 0) AS customer_id FROM sales_customer a LEFT OUTER JOIN sales_contact b ON a.customer_id = b.customer_id ORDER BY CASE WHEN company_name = '' THEN lname ELSE company_name END ASC

Lawrence Gadette
  • 75
  • 1
  • 2
  • 7

3 Answers3

1

Try using COALESCE(). It will return the first non-NULL value.

user1032531
  • 24,767
  • 68
  • 217
  • 387
  • I think you're misunderstanding the OP's problem. `a.customer_id` will never be `NULL`. – ruakh Mar 05 '12 at 15:08
1

Instead of writing SELECT * ("select all fields"), you should specify the fields you actually want: SELECT 54 AS `0`, a.customer_id, ... FROM .... That's better practice in general, not just for this.

If you really want to write SELECT * to select all fields, you can still add additional fields to the end, which in PHP will overwrite earlier like-named fields: SELECT *, a.customer_id FROM ....

ruakh
  • 175,680
  • 26
  • 273
  • 307
1

Use it like this:

SELECT 
    *,
    COALESCE(b.customer_id, 0) AS customer_id
FROM sales_customer a 
LEFT JOIN sales_contact b ON a.customer_id = b.customer_id
ORDER BY 
    CASE WHEN company_name = '' THEN lname ELSE company_name END ASC
Alex
  • 2,126
  • 3
  • 25
  • 47
  • Ok, first i would like to thank everyone for their help. Second the answer that worked best for me was actually a variation of this one. Ill post the solution above. Thanks again. – Lawrence Gadette Mar 05 '12 at 15:22