...

View Full Version : Select JOIN help



aaronl
04-27-2009, 09:15 PM
Im trying to query my database to see which users arent assigned to a task and display only those.

my tables are set up like so:

assignments:
http://intranet.juicedcreative.com/misc/assignments.png

users:
http://intranet.juicedcreative.com/misc/users.png

so in my assignments, I can get it to show me everyone whos know assigned to ANY tasks, but not just specifically to that task..



$userquery = "SELECT * FROM users LEFT JOIN assignments ON users.id = assignments.userid Where assignments.todoid != $listid and assignments.userid is NULL order by full_name asc";


is what Im using (this returned nothing, but if I removed the assignments.todoid != $listid part then I get everybody not assigned to anything)

Please help me.. im sure its somethings stupid and easy that i just cant see.

Fumigator
04-27-2009, 09:40 PM
I don't follow. Your requirement:


Im trying to query my database to see which users arent assigned to a task and display only those.

Seems to have been accomplished according to what you say here:


if I removed the assignments.todoid != $listid part then I get everybody not assigned to anything

aaronl
04-27-2009, 09:45 PM
Sorry, that does sound confusing....
if there are a,b,c,d as users I need to return the ones taht arent assigned to that specific task
task 1 (needs to return d)
a
b
c

task 2 (needs to return c and d)
a
b

task 3 (needs to return b and d)
a
c


what I have would only return D for all of them because its not assigned to ANY of the tasks...

Thanks!

Old Pedant
04-27-2009, 11:59 PM
So what you are calling "task 1" in this new post is the same as "todoid" in your first post, yes?

Let's re-create your tables but showing data that matches your newer post description. Simplifying a little for sake of brevity:


Table: assignments
todoid :: userid
1 :: a
1 :: b
1 :: c
2 :: a
2 :: b
3 :: a
3 :: c

Table: users
userid :: name
a :: abigail
b :: brian
c :: cora
d :: doug


Okay so far? (Used letters for userid in both tables for clarity only.)

So there's probably a simpler way to do this, but this seems to work:


CREATE VIEW fullOuter
AS
SELECT CAST(A.todoid AS VARCHAR) + ':' + CAST(U.userid AS VARCHAR) AS fullid, A.todoid, U.userid
FROM assignments AS A, users AS U
WHERE 1 = 1;
// (the WHERE clause there may not be needed, but won't hurt)

CREATE VIEW matchesOnly
AS
SELECT CAST(A.todoid AS VARCHAR) + ':' + CAST(U.userid AS VARCHAR) AS fullid
FROM assignments AS A, users AS U
WHERE A.userid = U.userid;

CREATE VIEW notAssigned
AS
SELECT fullid, todoid
FROM fullOuter
WHERE fullOuter.fullid NOT IN (
SELECT fullid FROM matchesOnly
);

SELECT NA.todoid, NA.userid, U.name
FROM
notAssigned AS NA, users AS U
WHERE NA.userid = U.userid
ORDER BY NA.todoid, U.name;



Naturally you can pull some (all?) of those VIEWs into the main query. I just find it easier to think of the problem if I created the VIEWs first.

Basically, that first VIEW produces a full outer join (a.k.a. "cartesian product") of all tasks with all users.

Then the second VIEW produces a list of all users *AND* their assigned tasks.

You can hopefully see that if you subtract the second view from the first one, you get all the task/user combinations that are *NOT* assigned. And that's all the third view does.

So then you can join that third view back to the original table(s) to get all the data you need.

Clearly, you could have the full outer join (first view, "fullOuter") return *all* the fields you need, so that then the third view ("notAssigned") would be the end product. Whether you want to do that or not depends on how much data you will be working with and/or how efficiently MySQL can handle this. My guess is that you could indeed do that, thus saving a step. And, more than likely, you can actually combine steps one and three. (You can probably even combine all three steps, but I just find *especially* step two to be attractive as something outside the main query.)

Old Pedant
04-28-2009, 12:00 AM
By the by, I *did* test this (albeit with Access...sorry!) and it does produce the right results.

aaronl
04-28-2009, 03:57 PM
holy crap.. i figured it was just something simple that I was forgetting about... I'll try this out and see if it works.. thanks!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum