IF QUERY

[netrusher]

I have a table with the following fields:

OMS_PAGE
BUILD_ID
PROCESS
OMS_REFERE

A OMS_PAGE can have multiple BUILD_ID'S assigned to it. If that is the case the OMS_PAGE will be listed as many times as there are Build ID's (max of 4). What I am trying to find out is how can I get data from the table that will show me only the OMS_PAGE that have all four BUILD_ID'S assigned to a page?

Thanks for your help!

 

[Golom]

Select   OMS_PAGE

From     myTable

Group By OMS_PAGE

Having   Count(*) = 4

This assumes that the values for BUILD_ID are unique for a given value of OMS_PAGE (i.e. the same BUILD_ID value cannot occur more than once.)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[netrusher]

Thanks,

I should have added that I would like to see each OMS_NAME that has the four Build_ID's, and see the BUILD_ID's too.

 

[PHV]

SELECT A.OMS_PAGE, A.BUILD_ID, A.PROCESS, A.OMS_REFERE
FROM yourTable AS A INNER JOIN (
SELECT OMS_PAGE FROM yourTable GROUP BY OMS_PAGE HAVING Count(*)=4
) AS C ON A.OMS_PAGE = C.OMS_PAGE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886