DB Query JOIN Statement

[CrzySdrs]

I have a database with two tables, one is Tasks, the other is People. In the tasks table, I have two columns, assignedby and assignedto which both hold the primary keys for different people in the People table, of which I am trying to get their real names from.

I am trying to combine three querys into one, but I seem to be running into a snag.

Original Querys:

set rs = conn.execute("SELECT * FROM tasks ORDER BY enddate ASC")
set rs2 = conn.execute("SELECT fullname FROM people WHERE uID=" & rs("assignedby"))
set rs3 = conn.execute("SELECT fullname FROM people WHERE uID=" & rs("assignedto"))


rs2 and rs3 queries must be run every time I pull up a different task. I am trying to use the inner join function to pull all of this together and my attempt at it is:


set rs = conn.execute("SELECT * FROM tasks INNER JOIN People ON tasks.assignedto=people.uid ORDER BY enddate ASC")


My problem is I want to get the names for both people, the assigner and the assignee, and the current will only get one of them. I can't seem to write a query that would be able to grab both names in a way I could use.

[whammy]

Hmm... ok what are your primary and foreign keys?

[CrzySdrs]

Primary Key For 'Tasks' is the tID
Primary Key For 'People" is the uID

[whammy]

Ok... well now I'm looking at your 2nd and 3rd queries, and you're pulling people by the same "foreign key" (uID).


set rs2 = conn.execute("SELECT fullname FROM people WHERE uID=" & rs("assignedby"))
set rs3 = conn.execute("SELECT fullname FROM people WHERE uID=" & rs("assignedto"))


YOu have not explained why you're using the EXACT same query, or how these differ. It seems we're not seeing all of your code or what you're really trying to do.

It seems there's a logic error in your coding if "assignedby" and "assignedto" have the same foreign key, as well... how could that possibly work?!?

Better yet, disregard my previous statement, and please explain in English what you are are trying to do.

[CrzySdrs]

Okay, let me try and re-explain it. I have a table named 'Tasks' with two columns (assignedby, assignedto) which are both numbers which are the uID's of different people. I am trying to pull up the peoples names which are stored in the 'People' table, which are identified by the uID. I am trying to write a statement that can grab all the task information contained in the 'Tasks' table, and get the referential data of the names from the 'People' table of both people listed for the task, the 'assignedto' and the 'assignedby'.

[oracleguy]

I think I got an idea on what your doing but without more specific information, this is the best I could come up with at the moment.

SELECT * FROM
(
tasks
left join
(SELECT fullname as Ab_fullname FROM people)
as ab
on as.uID=tasks.assignedby
)
left join
(SELECT fullname as At_fullname FROM people)
as at
on
at.uID=tasks.assignedto
ORDER BY
enddate ASC


Obviously this is an untested query though.