Page 1 of 1

Return empty string when no connection with another table

Posted: Sat Jul 22, 2006 11:38 am
by MarK (CZ)
I hope it's not too misleading heading :)

I have this query:

Code: Select all

mysql> SELECT ranges.id, ranges_types.type as rtype, ranges.name, gps_n, gps_e 
       FROM ranges, ranges_types 
       WHERE ranges.id = '20' 
       AND ranges.type_id = ranges_types.id 
       LIMIT 1
The thing is, if ranges.type_id is '0' (that means no connection with the ranges_types table), nothing is returned because of course the second condition wasn't met.
I would like the query to return rtype as an empty string if there was no matching row in ranges_types table found. I could solve this problem via php and one more SQL query but is there an easier way - to edit the sql query to do what I need?

Thanks in advance ;)

Posted: Sat Jul 22, 2006 11:43 am
by Weirdan
use left join instead of inner:

Code: Select all

SELECT 
   ranges.id, ranges_types.type as rtype, ranges.name, gps_n, gps_e
FROM 
   ranges
LEFT JOIN
   ranges_types
ON ranges.type_id = ranges_types.id
WHERE 
   ranges.id = '20'
LIMIT 1

Posted: Sat Jul 22, 2006 12:15 pm
by MarK (CZ)
Works nicely, I've learnt new useful stuff, thanks! :)