mysql left join but return blank row

Question

Hi, i have a small problem where i have this setup...

Table: trade names { trade_id : 1 trade_name : olivers guest house}

Table Customer { name: me, trade_id: 1 : blah: blah}

i do a left join to get the trade name into customers as if you are a guest house you will have a trade name but as a landlord you will not have a trade name, but i still want a result to come back.

so all is good.... nope. i display the results in a table which has a search by postcode AND trade.

i do a select where postcode like %postcode% AND trade like %trade% which will bring back the criteria that match.

As the left join wont bring back a trade for the landlord anything with out a trade is excluded from the results.

I was wondering if there was some way to say if the row does not exist return a blank field.

so it would be like this:

array([0] => array([0] =>[name] = "something" ["trade_name"] = "olivers guest house" [type] = "guest house").
             array[0] => [name] = "something" ["trade_name"] = " " [type] = "landlord");

as currently i get

array([0] => array([0] =>[name] = "something" ["trade_name"] = "olivers guest house" [type] = "guest house").
             array[0] => [name] = "something" ["trade_name"] = "NULL" [type] = "landlord");

which the where trade_name like %trade% cant work on.

I hope i havent over complicated the description.

i hope there is just some type of join that would do it but i cant see one :(

Thankys guys

Answer 1

In your query (which I think you haven't posted) you should replace:

SELECT trade_name, ....

with:

SELECT COALESCE(trade_name, ' '), ....

or alternatively you can use the MySQL specific funciton IFNULL:

SELECT IFNULL(trade_name, ' '), ....

Answer 2

Thank you mark, your suggestion worked :)

happy days