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 |
|
Ferox
Starting Member
18 Posts |
 Posted - 2005-06-21 : 04:47:26
|
| Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused. I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item. A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:Select * FROM ItemsLEFT OUTER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID IN (10, 13, 17)But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull out ONLY items that have a AttributeID of 10 AND 13 AND 17.Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :S |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-06-21 : 07:42:28
|
| First do not use * list all fields.You are using an outer join this will return all from the items tableyou need an inner join to limit to your where.JimUsers <> Logic |
 |
|
|
Ferox
Starting Member
18 Posts |
Posted - 2005-06-22 : 06:18:57
|
| Thanks for the reply Jim. I know not to use '*' I put it here to save me listing out my columns, and since they arn't really necessary to view to solve the problem I used '*'. I was using a LEFT OUTER in that first query because some items may not have any attributes at all.I got the query working...but it's a little kludgy:Select SOME_COLUMN_HERE From ITEMSWHERE Item_ID IN( SELECT Link_ItemID FROM linking WHERE Link_AttributeID IN (10))AND Item_ID IN( SELECT Link_ItemID FROM linking WHERE Link_AttributeID IN (13))AND Item_ID IN( SELECT Link_ItemID FROM linking WHERE Link_AttributeID IN (17)) |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-06-22 : 08:08:01
|
| In your example would you want to return only those items with all specified attributes and no others? I.e. if an item had attributes 10, 13, 17 and 21, would it be excluded?Mark |
|
|
|
Ferox
Starting Member
18 Posts |
Posted - 2005-06-22 : 09:03:30
|
| Hi Mark. I get items returned that definitely have the three specified attributes, but they can have other attributes that are currently not being searched on. So if a user specifies another attribute for the criteria, then it's drills down into the results further excluding items without the new selected attribute. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-06-22 : 09:34:20
|
| Okay - so say the user selected no attributes, would this go the other way? I.e. I assume you would only want to return those items with no attributes.Mark |
|
|
|
Ferox
Starting Member
18 Posts |
Posted - 2005-06-23 : 04:07:02
|
| I'll give you a senario:A user is searching for cars. The first search they search for 'all cars', but they get too many results. So they narrow it by selecting an attribute (but checking a tickbox): 'All cars with electric windows (att_id: 10)'. Still to many results, so they add another attribute: 'all cars with electric windows (att_id: 10) and powered steering (att_id: 13)' - getting close, but this user also wants an airbag, so their final searh is ''all cars with electric windows(att_id: 10), powered steering(att_id: 13) and an airbag (att_id: 17)' The query I'm using now works sufficiantly, I'm gonna run some tests on how it handles heavy loads/ 1000's records etc. |
|
|
|
|
|
|
|
|
Help with relational query