0

I have table where item_id, property_id and value_id(value of property, concerning item) stored. It looks like this:

item_id   property_id   value_id
--------------------------------
001       p_001         aaa      
001       p_002         bbb      
001       p_003         ccc      
002       p_001         zzz      
002       p_002         bbb      
002       p_003         ddd      
003       p_001         yyy      
003       p_002         bbb      
003       p_003         ddd

So I want get items, that have values val = [bbb], for example, so it will be item_001, item_002 and item_003.
But now I want items, that have values val = [bbb, ccc], it will be item_001 only.
And now I want to get items, that have values val = [bbb, ddd], it will be obviously item_002 and item_003.
When val = [aaa,zzz] it should output item_001 as well as item_002.

How can I achieve that?
I thought that there is simple SQL query, but I can't construct it properly.

Artyom Tsoy
  • 2,238
  • 1
  • 13
  • 18

2 Answers2

0

While I don't have time to mockup the entire scenario to test, I think the following should work:

SELECT DISTINCT A.item_id 
FROM   table A 
       INNER JOIN table B 
               ON A.item_id = B.item_id 
       INNER JOIN table C 
               ON A.item_id = C.item_id 
WHERE  A.value_id = 'bbb' 
       AND B.value_id = 'ccc'

In addition, the following link should prove helpful:
SQL: how to select a single id ("row") that meets multiple criteria from a single column

Good luck,

Community
  • 1
  • 1
Seymour
  • 7,043
  • 12
  • 44
  • 51
  • Is it possible to join table to itself? Or should I create temporary table? – Artyom Tsoy Dec 10 '13 at 14:40
  • Yes, you can join the table to itself. (so you would not need a temp table) The following link provides some additional info: http://stackoverflow.com/questions/9824948/how-to-left-join-or-inner-join-a-table-itself – Seymour Dec 10 '13 at 14:59
  • I need to `INNER JOIN` as many tables as different properties I have? – Artyom Tsoy Dec 11 '13 at 06:56
0

In rails mysql you can do this:

Item.find(:all, :select => :item_id,:group => 'item_id HAVING COUNT(id) >= 2', :conditions => ['value_id IN (?)', ['bbb','ccc']])

You will see the SQL query:

Item Load (0.2ms)  SELECT item_id FROM `items` WHERE (value_id IN ('bbb','ccc')) GROUP BY item_id HAVING COUNT(id) >= 2
user2503775
  • 4,267
  • 1
  • 23
  • 41
  • I thought of something like that, but what if I will input `val = [aaa, zzz]`? I will get nothing at all, because item won't have 2 values of the same property. – Artyom Tsoy Dec 10 '13 at 14:13
  • It wasn't clear from your answer before update. When you say: `val = [bbb, ccc], it will be item_001 only.` it looks like `val = [aaa, zzz]` should be nothing at all. So what is the logic here? don't you meen to get only item_id that has all the value_id from a list? – user2503775 Dec 11 '13 at 06:45
  • Well, actually I want to make content filtering like in [Amazon](http://www.amazon.com/Televisions-Video/b/ref=sa_menu_tv?ie=UTF8&node=1266092011), but I have table scheme like this. Is it even possible? – Artyom Tsoy Dec 11 '13 at 07:01
  • Not enough clear why `val = [aaa,zzz]` should output `item_001` and `item_002`, while `val = [bbb, ccc]` gives `item_001` only, and not also `002` and `003`. it seems that you want : `1.` if item_id has all value_id from a list, then return only it, and not item_id that has only one value_id. `2.` if there is no such item, then return also item_ids that have only one value. Let me know if I'm wrong. – user2503775 Dec 11 '13 at 10:20
  • It's hard to explain so I will show simple example: let's say I want to buy medicine - I wand liquid medicine in bottle of 50ml. So liquid or tabs will be p_001 and 50 or 100 will be p_002. So if I check 'liquid' and '50ml' I want only liquids in bottles of 50ml, but what if I want 50 or 100? So here should pop up bottles, that are 100ml, so just like that. Sorry, if my English is too bad. – Artyom Tsoy Dec 13 '13 at 11:40