Do I need a SQL UNION for this? Awkward SQL query! (URGENT)

VBKid04
04-15-2004, 01:20 AM
I have a system here with which I need to do a SQL query on that I cant seem to figure out how to perform...


The basis of my system is that a User can use the system to post/register a Query that they need Help or an answer for (kind of like here).

I have 4 tables (that are important here) with the following fields:

USER
UserID, UserSurname, UserFirstName, CustID, TaskID

QUERY
QueryID, QueryDetails, QueryStatus, UserID, AdminID, CustomerID, TaskID

TASK
TaskID, TaskInfo, SolutionID, UserID, AdminID

SOLUTION
SolutionID, SolutionInfo, TaskID, AdminID


THE USERS ROLE:
So, a User uses the system to Login, then they post/register a Query (into the Query Table) that they need help or a solution for (kind of like here).

There already exists a Task database, full of tasks (kind of like the FAQ here), so using Keywords they will try to match the closest related Task, or if none exists, create a new Task. Query.TaskID will then be updated with this value to show that it is being looked into/that a related/similar Task has been assigned to it - and is awaiting a Solution.


THE ADMINS ROLE:
An Admin will then look at the Task (the Admins do not go near the Query at all), and try to supply a Solution for that Task (either an existing Solution or create a new one), and will record it in the Solution table, along with their AdminID and the TaskID.

The SolutionID is then automatically passed back to the Task.SolutionID (the SolutionID in Task is updated with the new value)-to show that the particular TaskID has been solved.

The user should then be able to go and view a list of tasks that have been solved for them (ie. Where the QueryID.QueryStatus is Solved and the UserID matches the user that is using the system).


The problem is that I need to get a recordset of:

Select all Solutions WHERE the SolutionID is in Task, that match any Tasks that the User may have submitted
(where the Username is got from a textbox txtLogin on the form)


Can anyone please help? All I need to know is how to perform the SQL query.

(sorry if that was long-winded but I had to explain it otherwise it would be totally confusing)

malloc
04-15-2004, 01:37 AM
Is this what you're looking for?

SELECT * FROM Solution, Task WHERE Solution.SolutionID = Task.SolutionID AND Task.UserID = '" & txtLogin.Text & "';"


Malloc

MKoslof
04-15-2004, 07:45 AM
Not sure how your table is designed and where each field resides, but something like:



"SELECT Solution.*, Tasks.UserID FROM Solution INNER JOIN Tasks ON(Solution.SolutionID = Task.SolutionID) WHERE Task.UserID = '" & txtLogin.Text & "'"

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum