Return a row dependant on the value of another column

[happs]

Hi People

 

I have a table (see attachment) that has an item ID and a valSelect ID.

I need to create a query that returns the item ID only if two of the valSelect columns match a passed value.

 

 

I have tried:

SELECT item FROM item2attr_adv WHERE valSelect = 31 OR valSelect = 44

But this returns the item even if just ONE valSelect matches.

 

See the two highlighted item rows in the attachment - they hav a valSelect of 31 and 44 so I want this item ID returning.

 

 

[attachment deleted by admin]

[Zeradin]

when you say it returns item when just one matches, doesn't that mean you want to make that OR an AND?

 

I'm confused about this question

[happs]

Hi.

 

Yes, it should really be an AND - but this returns nothing so I am obviously doing something wrong

 

SELECT item FROM item2attr_adv WHERE valSelect = 31 AND valSelect = 44

 

Returns 0 records.

 

[Zeradin]

i think what you need to do here is use a self join

 

because you have to select values that are either 31 or 44 that have the same item number

 

it'd be something like:

SELECT a.item, b.item 
FROM item2attr_adv a JOIN item2attr_adv b ON (a.item = b.item)
WHERE a.item=31 AND b.item=44

or

SELECT a.item, b.item 
FROM item2attr_adv a, item2attr_adv b
WHERE a.item=b.item 
AND a.item=31 AND b.item=44

 

I don't know if that's quite right, but you can get more information about how to do that sort of thing from sql zoo.

 

http://sqlzoo.net/6.htm?answer=1

 

that should help

[fenway]

That's one way... I prefer writing it as a cross join an moving the condition to the where clause, but that's just me.  You can use an IN() clause with HAVING & COUNT(), but depending on which version you use, it may not use indexes efficiently.