Tom Jakobsen wrote:
> Could you explain this a little more.
> Never used LEFT JOIN before, only get error when trying it.
If you really want to learn about LEFT JOIN then may I suggest
http://www.mysql.com/documentation/manual.php?section=JOIN
As for the query that I wrote, I used "status" when I should
have used "invoice_status":
SELECT i.ID
FROM invoice i
LEFT JOIN invoice_status s1 ON s1.Invoice=i.ID AND s1.Status=1
LEFT JOIN invoice_status s2 ON s2.Invoice=i.ID AND s2.Status=2
WHERE s1.Status=1
AND s2.Status IS NULL
This query actually works to list invoices who have
a status 1 entry but not a status 2 entry. Hmm,
come to think of it, if all you want is the invoice
number you don't even need the invoice table:
SELECT s1.Invoice
FROM invoice_status s1
LEFT JOIN invoice_status s2 ON s2.Invoice=s1.Invoice AND s2.Status=2
WHERE s1.Status=1
AND s2.Status IS NULL
Does anybody know if this is legitimate SQL to use the
same table twice or have I just made use of a quirk
that happens to work?
I liked the other example which was something like:
SELECT Invoice
FROM invoice_status
WHERE Status=1 AND Created=MAX(Created)
GROUP BY Invoice
But couldn't get it to work. "Invalid use of group function"
And of course, you can use a PHP loop to find the ones you
want, but where's the fun in that? :-)
--
Paul Chamberlain, [EMAIL PROTECTED]
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Site: http://www.working-dogs.com/freetrade/
Problems?: [EMAIL PROTECTED]
