Query question

[Finedean]

I a table like this:

ProvID Type Office
1 Rad-H Office1
1 Rad-A Office2
2 Rad-H Office1
2 Active Office2
3 Active Office1
3 Rad-H Office2
4 Active Office1
4 Active Office2

I would like a query to pull only provid that type does not contain the word active

The query result will be:

ProvID Type Office
1 Rad-H Office1
1 Rad-A Office2


Thanks in advance

Dean

 

[mp9]

SELECT * FROM YourTableNameHere
WHERE [Type] NOT LIKE '*Active*';

http://www22.brinkster.com/accessory/

 

[PHV]

One way:
SELECT A.ProvID, A.Type, A.Office
FROM yourTable As A
WHERE NOT EXISTS (SELECT * FROM yourTable WHERE ProvId=A.ProvID AND Type='Active')

Another way:
SELECT ProvID, Type, Office
FROM yourTable
WHERE ProvID NOT IN (SELECT ProvID FROM yourTable WHERE Type='Active')

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

 

[mp9]

Ignore my post, I misinterpreted the question.

 

[Finedean]

Thank you. You guys are the best.
Dean