douyanqu9722 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 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'
    本回答被题主选为最佳回答 , 对您是否有帮助呢?



    • ¥15 Tpad api账户 api口令
    • ¥30 ppt进度条制作,vba语言
    • ¥15 stc12c5a60s2单片机测光敏ADC
    • ¥15 生信simpleaffy包下载
    • ¥15 请教一下simulink中S函数相关问题
    • ¥15 在二层网络中,掩码存在包含关系即可通信
    • ¥15 端口转发器解析失败不知道电脑设置了啥
    • ¥15 Latex算法流程图行号自定义
    • ¥15 关于#python#的问题:我在自己的电脑上运行起来总是报错,希望能给我一个详细的教程,(开发工具-github)
    • ¥40 基于51单片机实现球赛计分器功能