* Vince LaMonica
[...]
> I'm currently attempting this by doing [only trying priority 1 and 2
> right now]:
>
> SELECT
> papers.id,
> a1.last_name as auth1,
> a2.last_name as auth2,
> papers.year
> FROM
> paper_authors, papers,
> authors AS a1
> left join authors AS a2 ON (a2.id = paper_authors.author_id
> AND
> paper_authors.paper_id = papers.id AND paper_authors.priority = '2')
> WHERE
> a1.id = paper_authors.author_id
> AND paper_authors.priority = '1'
> AND paper_authors.paper_id = papers.id
> ORDER BY
> year ASC
This is wrong, because "paper_authors.priority = '1'" in the where clause
will prevent you from finding any paper_authors with priority = '2'. You
must put this criteria in the ON clause of the LEFT JOIN:
SELECT papers.id,
a1.last_name as auth1,
a2.last_name as auth2,
papers.year
FROM papers
LEFT JOIN paper_authors pa1 ON
pa1.paper_id = papers.id AND pa1.priority = 1
LEFT JOIN authors a1 ON
pa1.author_id = a1.id
LEFT JOIN paper_authors pa2 ON
pa2.paper_id = papers.id AND pa2.priority = 2
LEFT JOIN authors a2 ON
pa2.author_id = a2.id
ORDER BY year ASC
Another tip: If you rename autors.id to autors.author_id and papers.id to
papers.paper_id, you can use NATURAL JOIN, which makes things a bit simpler:
SELECT papers.id, last_name, priority, papers.year
FROM papers
NATURAL JOIN paper_authors
NATURAL JOIN authors
WHERE priority IN (1,2)
ORDER BY year ASC
(This is a different query: it returns all paper/author pairs matching the
WHERE clause (i.e., priority 1 or 2), one author for each row in the result
set, while the first query returns two authors in one row for each paper.)
The 'NATURAL JOIN' is described like this in the manual:
* The `NATURAL [LEFT] JOIN' of two tables is defined to be
semantically equivalent to a `INNER JOIN' or a `LEFT JOIN' with a
`USING' clause that names all columns that exist in both tables.
HTH,
--
Roger
sql
---------------------------------------------------------------------
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
