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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-14 : 23:41:52
|
Mike writes "I have 3 tables:
room
room_property <-- links objects to props
room_property_lookup <-- stores all possible props
now, with following data:
room:
roomID NAME
1 Room 100
2 Room 200
3 Room 300
room_property_lookup
propertyID Name
11 Chair
12 Table
13 Projector
we can have the foolowing in room_property:
roomID propertyID
1 11
2 11
2 12
3 12
3 13
here comes the question: How can I get the room which has both chair(11) and table(12)?
with the assumption that I am building sql statement in ASP and I am getting the IDs of the properties, I came up with 2 solutions:
SELECT * FROM room WHERE
roomID IN (SELECT roomID FROM room_property WHERE propertyID = 11)
AND roomID IN (SELECT roomID FROM room_property WHERE propertyID = 12)
, but with the number of properties being searched growing, this causes too much stress on DB.
The other solution uses count:
SELECT roomID FROM room WHERE propertyID IN (11, 12)
GROUP BY roomID
HAVING COUNT(roomID) = 2
Any other suggestions?
thanks, Mike
" |
|
|
|
|
|
SELECT value with unique property