Return empty string when no connection with another 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
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Return empty string when no connection with another table

Post 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 ;)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post by MarK (CZ) »

Works nicely, I've learnt new useful stuff, thanks! :)
Post Reply