Desmond Lee wrote:
> Hi there
>
> I want to do a left outer join but i also have a bunch of where conditions
> in my sql statment that i need. Can anyone help me out:
>
> SELECT title, authorName, journalName, journalPages, aname, tname,
> avg(ranking)
> FROM publications, area, topic, pubTopicInt,
> LEFT OUTER JOIN ranking publications.pubID = ranking.pubID
> WHERE publications.areaID = area.areaID
> AND
> publications.pubID = pubTopicInt.pubID
> AND
> topic.topicID = pubTopicInt.topicID
> AND
> publications.pubID = ranking.pubID
> AND
> publications.pubID = 3
> GROUP BY (publications.pubID);
>
> I look at the mysql documentation, but i couldn't really understand their
> examples when they had additional where cluases.
1. Specifying LEFT (or RIGHT) already excludes INNER,
so you don't need OUTER.
2. Is 'ranking' a column as in AVG( ranking ), a table
as in ranking.pubID, or both? Assuming it's both,
but from inspection rather than tryng to recreate your
schema, your FROM clause doesn't look right. Perhaps
it should be more like
FROM area, topic, pubTopicInt,
publications LEFT JOIN ranking
ON publications.pubID = ranking.pubID
3. In step 2 of its optimisation, MySQL moves all JOINs to
the WHERE clause. Once the query is returning results,
use
EXPLAIN <query>
to find what the parser makes of your sntax.
HTH
P.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
