Member Avatar for ray_broome

Hi, I have the following query i'm trying to execute:

SELECT CONCAT_WS(' ', g.genus_name, s.species_name, i.rank, i.infraspecies_name, conf.auth1, conf.auth2 ) AS scientific_name, 
cn.common_name, GROUP_CONCAT(cn.common_name ORDER BY cn.common_name SEPARATOR ', ' ) AS common_names, ui.unvouch_image_src, ui.unvouch_image_alt, gh.growth_habit, gd.gen_distrib_name 
FROM plants AS p 
LEFT JOIN (genera AS g, species AS s) 
ON (p.genus_num = g.genus_id AND p.species_num = s.species_id)
LEFT JOIN (confirmations AS conf) 
ON (p.plant_id = conf.plant_num)
LEFT JOIN (plant_growth_habits AS pgh, growth_habits AS gh)
ON (p.plant_id = pgh.plant_num)
LEFT JOIN (plant_distributions AS pd, gen_distribs AS gd)
ON (p.plant_id = pd.plant_num)
LEFT JOIN (plant_common_names AS pcn, common_names AS cn) 
ON (p.plant_id = pcn.plant_num)
LEFT JOIN (unvouchered_images AS ui) 
ON (p.plant_id = ui.plant_num)
LEFT JOIN (infraspecies AS i) 
ON (p.infraspecies_num = i.infraspecies_id)
WHERE scientific_name LIKE '%sesuvium%'
GROUP BY scientific_name ORDER BY scientific_name

I only recently added the last 3/4 join statements but before those it was working, i'm not sure if there's a problem with my joins/join syntax any help would be greatly appreciated.

  • 1 Contributor
  • 1 Reply
  • 168 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ray_broome
Member Avatar for ray_broome

Ok it looks like i got the crashing problem fixed by reworking my query to:

SELECT 
CONCAT_WS(' ',g.genus_name,s.species_name,i.rank,i.infraspecies_name,
conf.auth1,conf.auth2) AS scientific_name, cn.common_name,
GROUP_CONCAT(DISTINCT cn.common_name ORDER BY cn.common_name SEPARATOR ', ' ) AS common_names, 
ui.unvouch_image_src, ui.unvouch_image_alt, gh.growth_habit, GROUP_CONCAT(DISTINCT gh.growth_habit ORDER BY gh.growth_habit SEPARATOR ', ') AS growth_habits,
gd.gen_distrib_name, GROUP_CONCAT(DISTINCT gd.gen_distrib_name ORDER BY gd.gen_distrib_name SEPARATOR ', ') AS general_dists
FROM plants AS p 
 LEFT JOIN genera AS g 
	ON g.genus_id = p.genus_num
 LEFT JOIN species AS s 
	ON s.species_id = p.species_num
 LEFT JOIN confirmations AS conf 
	ON conf.plant_num = p.plant_id
 LEFT JOIN (plant_growth_habits AS pgh INNER JOIN growth_habits AS gh ON gh.growth_habit_id = pgh.growth_habit_num) 
	ON pgh.plant_num = p.plant_id
 LEFT JOIN (plant_distributions AS pd INNER JOIN gen_distribs AS gd ON gd.gen_distrib_id = pd.gen_distrib_num) 
	ON pd.plant_num = p.plant_id
 LEFT JOIN (plant_common_names AS pcn INNER JOIN common_names AS cn ON cn.common_name_id = pcn.common_name_num) 
	ON pcn.plant_num = p.plant_id 
 LEFT JOIN unvouchered_images AS ui
	ON p.plant_id = ui.plant_num
 LEFT JOIN infraspecies AS i 
	ON p.infraspecies_num = i.infraspecies_id
WHERE scientific_name LIKE '%sesuvium%' GROUP BY scientific_name ORDER BY scientific_name

the above query seems to be working fine but i got onnneee not so big issue i was wondering about. This line here:

CONCAT_WS(' ',g.genus_name,s.species_name,i.rank,i.infraspecies_name,conf.auth1,conf.auth2) AS scientific_name

works and everything but there will not always be a value for conf.auth1 or conf.auth2 and the concatenation sometimes prints say for e.g. "Sesuvium microphyllum Willd. NULL" where there was no value for conf.auth2. Is this the default behaviour of concat_ws, can i get around this or would i just have to strip that out using php?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.