[thelist] MySQL problemo

[rudy]

hi jim

first of all, in order to select just the "used" keyphrases, you were
definitely on the right track by joining the keyphrases table to the
pages_phrases table

> $query = "SELECT * FROM keyphrases AS kp,
> pages_phrases AS pph" .
> "WHERE kp_phrase LIKE '" . strtolower($letter) . "%'
> OR kp_phrase LIKE '" . strtoupper($letter) . "%' " .
> "AND kp.kp_id = pph.kp_id " .
> "GROUP BY kp.kp_id ORDER BY kp_phrase";
>
> This correctly lists the keyphrases for the letter in
> alphabetical order, but still maintains those that do
> not have any associated page relationships.

as a born pedant, i would argue that it is not even syntactically correct,
because the query uses the non-standard mysql proprietary extension that
allows you to group on only some of the non-aggregate fields in the select
list

that's easy to fix, but it would still produce erroneous results because of
the precedence of ANDs and ORs

your query says   WHERE  x   OR  (  y  AND  joincondition )

that's easy to fix too, by moving the join condition into the FROM clause

note that by going from the "one" table to the "many" table, i.e. one
keyphrase row linked to one or more pages_phrases rows, you definitely need
GROUP BY, but -- here's the trick -- you don't actually have to select
anything from the pages_phrases table

   select kp.kp_id
        , kp_phrase
       from keyphrases AS kp,
   inner
     join pages_phrases AS pph
       on kp.kp_id = pph.kp_id
    where kp_phrase LIKE 'a%'
       or kp_phrase LIKE 'A%'
   group
       by kp.kp_id
        , kp_phrase
   order
       by kp_phrase

in this relatively rare case where you are producing groups without any
aggregate functions, you could use DISTINCT in the select list and leave
the GROUP BY clause off

but i prefer leaving it as a GROUP BY construction, because then you can
add COUNT(*) to the select list without further effort

rudy