This should be so easy, i'm ashamed of myself

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

This should be so easy, i'm ashamed of myself

Author

Topic

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-24 : 19:31:07

I have two tables. A table called jobs and and a table called job assignements.

The idea is that a job can be have many users assigned to it via the job_assignment table.

What I need to do is simply return a list of jobs where a x amount of users are assigned to it.

Heres my sql, but it only returns anything if I include one assigned user. As soon as I add two assigned users I get nothing even though I can see in the tables that they are assigned.

SELECT DISTINCT Master_Jobs.JobID, Master_Jobs.Job_Title
FROM Master_Jobs INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID

WHERE Job_Assignments.UserID = 5 AND Job_Assignments.UserID = 1;

heres an idea of my table Job_Assignments

FK_Master_JobID UserID
393 5
393 9
393 5
393 5
393 6
393 1

as you can see the two users 5 and 1 do exist in job 393?????

but I get nothing back!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 19:33:49

Switch AND to OR:

WHERE Job_Assignments.UserID = 5 OR Job_Assignments.UserID = 1

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-24 : 19:34:27

You are attempting relation division.. Do a search on google..

Oh never mind.. [url]http://www.dbazine.com/celko1.html[/url]

DavidM

"SQL-3 is an abomination.."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 19:39:49

I guess I misunderstood it then.

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-24 : 19:55:48

I don't know Tara.. he didn't provide much info really...

When ever I see some make the "AND" mistake I just assume division....

DavidM

"SQL-3 is an abomination.."

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-24 : 21:08:10

FK_Master_JobID UserID
393 5
393 9
393 5
393 5
393 6
393 1

From the example I gave at the beginning(above also) I would simply need returning the jobID 393. IE I need jobs with users 5 and 1 in them.

I read the article you gave me, thanks very interesting however Its a bit of long road to something that i thought would've been easier. Still havent got it yet.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-24 : 21:39:28

That is the only way to do division in a SQL DBMS (That includes Oracle, DB2 etc..)
It demonstrates one of the many problems of SQL..

Keep reading, you will figure it out.. If you don't, post the CREATE TABLE statements for both tables and supply the INSERT statements and we'll help you out...

DavidM

"SQL-3 is an abomination.."

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-24 : 21:54:39

This is the 2nd time in two days that I dont' seem to quite get the requirement.

What do you mean by

quote:
where a x amount of users are assigned to it.

It sounds like: "Return all jobs that have "X" (where X=0..N) users assigned. For example, show me the jobs that have 5 users assigned.

If that's the problem (and I'm not sure I've got it right...)

SELECT M.JobID, Count(*) as TotalUsers
FROM Master_Jobs M
INNER JOIN Job_Assignments J ON J.FD_Master_JobID = M.JobID
GROUP BY M.JobID
HAVING COUNT(*) = 5

that'll give all the JobIDs that have five, and only 5 users. 5 could be parameterized: @UserCount

Next you'll say: But I want all the UserID's too!

OK


SELECT UserID, FD_Master_JobID
FROM Job_Assignments J
WHERE FK_Master_JobID IN (
  SELECT M.JobID
  FROM Master_Jobs M
  INNER JOIN Job_Assignments J ON J.FD_Master_JobID = M.JobID
  GROUP BY M.JobID
  HAVING COUNT(*) = 5
)

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-24 : 22:44:43

I apologise if i've confused everybody.

SamC, thanks for your help pasted your suggestion into query analyser and it worked/compiled but it doesn't do what I need. But I also figure my explanation was at fault.

Your example is returning results from the wrong table. I need results returned from the Master_Jobs table. Putting it as has simply as I can. If I wanted to return a single job by its jobid lets say 5, being based on the foreign key[jobid], in the job_assignments table I would simply use : Where Job_Assignment.JobID = 5.

This would return any jobs from my Master_Jobs table if it happens to contain a 5 in both Master_Jobs and Job_Assignment. I want to do the same but say, lets get the a job that might have two users assigned to it.

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-24 : 23:28:34

Thanks for trying to clarify the requirement, but I'm still confused. A sample result set would help.

I think TARA was right. You need an OR, not an AND. The DISTINCT will remove duplicates. I don't see any comment from you on Tara's suggestion. What's wrong with it?

Here's another way of getting the same result set. It sorta reads nicely, depending on your point of view.


SELECT JobID, Job_Title
FROM Master_Jobs
WHERE JobID IN (
  SELECT FD_Master_JobID
  FROM Job_Assignments
  WHERE UserID IN (5, 1)
)

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-24 : 23:51:51

Neither of these work. To start with:

WHERE Job_Assignments.UserID = 5 OR Job_Assignments.UserID = 1

I dont want one or the other I only need to return a result (Job) if both users together exist in the Job_Assignements table for the same job.

The second suggestion

WHERE UserID IN (5, 1)

doesn't work because it returns everything from all users. For eg say I used this

WHERE Job_Assignments.UserID IN (5)

and userID 5 had one job assigned to him, then it would return one job, but if i do this:

WHERE Job_Assignments.UserID IN (5, 7)

and userID 7 has 3 jobs assigned to him then I will end up with four jobs.

When I only want jobs that exist for both, IE one job.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-02-25 : 00:10:05

This topic is driving me insane. Do any of these do what you need or are we all on another planet. :)

DECLARE @tmp_jobs TABLE(
job_id INT IDENTITY(1,1) PRIMARY KEY,
job_name VARCHAR(55))

DECLARE @tmp_users TABLE(
users_id INT IDENTITY(1,1) PRIMARY KEY,
users_name VARCHAR(55))

DECLARE @tmp_assignments TABLE(
assignment_id INT IDENTITY(1,1) PRIMARY KEY,
job_id INT,
users_id INT)

INSERT @tmp_jobs SELECT 'Job 1'
INSERT @tmp_jobs SELECT 'Job 2'
INSERT @tmp_jobs SELECT 'Job 3'

INSERT @tmp_users SELECT 'User 1'
INSERT @tmp_users SELECT 'User 2'
INSERT @tmp_users SELECT 'User 3'

INSERT @tmp_assignments SELECT 1,1
INSERT @tmp_assignments SELECT 1,2
INSERT @tmp_assignments SELECT 1,3
INSERT @tmp_assignments SELECT 2,1
INSERT @tmp_assignments SELECT 3,2
INSERT @tmp_assignments SELECT 3,3

SELECT * FROM @tmp_jobs
SELECT * FROM @tmp_users
SELECT * FROM @tmp_assignments

PRINT 'This will return jobs with a specific number of users attached.'
DECLARE @counter INT, @user1 INT, @user2 INT

SELECT @counter = 2, @user1 = 2, @user2 = 3

SELECT j.job_id,j.job_name
FROM @tmp_jobs j INNER JOIN @tmp_assignments a ON j.job_id = a.job_id
GROUP BY j.job_id, j.job_name
HAVING COUNT(*)>=@counter

PRINT 'This will return jobs with specific users attached.'
SELECT DISTINCT j.job_id,j.job_name
FROM @tmp_jobs j INNER JOIN @tmp_assignments a ON j.job_id = a.job_id
WHERE a.users_id IN (@user1,@user2)

PRINT 'Or you can do a combination if you are just really crazy and cant sleep.'
SELECT @counter = 3

SELECT DISTINCT j.job_id,j.job_name
FROM
@tmp_jobs j
INNER JOIN @tmp_assignments a ON j.job_id = a.job_id
INNER JOIN (
SELECT j1.job_id,j1.job_name
FROM @tmp_jobs j1 INNER JOIN @tmp_assignments a1 ON j1.job_id = a1.job_id
GROUP BY j1.job_id, j1.job_name
HAVING COUNT(*)>=@counter) j1 ON j.job_id = j1.job_id
WHERE a.users_id IN (@user1,@user2)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-25 : 00:22:00

thanks derricklegget:

This is what I wanted/did, thanks for you help anyways.

SELECT Master_Jobs.JobID
FROM Master_Jobs
WHERE EXISTS
(
SELECT *
FROM Job_Assignments AS ja1
WHERE Master_Jobs.JobID = ja1.FK_Master_JobID
AND ja1.UserID = 6
)
AND EXISTS
(
SELECT *
FROM Job_Assignments AS ja5
WHERE Master_Jobs.JobID = ja5.FK_Master_JobID
AND ja5.UserID = 6
)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-02-25 : 00:30:41

No prob. If you are going to use this a lot, you need to rewrite it though. The exists are more inefficient. The * will get you in trouble a lot also. Just a warning if the tables ever get really big and you have concurrency problems.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-02-25 : 00:46:33

thanks mate!!

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-25 : 09:50:05

This query is a little more general and get's the resultset for all jobs that have ALL the enumerated UserIDs assigned.

Parameterizing this query is a little more work, but it's easier to read without paramters:

SELECT JobID, Job_Title
FROM Master_Jobs
WHERE JobID IN (
  SELECT FK_Master_JobID
  FROM Job_Assignments
  WHERE UserID IN (3, 5, 8)
  GROUP BY FK_Master_JobID
  HAVING COUNT(DISTINCT *) = 3
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources