I have a rather simple query which looks to combine all results from another table into one json object.
MySQL
SELECT s.id, s.uid, s.sexnumber, s.rating, s.sextime, s.diary, GROUP_CONCAT(p.pid,',') as positions, GROUP_CONCAT(w.id,':',w.who) as who, GROUP_CONCAT('{',
'\"lat\":',l.lat,
',\"lon\":',l.lon,
',\"house\":',l.house,
',\"house_number\":',l.house_number,
',\"road\":',l.road,
',\"supermarket\":',l.supermarket,
',\"city\":',l.city,
',\"city_district\":',l.city_district,
',\"country\":',l.country,
',\"country_code\":',l.country_code,
',\"county\":',l.county,
',\"neighbourhood\":',l.neighbourhood,
',\"pedestrian\":',l.pedestrian,
',\"place_of_worship\":',l.place_of_worship,
',\"postcode\":',l.postcode,
',\"state\":',l.state,
',\"suburb\":',l.suburb,
'}'
) as location, GROUP_CONCAT(ww.id,':',ww.name) as place
FROM users u join sex s
on s.uid = u.uid
LEFT OUTER JOIN whos ws
ON s.id = ws.sid
LEFT OUTER JOIN who w
ON w.id = ws.wid
LEFT OUTER JOIN locations l
ON l.sid = s.id
LEFT OUTER JOIN wheresex whs
ON whs.sid = s.id
LEFT OUTER JOIN wherewhere ww
ON whs.wid = ww.id
LEFT OUTER JOIN positions p
ON s.id = p.sid
WHERE u.sessionCheck = '%s'
GROUP BY s.id
ORDER BY s.sextime DESC;
If any of the location results inside the GROUP_CONCAT are NULL then the entire entry will return NULL.
How can I have the individual results that are NULL return this way and anything else with their values?
I must admit that I haven't tried to execute this sort of query before, but I can't see how a coalesce wouldn't do the trick?
SELECT s.id, s.uid, s.sexnumber, s.rating, s.sextime, s.diary, GROUP_CONCAT(p.pid,',') as positions, GROUP_CONCAT(w.id,':',w.who) as who, GROUP_CONCAT('{',
'\"lat\":',coalesce(l.lat,'Unknown'),
',\"lon\":',coalesce(l.lon,'Unknown'),
',\"house\":',coalesce(l.house,'Unknown'),
',\"house_number\":',coalesce(l.house_number,'Unknown'),
',\"road\":',coalesce(l.road,'Unknown'),
',\"supermarket\":',coalesce(l.supermarket,'Unknown'),
',\"city\":',coalesce(l.city,'Unknown'),
',\"city_district\":',coalesce(l.city_district,'Unknown'),
',\"country\":',coalesce(l.country,'Unknown'),
',\"country_code\":',coalesce(l.country_code,'Unknown'),
',\"county\":',coalesce(l.county,'Unknown'),
',\"neighbourhood\":',coalesce(l.neighbourhood,'Unknown'),
',\"pedestrian\":'coalesce(,l.pedestrian,'Unknown'),
',\"place_of_worship\":',coalesce(l.place_of_worship,'Unknown'),
',\"postcode\":',coalesce(l.postcode,'Unknown'),
',\"state\":',coalesce(l.state,'Unknown'),
',\"suburb\":',coalesce(l.suburb,'Unknown'),
'}'
) as location, GROUP_CONCAT(ww.id,':',ww.name) as place
Edit: I am not sure if I understand your comment correctly, but if you want to add quotes around the values returned by the coalesce you could use a concat function like this:
'\"lat\":',concat('"',coalesce(l.lat,'Unknown'),'"')
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments