CODEWITHSUNDEEP
sqlsql-serversql-server-2008
saadamiens
saadamiens

Reputation: 47

sql query that select data that have only one value

I am trying with this query to get only the rtp.releaseid that have a unique territory=200

select rtp.ReleaseId, rtp.TerritoryId  from ReleaseTerritoryPrice rtp where 
rtp.TerritoryId=200

but I guess something is missing , can you please help. Thanks.

Upvotes: 0

Views: 154

Answers (4)

Gabe
Gabe

Reputation: 462

Maybe you mean this 

SELECT MAIN.* FROM ReleaseTerritoryPrice MAIN
WHERE MAIN.ReleaseId in (
    SELECT rtp.ReleaseId
    FROM   ReleaseTerritoryPrice rtp
    WHERE rtp.TerritoryId =200
    GROUP BY rtp.ReleaseId
    HAVING COUNT(rtp.TerritoryId) =1 )

Upvotes: 0

Taryn
Taryn

Reputation: 247860

You can use the following with NOT EXISTS in a WHERE clause:

select rtp1.releaseId, rtp1.territoryId
from ReleaseTerritoryPrice rtp1
where rtp1.territoryId = 200
  and not exists (select releaseId
                  from ReleaseTerritoryPrice t2
                  where t2.territoryId <> 200
                    and rtp1.releaseId = t2.releaseId);

See SQL Fiddle with Demo

Or you can use NOT IN in a WHERE clause:

select rtp1.releaseId, rtp1.territoryId
from ReleaseTerritoryPrice rtp1
where rtp1.territoryId = 200
  and rtp1.releaseId not in (select releaseId
                              from ReleaseTerritoryPrice t2
                              where t2.territoryId <> 200);

See SQL Fiddle with Demo

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453898

A couple of ways

SELECT ReleaseId
FROM   ReleaseTerritoryPrice
WHERE  TerritoryId = 200 
EXCEPT
SELECT ReleaseId
FROM   ReleaseTerritoryPrice
WHERE  TerritoryId IS NULL OR TerritoryId <> 200

Or

SELECT ReleaseId
FROM   ReleaseTerritoryPrice
GROUP BY ReleaseId
HAVING COUNT(DISTINCT TerritoryId) =1 AND MAX(TerritoryId )=200

Upvotes: 0

acermate433s
acermate433s

Reputation: 2554

Use the DISTINCT keyword.

select DISTINCT rtp.ReleaseId, 
     rtp.TerritoryId  
from ReleaseTerritoryPrice rtp 
where rtp.TerritoryId=200

Upvotes: -1

Related Questions