I have 2 tables that contain Schedule information. The first table
contains Work Order's and the second table contains Task information.

A given Work Order is composed of any combination of Tasks. Example:

Work Order = Car
Task 1 = Wheel Install
Task 2 = Wheel Install
Task 3 = Wheel Install
Task 4 = Wheel Install

What I'm looking for is a way to find all Work Orders that contain only
4 tasks. There's no feild in the tables that specify how many Tasks are
part of a Work Order.

I have the feeling in my gut that I need to do this query using a
subquery. My only problem is that I don't know how to filter out Work
Orders with less than 4 Tasks as well as those with more than 4 Tasks.

Work order = A Car=B
select * from A,B
where /*use correct join*/
rownnum <= 4
order by Task1


"Paul Izzo" <paul.izzo (AT) mosca-ag (DOT) com> wrote

Hi,

the query below should solve ur problem assuming that work & task table
had a relation based on order_id

for count of less than 4

select work_order.order_id,
sum(task.order_id)
from work_order, task
where work_oder.order_id=task.order_id
group by work_order
having count(*) < 4

& for count of greater than 4

select work_order.order_id,
sum(task.order_id)
from work_order, task
where work_oder.order_id=task.order_id
group by work_order
having count(*) >4

On 18 Feb 2005 01:13:45 -0800, "Paul Izzo" <paul.izzo (AT) mosca-ag (DOT) com>
wrote:

I suggest you look at using the exists command combining it with
count.

Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com

On 18 Feb 2005 01:13:45 -0800, "Paul Izzo" <paul.izzo (AT) mosca-ag (DOT) com>
wrote:

SELECT t.*
,w.*
FROM workorders w
,tasks t
WHERE w.order_id = t.order_id
AND 4 = (SELECT COUNT (t2.task_id)
FROM fundgroups t2
WHERE t2.order_id = w.order_id)

Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com

These are all great suggestions guys and I feel that I'm getting
closer to solving my problem. I took your suggestions Vikas and made
them into 1 select statement that looks like this:

select papp.WORKPLAN_NR,sum(papp.UNIT_NR)
from w100.papp,w100.papk
where papp.WORKPLAN_NR = papk.WORKPLAN_NR
group by papp.WORKPLAN_NR
having sum(decode(UNIT_NR,3400,1,3500,1,3430,1)) != 3

With this select statement I get all the records I don't want (this
is good) but I get the problem when I try to combine the select
statement with another. I'd like to do something like this:

select papp.WORKPLAN_NR,sum(papp.UNIT_NR)
from w100.papp,w100.papk
where papp.WORKPLAN_NR = papk.WORKPLAN_NR
group by papp.WORKPLAN_NR
having sum(decode(UNIT_NR,3400,1,3500,1,3430,1)) = 3
not in
(select papp.WORKPLAN_NR,sum(papp.UNIT_NR)
from w100.papp,w100.papk
where papp.WORKPLAN_NR = papk.WORKPLAN_NR
group by papp.WORKPLAN_NR
having sum(decode(UNIT_NR,3400,1,3500,1,3430,1)) != 3 )

But this doesn't work. Does anyone know how I can join the 2 select
statements?

These are all great suggestions guys and I feel that I'm getting
closer to solving my problem. I took your suggestions Vikas and made
them into 1 select statement that looks like this:

select papp.WORKPLAN_NR,sum(papp.UNIT_NR)
from w100.papp,w100.papk
where papp.WORKPLAN_NR = papk.WORKPLAN_NR
group by papp.WORKPLAN_NR
having sum(decode(UNIT_NR,3400,1,3500,1,3430,1)) != 3

With this select statement I get all the records I don't want (this
is good) but I get the problem when I try to combine the select
statement with another. I'd like to do something like this:

select papp.WORKPLAN_NR,sum(papp.UNIT_NR)
from w100.papp,w100.papk
where papp.WORKPLAN_NR = papk.WORKPLAN_NR
group by papp.WORKPLAN_NR
having sum(decode(UNIT_NR,3400,1,3500,1,3430,1)) = 3
not in
(select papp.WORKPLAN_NR,sum(papp.UNIT_NR)
from w100.papp,w100.papk
where papp.WORKPLAN_NR = papk.WORKPLAN_NR
group by papp.WORKPLAN_NR
having sum(decode(UNIT_NR,3400,1,3500,1,3430,1)) != 3 )

But this doesn't work. Does anyone know how I can join the 2 select
statements?

On 21 Feb 2005 05:04:09 -0800, "Paul Izzo" <paul.izzo (AT) mosca-ag (DOT) com>
wrote:

Please stop wasting bandwith, and forcing people to read irrelevant
replies, use the test groups instead


--
Sybrand Bakker, Senior Oracle DBA

Thanks Sybrand for your concerns I've since removed all "test"
replies. This query that we're attempting is futile because my boss
changed his mind.

I'd like to thank everyone for their help on the subject.

The new query is a bit more strange but I'll open up a seperate
thread on this one.