exclusive set of data query

hi all, given that i have the following tables and data:
mst_locs.locid ctl_loctypes.loctypeid [description]
1 1 [plant]
2 2 [hub]
3 3 [warehouse]
intersect_loc_loctype
locid loctypeid
1 1
1 2
1 3
2 1
2 2
3 3
i want to query out exclusive loctypes data for loctypeid = 3. using the sql
:
select * from intersect_loc_loctype where loctypeid = 3
will return:
locid loctypeid
1 3
3 3
i need a way to loc that are exclusively loctype = 3 [warehouse] such that
the returned data will be:
locid loctypeid
3 3
this is a bit long but i hope my problem was stated clearly.Here's one way:
SELECT *
FROM intersect_loc_loctype ill
WHERE loctypeid = 3
AND NOT EXISTS
(
SELECT *
FROM intresect_loc_loctype ill2
WHERE ill2.locid = ill.locid
AND ill2.loctype <> 3
)
To solve problems like this going forward, I recommend you read the
following article:
http://www.dbazine.com/ofinterest/oi-articles/celko1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Amil" <Amil@.discussions.microsoft.com> wrote in message
news:4E1F7C1E-8C48-4EEE-BD4B-1477A2F4ADC0@.microsoft.com...
> hi all, given that i have the following tables and data:
> mst_locs.locid ctl_loctypes.loctypeid [description]
> 1 1 [plant]
> 2 2 [hub]
> 3 3 [warehouse]
> intersect_loc_loctype
> locid loctypeid
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 3
> i want to query out exclusive loctypes data for loctypeid = 3. using the
sql:
> select * from intersect_loc_loctype where loctypeid = 3
> will return:
> locid loctypeid
> 1 3
> 3 3
> i need a way to loc that are exclusively loctype = 3 [warehouse] such that
> the returned data will be:
> locid loctypeid
> 3 3
> this is a bit long but i hope my problem was stated clearly.|||Adam,
Thank you for the solution, and thank you for providing me the link. You
helped me a great deal.
"Adam Machanic" wrote:

> Here's one way:
>
> SELECT *
> FROM intersect_loc_loctype ill
> WHERE loctypeid = 3
> AND NOT EXISTS
> (
> SELECT *
> FROM intresect_loc_loctype ill2
> WHERE ill2.locid = ill.locid
> AND ill2.loctype <> 3
> )
>
> To solve problems like this going forward, I recommend you read the
> following article:
> http://www.dbazine.com/ofinterest/oi-articles/celko1
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Amil" <Amil@.discussions.microsoft.com> wrote in message
> news:4E1F7C1E-8C48-4EEE-BD4B-1477A2F4ADC0@.microsoft.com...
> sql:
>
>sql