[thelist] MySQL problemo
rudy
r937 at interlog.com
Tue Aug 20 09:40:01 CDT 2002
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
More information about the thelist
mailing list