条件何处条款（基于等级）

Question

I want to display a taxonomic list of state birds that looks something like this:

Order Falconiformes
Family Falconidae
peregrine falcon (Falco Perergrinus) | Idaho
Order Passeriformes
Family Turdidae
robin (Turdus migratorius) | Connecticut, Michigan, Wisconsin

I can't figure out how to make a single query that does what I want, so I'm trying to figure out a workaround.

This is my basic query:

$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,
));

I can then use this to style the rows...

switch($Rank)
{
 case 35:
 $SymRow = '<tr class="Red Center"><td colspan="2" style="border-bottom: 1px solid #fff;"> <em>Order</em> <b>'.$Order.'</b> ('.$Common.')</td></tr>';
 break;
 case 45:
 $SymRow = '<tr class="Blue"><td colspan="2">&gt; <em>Family</em> <b>'.$Family.'</b>   ('.$Common.')</td></tr>';
 break;
 case 65:
 $SymRow = '
 <tr>
 <td class="Symbol">&gt;&gt; <a href="'.$GZ_URL.'/life/'.$LatinL.'" title="'.$Symbol.' ('.$Latin2.')">'.$row['Common'].'</a> (<em>'.$Latin2.'</em>)</td>
 <td class="Place"><a href="/world/'.$PlaceL.'" title="'.$Place.'">'.$PlaceGroup.'</a>  </td>
 </tr>
';
 break;
 default:
 $SymRow = '';
 break;
}

...where a rank of 35 = an order, 45 = a family and 65 = a species. This works, but it displays EVERYTHING related to birds in the table gs_planimals. I want to display only U.S. state birds and their grandparents (families) and great grandparents (orders).

If I modify the WHERE clause to indicate a geographic parent, like this...

WHERE PLAN.Group1 = 'bird' AND Rank != '55' **AND GG.IDParent = 'usa'**

Then it works for species (rank 65). However, no families or orders display at all, presumably because families and orders aren't linked to the tables gs or gw_geog.

So I'd like to know if there's a way to write a conditional WHERE CLAUSE, something like this...

WHERE PLAN.Group1 = 'bird' AND Rank != '55' [AND **- if Rank = 65 -** GG.IDParent = 'usa']

If I can do this, then the table should still display every order and family in the table, but I can put together some PHP switches that manually delete orders and families I don't want to display.

Anyway, does anyone now how to modify a where clause like that? If not, can you suggest another workaround?

Answer 1

Relocate the predicate

GG.IDParent = 'usa'

from the WHERE clause to the ON clause of the LEFT JOIN

In the WHERE clause, that predicate negates the "outerness" of the LEFT JOIN. Any rows that don't have a "matching" row from GG, all of the GG. columns will be NULL, and those rows won't satisfy that predicate in the WHERE clause.

LEFT JOIN gw_geog GG ON GG.IDArea = GS.IDArea AND GG.IDParent = 'usa'
                                              ^^^^^^^^^^^^^^^^^^^^^^^