Duplicating And's - 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

General SQL Server Forums

New to SQL Server Programming

Duplicating And's

Author

Topic

Slither
Starting Member

3 Posts

Posted - 2009-08-23 : 09:22:15

I have the following code so far,

SELECT item.name
FROM items, item_variables
WHERE items.variableRef = item_variables.itemVariableRef
AND item_variables.variableId = 1 AND item_variables.variableId = 3;

I only want to bring back the name of the item that has both the variable Id 1 and 3. How can I do this within one query. Currently instead of bring back one item that has 3 and 1 it brings back NO results presumably because it thinks I mean the same column.

Thanks

singularity
Posting Yak Master

153 Posts

Posted - 2009-08-23 : 12:18:08

SELECT item.name
FROM items
JOIN item_variables ON items.variableRef = item_variables.itemVariableRef
WHERE item_variables.variableId IN (1,3)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-23 : 12:41:41

variableId cannot be 1 and also 3.
You can use IN(1,3) like shown by singularity or

WHERE items.variableRef = item_variables.itemVariableRef
AND (item_variables.variableId = 1 OR item_variables.variableId = 3)

No, you're never too old to Yak'n'Roll if you're too young to die.

Slither
Starting Member

3 Posts

Posted - 2009-08-23 : 15:01:35

Ok thats not exactly what I wanted let me explain my table structure,

Items table has a variable ref which links it to multiple categories.

Item 1, itemVariables Ref (for instance number 1)

Item_variables has data like.

ItemVariable ref and a variableId so an Item has multiple categories

1, 1,
1, 3

I want to bring back the items that have certain categories 1 and 3 from same table. That is why I am using an AND. I want to get items that have 1 and 3 as the category and not any others. Is there a way this can be done?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-23 : 16:18:52

[code]
--Making tables and data for test
declare @items table ([name] varchar(255), variableRef int)
declare @items_variables table (itemVariableRef int, variableId int)

insert @items
select 'name_1', 1 union all
select 'name_2', 2 union all
select 'name_3', 3

insert @items_variables
select 1,1 union all
select 1,3 union all
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 3,1 union all
select 3,3

-- select (DISTINCT) only names where categories 1,3 and not any others
select DISTINCT [name] from @items i
join @items_variables iv
on i.variableRef = iv.itemVariableRef
where not exists (select * from @items_variables where itemVariableRef = i.variableRef and (variableId <> 1 and variableId <> 3))
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-23 : 16:20:03

btw. giving tables, sample data and wanted output was your job