How can I write this conditional where clause?

Databases
1

I can’t figure out how to do everything I want to do with a single query, so I’m working on a workaround.

This is the query I’m working with…


$stm = $pdo->prepare("SELECT PLAN.Latin Latin2, PLAN.Common, PLAN.Group1, PLAN.Rank,  PLAN.Family, PLAN.Order1, GS.Symbol, GS.Latin, GROUP_CONCAT(GG.Name ORDER BY GG.Name ASC  SEPARATOR ', ') as Names, GG.IDParent
FROM gs_planimals PLAN
LEFT JOIN gs GS ON GS.Latin = PLAN.Latin
LEFT JOIN gw_geog GG ON GG.IDArea = GS.IDArea
WHERE PLAN.Group1 = :RefCat AND Rank != '55'
GROUP BY PLAN.Common
ORDER BY PLAN.N, GG.Name");
$stm->execute(array(
'RefCat'=>$RefCat,
));

The problem is that it displays EVERYTHING in the table gs_planimals. If I want to display a list of state birds, then I want it to display only taxons (species and their grandparents and great grandparents - families and orders) that are associated with U.S. states.

I can achieve a partial fix by modifying the where clause, like this:


WHERE PLAN.Group1 = :RefCat AND Rank != '55' AND GG.IDParent = 'usa'

It now correctly displays only species linked to U.S. states in the database table gs. However, it doesn’t display any orders or families at all, presumably because they’re only listed in the database table gs_planimals and aren’t linked to the other tables.

So can anyone tell me how to modify my where clause conditionally, so the parent is ‘usa’ only for rows where the value for Rank is 65? It might look something like this:


WHERE PLAN.Group1 = :RefCat AND Rank != '55' AND (GG.IDParent = 'usa' - but only if Rank = '65')

Thanks.

2

I think you’d be better asking in the SQL section - although your query is wrapped in some php, the actual question is more about SQL syntax than php.

3

Moderator Note

topic moved

4

Your second where clause has a condition on a left joined table, turning the left join in a inner join.

Try something like this:

WHERE PLAN.Group1 = :RefCat
AND
( (Rank = ‘65’ AND GG.IDParent = ‘usa’) OR
(Rank != ‘55’) )

Didn’t test it so maybe you need to tweak it a bit.