SQl Query - SQL Server Forums

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)

SQl Query

Author

Topic

dbaz
Starting Member

5 Posts

Posted - 2005-10-13 : 00:44:29

Dear All.

I have below tables:

[Docs]
DocID DocName
1 Doc1
2 Doc2
3 Doc3
....

[DocTasks]
DocID TaskID
1 1
1 2
2 1
2 3
2 2
3 2
3 1
4 5
4 2
4 1
4 3
5 3
5 2
....

[Tasks]
TaskID TaskName
1 Task1
2 Task2
3 Task3
4 Task4
...

Now I want to get TaskID-s from DocTasks table, where each that TaskID belongs to all DocID-s in DocTasks table. For example:
TaskID 1 belongs to DocIDs(1, 2, 3, 4) -> This TaskID is not needed
TaskID 2 belongs to ALL DocIDs(1, 2, 3, 4, 5) -> This TaskID is needed
TaskID 3 belongs to DocIDs(2, 4, 5) -> This TaskID is not needed. So on..
So, Query must return only TaskID=2, because only this TaskID belongs to All DocIDs.

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-13 : 01:30:53

Try this

Select T2.TaskID from DocTasks T1 inner join Tasks T2 on T1.TaskID=T2.TaskID
where T1.DocID in (1,2,3,4,5)--you need to include other DocIds if any
group by T2.DocID having count(*)=5 --Change this if you have many

Madhivanan

Failing to plan is Planning to fail

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-10-13 : 01:41:52

Not sure if reading your question right but how about
select TaskId from DocTasks
group by TaskId
having count(distinct DocId)=5

Hippi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-13 : 02:16:52

quote:
Originally posted by Hippi

Not sure if reading your question right but how about
select TaskId from DocTasks
group by TaskId
having count(distinct DocId)=5

Hippi

Seems to be Good Idea

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-13 : 02:30:13

little modification to the hippi's code.. :-))..

select TaskId From DocTasks
group by TaskId
having count(distinct DocId)>=(Select Count(1) From Docs)

hope this makes any sense. :-)

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-13 : 04:47:26

What is the need for >=(Select Count(1) From Docs)?

Madhivanan

Failing to plan is Planning to fail

dbaz
Starting Member

5 Posts

Posted - 2005-10-13 : 05:34:22

Hey GUYS!!!
What are you doing?
Lets forget about Docs and Task table.
I habe table as below:
[DocTasks]
DocID TaskID
1 1
1 2
2 1
2 3
2 2
3 2
3 1
4 5
4 2
4 1
4 3
5 3
5 2
6 1
6 4
6 2
7 1
7 2
7 3
8 5
8 2
9 4
9 5
9 2

Ok.
What are the dictinct DocIDs in this table:
1, 2, 3, 4, 5, 6, 7, 8, 9
What are the dictinct TaskIDs in this table:
1, 2, 3, 4, 5.

Now I want the TaskIDs, which it has been assigned to all the above distinct DocIDs.
For example: TaskID = 2. Because there are rows as below
[DocTasks]
DocID TaskID
1 2
2 2
3 2
4 2
5 2
6 2
7 2
8 2
9 2
So, All DocIDs found.
But TaskID = 3. This is not what we want, Because there are rows as below
[DocTasks]
DocID TaskID
2 3
4 3
5 3
7 3
So, there is not DocIDs(1, 3, 6, 8, 9).

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-13 : 05:45:36

Did you try the queries suggested?

Madhivanan

Failing to plan is Planning to fail

dbaz
Starting Member

5 Posts

Posted - 2005-10-13 : 06:11:24

Yes.
Select T2.TaskID from DocTasks T1 inner join Tasks T2 on T1.TaskID=T2.TaskID
where T1.DocID in (1,2,3,4,5)--you need to include other DocIds if any
group by T2.DocID having count(*)=5 --Change this if you have many

Result:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DocID'

T2 is alias to Tasks, but it has no DocID Field

Any idea?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-13 : 06:12:51

Use T1.DocID and try it

Madhivanan

Failing to plan is Planning to fail

dbaz
Starting Member

5 Posts

Posted - 2005-10-13 : 07:23:54

Server: Msg 8120, Level 16, State 1, Line 1
Column 'T2.TaskID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-13 : 07:34:28

Use Hippi's query

select TaskId from DocTasks
group by TaskId
having count(distinct DocId)=9

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-13 : 08:03:05

[quote]Originally posted by madhivanan

What is the need for >=(Select Count(1) From Docs)?

I have given that clause, according to what i thought the logic goes like this

Get the task id from the doctask table and group by it and then in the having clause check with the total number of doc id present .. so instead of hardcodding the number of docid in the docs table i gave (count(1) query.. that what i thought

but i guess it should be somthing like this..

select TaskId From DocTasks
group by TaskId
having count(distinct DocId)>=(Select Count(Distinct DocID) From Docs)

Hope its work out what he wants.

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources