Subquery issue. - 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

General SQL Server Forums

New to SQL Server Programming

Subquery issue.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-05-19 : 17:46:34

I'm working with a table called Tasks. My select statement looks like this:

SELECT tasks.TYPE,
       tasks.request,
       tasks.reqdate,
       tasks.priority,
       tasks.wo_num,
       tasks.wotype2,
       tasks.wotype3,
       tasks.task,
       tasks.completed,
       tasks.respons,
       tasks.hours,
       tasks.parentwoid
FROM   tasks

I want to return a row if:
tasks.parentwoid <> tasks.wo_num OR
If the count of tasks.parentwoid is 1. Only one record in the table.

How do I do this?

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-19 : 18:25:49

David Chel,

This is my first time replying to any post like yours & Im still learning sql. Anyways try this & this should work ...crossing my fingers..

regards,

Anil

(select JOB.First as First, count(JOB.FIRST) as firstlineactivecnt
from
(select t.type as BEE, t.wo_num as First
from tasks t
inner join tasks p on t.type = p.type and t.wo_num = p.wo_num
where t.type is not null and t.parentwoid != q.wo_num)JOB
group by JOB.First
having count(job.first) = 1
)

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-19 : 19:30:41

Im sorry, I just realised that the previous one that I posted is more of an AND' operator than 'OR'. So try it on the following lines & you should be able to get it.
However if you want a cut & paste answer, please post only the KEY columns here & I should be able to do that.

select p1.type, p1.request,p1.* from tasks p1 inner join
(
select JOB.First as First, count(JOB.FIRST) as firstlineactivecnt
from
(select t.type as BEE, t.wo_num as First
from tasks t
)JOB
group by JOB.First having count(job.first) = 1
)ABC
on ABC.type = p1.type

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 23:39:11

[code]SELECT t.TYPE,
t.request,
t.reqdate,
t.priority,
t.wo_num,
t.wotype2,
t.wotype3,
t.task,
t.completed,
t.respons,
t.hours,
t.parentwoid
FROM tasks t
left join (select parentwoid
from tasks
group by parentwoid
having count(*) =1) s
on s.parentwoid =t.parentwoid
where s.parentwoid is not null
or t.parentwoid <> t.wo_num [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources