problem with select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem with select statement

Author

Topic

kaisdd
Starting Member

17 Posts

Posted - 2006-09-15 : 17:44:09

hello there, i´m stuck with a query and ask for some help.

i´ve got a table structure like the following

PhotoID = TagID
300 = 1
301 = 1
302 = 1
303 = 1
303 = 2

my question is, how can i select only PhotoID 303 in a single query? task is, to select it because it has TagID 1 AND 2. another task is, to select all photos which have TagID 1 but not TagID 2 (all except 303).

it sound completely simple, but i dont get the trick.
maybe anyone could help me out?
thank you very much!

Luis Martin
Yak Posting Veteran

54 Posts

Posted - 2006-09-15 : 18:42:58

Check for 2nd question:
SELECT PhotoId, TagId, COUNT(*)
FROM Table
GROUP BY PhotoId, TagId
HAVING COUNT(*) = 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-15 : 19:17:39

"how can i select only PhotoID 303 in a single query? task is, to select it because it has TagID 1 AND 2"


select PhotoID
from   Table
where  TagID in (1, 2)
group by PhotoID
having count(*) = 2

kaisdd
Starting Member

17 Posts

Posted - 2006-09-18 : 11:19:17

thank you very much for your replys. i will try.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 11:27:26

Are there more variatons for TagID than 1 and 2?
Could TagID have another value than 1 or 2?

Peter Larsson
Helsingborg, Sweden

kaisdd
Starting Member

17 Posts

Posted - 2006-09-21 : 12:45:45

jep. it could. tagid is related to entrys of a keywordcatalog.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 16:15:28

[code]declare @test table (PhotoID INT, TagID INT)

insert @test
select 300, 1 union all
select 301, 1 union all
select 302, 1 union all
select 303, 1 union all
select 303, 2

-- Select all PhotoID with both TagID 1 and TagID 2
SELECT a.PhotoID
FROM (
SELECT PhotoID
FROM @Test
WHERE TagID = 1
) a
INNER JOIN (
SELECT PhotoID
FROM @Test
WHERE TagID = 2
) b ON b.PhotoID = a.PhotoID

-- Select all PhotoID with only TagID 1 and not TagID 2
SELECT a.PhotoID
FROM (
SELECT PhotoID
FROM @Test
WHERE TagID = 1
) a
LEFT JOIN (
SELECT PhotoID
FROM @Test
WHERE TagID = 2
) b ON b.PhotoID = a.PhotoID
WHERE b.PhotoID IS NULL[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources