2014-08-31 17:42 阅读 28


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'

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

 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>';
 case 45:
 $SymRow = '<tr class="Blue"><td colspan="2">&gt; <em>Family</em> <b>'.$Family.'</b>   ('.$Common.')</td></tr>';
 case 65:
 $SymRow = '
 <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>
 $SymRow = '';

...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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dongxian6741 dongxian6741 2014-08-31 18:00

    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'
    点赞 评论 复制链接分享