To find workers with projects, you join the worker table to the webprojectassign table.
To get all workers whether they have a project or not, you use LEFT JOIN, as Rory McKinley suggested. With a LEFT JOIN, "missing" rows from webprojectassign (the table on the right) are filled with NULLS.
So, to find workers without projects, you do a LEFT JOIN and look for the NULLs, like this:
SELECT worker.fname, worker.lname, worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC
In this case, as the workerid column name is the same in both tables, I could have used "USING (workerid)" in place of the ON clause.
Michael
Noamn wrote:
Rory wrote:
The query is behaving exactly as it should. Your query asks it to return
only those workers that are listed in the webprojectassign table. Seeing as there are
no entries in the webprojectassign table, no workers match and hence no results are
produced.
No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid
Is != a different way of writing <>?
If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program.
-----Original Message----- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select?
On 6 Jan 2004 at 1:00, Tyler Longren wrote:
Hi,
I'm baffled. Been lookin at this for the last hour now.
SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC;
That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the "workers" table? Could someone just explain to me why this doesn't work the way I expected it to?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
