CODEWITHSUNDEEP
sqlsql-server
Joie Tamayo
Joie Tamayo

Reputation: 541

How to write a complex query

I need some help on SQL queries. The Type, ProcessID and ParentProcessID are columns of a table. What I need to do is search for Type "P". Then, get it's ProcessID. Once I have it's ProcessID, I need to get all the processes of Type "S" spawned by the process of Type "P". For example:

Type    ProcessID   ParentProcessID
------------------------------------
P   1001049    1000052
S   1001050    1001049
S   1001051    1001049
S   1001052    1001049
S   1001053    1001049

P   1002015    1000045
S   1002016    1002015
S   1002017    1002015
S   1002018    1002015
S   1002019    1002015

I can write a pseudo code but I don't have any idea with SQL.

select * from table where Type=P...

... ...

Any idea would be greatly appreciated.

Upvotes: 0

Views: 107

Answers (3)

Smutje
Smutje

Reputation: 18143

Naive solution:

select *
from table as Spawns
where Spawns.Type = 'S' and Spawns.ParentProcessID in (
    select Parents.ProcessID
    from table as Parents
    where Parents.Type = 'P'
)

Upvotes: 2

jabs
jabs

Reputation: 1848

In Sql Server, I like to use Common Table Expressions (CTE). It can help keep different elements of the query straight.

With GetProcess as (

select ProcessID
from tableA
where Type = 'P')

select S.ProcessID 
from tableA S
join GetProcess P on S.processId = P.ProcessID
where S.type = 'S'

So, the CTE gets your processIDs where the type = P. Then, join it to your table again and find the 'S' types.

Upvotes: 2

SoulTrain
SoulTrain

Reputation: 1904

You could use EXISTS to get your resultset

select *
from table as Spawns
where Spawns.Type = 'S' and EXISTS (
    select 1
    from table as Parents
    where Parents.Type = 'P'
    and Spawns.ParentProcessID =Parents.ProcessID   
)

Upvotes: 2

Related Questions