Query problem

[oliverg]

Hello,

If I have a database with two tables, jobs and staff, how would I link staff.ID to two seperate fields in the jobs table jobs.staff1, jobs.staff2?

I can make the relationships, but the query is the problem, I get a Exp004 field with no data in it.

Thanks,
Oliver

 

[r3plica]

post you query and show the records it returns so we can look at it. You might also want to tell us what columns exist in both tables and which are the primary and foriegn keys (if any) so we can help you better.

 

[oliverg]

Hello,

I have two tables: Jobs and Staff

Jobs fields:

ID (number)
Code (text)
Staff1 (number)
Staff2 (number)

Staff fields:

ID (number)
firstName (text)

Relationships:

Staff.ID > one to many > Jobs.Staff1
Staff.ID > one to many > Jobs.Staff2


Query:

SELECT Jobs.Code, Staff.firstName FROM Jobs, Staff WHERE Jobs.Staff1 = Staff.ID AND Jobs.Staff2 = Staff.ID

The query is the problem, how do I get two relational data values from the same field? I need to assign two different staff members to the jobs table, but there is only one staff table, containing details of both reference staff members.

Am I going to have to perform two queries?

Cheers

 

[Arg81]

What exactly are you trying to achieve? To have a list of all Jobs but show the first name instead of the ID?

What are you running this query on? SQL or within the Visual Studio designer?


I don't think you need to do anything on the query. Personally I have a table where it records
"Account Manager"
"Project Leader"

I then have another table called Employees that list names. EmployeeID, EmployeeName, EmployeeType - this is an integer column; 1 = account manager, 2 = project leader, 3 = another role

In my main table, it just records the Employee ID.

In my app, on the DataTable is where I create my queries.
I have queries such as
- SELECT * FROM Projects WHERE Employee.EmployeeTypeID = 1 ("Account Manager")
- SEELCT * FROM Projects WHERE Employee.EmployeeTypeID = 2 ("Project Manager")

You would simply do the same. In your app you would have combo boxes that display the name instead of the ID (you would need to create 2 dataTables - Staff1 and Staff2, both connecting to the Staff table)
You would then have queries such as:
- SELECT * FROM Jobs
- SELECT * FROM Jobs WHERE Jobs.Staff1 = @Staff1ID (parameter query, you would then use your app to select a member of staff from a list, and all jobs would return for that staff member if the ID is in Jobs.Staff1)
- SELECT * FROM Jobs WHERE (Jobs.Staff1 = @StaffID) OR (Jobs.Staff2 = @StaffID) | This parameter query will return all jobs where selected staff member's ID is in either jobs.staff1 field or jobs.staff2

etc etc...

I don't know if I've got the wrong end of the stick, but not sure what exactly you want to achieve with this query within your application.

 

[oliverg]

So two queries by the looks of it?

I want to list all the jobs in the jobs table, in a datagrid along with, as you said, the staff names.

I wasn't sure if I could get this into a nice simple query, where the staff.firstname was returned from both jobs.staff1 and jobs.staff2.

I'm guessing I can't. I'll just put the data together in bits and bobs.

Cheers.

 

[rcombs4]

You can try playing with subqueries as well.

SELECT J.CODE,
     (SELECT FirstName FROM Staff S WHERE S.ID = J.Staff1),
     (SELECT FirstName FROM Staff S WHERE S.ID = J.Staff2)
FROM Jobs J

 

[oliverg]

Cheers, that'll do nicely.

 

[Arg81]

Ive generally kept away from subqueries (no real reason)

Wouldn't there be an issue with that if the Job's table started to get a lot of rows? Wouldn't the 2 subqueries put more processing onto the SQL server?


Regarding my post, no it'll be one query. SELECT * FROM Jobs.
You'd have two dataTables in your DataSet for Staff1 and Staff2.
Your datagrid would then use combobox to show dataMember (in this case name) instead of valueMember (StaffID).
The benefit of this way is if you wanted to create searches based on a user selection for the names.