Sql statement with multi ANDs querying the same column

Question

I don't know if the title of the post is the appropriate. I have the following table

enter image description here

and an Array in php with some items, parsed_array. What I want to do is to find all the SupermarketIDs which have all the items of the parsed_array.

For example, if parsed_array contains [111,121,131] I want the result to be 21 which is the ID of the Supermarket that contains all these items.

I tried to do it like that:

$this->db->select('SupermarketID'); 
$this->db->from('productinsupermarket');
for ($i=0; $i<sizeof($parsed_array); $i++)
    {
      $this->db->where('ItemID', $parsed_array[$i]);
    }
$query = $this->db->get();
return $query->result_array();

If there is only one item in the parsed_array the result is correct because the above is equal to

SELECT SupermarketID 
FROM productinsupermarket
WHERE ItemID=parsed_array[0];

but if there are more than one items, lets say two, is equal to

SELECT SupermarketID 
FROM productinsupermarket
WHERE ItemID=parsed_array[0]
AND ItemID=parsed_array[1];

which of course return an empty table. Any idea how can this be solved?

Joachim Isaksson · Answer 1 · 2013-05-04T13:05:03.170

2

There are at least two ways of generating the result you want, either a self join (no fun to generate with a dynamic number of items) or using IN, GROUP BY and HAVING.

I can't really tell you how to generate it using CodeIgniter, I assume you're better at that than I am :)

SELECT SupermarketID 
FROM productinsupermarket
WHERE ItemID IN (111,121,131)       -- The 3 item id's you're looking for
GROUP BY SupermarketID
HAVING COUNT(ItemId) = 3;           -- All 3 must match

An SQLfiddle to test with.

EDIT: As @ypercube mentions below, if the ItemId can show up more than once for a SupermarketID, you'll want to use COUNT(DISTINCT ItemId) to count only unique rows instead of counting every occurrence.

edited May 04 '13 at 13:05

answered May 04 '13 at 12:44

Joachim Isaksson

176,943
25
281
294

1

Two is an under-estimate; Here's a similar question with more than 10 answers for the same problem (relational division): [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) – ypercubeᵀᴹ May 04 '13 at 12:50
@ypercube Interesting link, I've only encountered the two :) Thanks. – Joachim Isaksson May 04 '13 at 12:51
You have my +1. The `GROUP BY / HAVING COUNT =n` is the most popular and easy to write (and usually without need for dynamic SQL). I think you have to add that it works only if the `(ItemID,SupermarketID)` combination is unique. Otherwise, the condition has to be `HAVING COUNT(DISTINCT ItemId) = 3; ` – ypercubeᵀᴹ May 04 '13 at 12:56

score 1 · Accepted Answer · answered May 04 '13 at 13:09

1

You can use where_in in codeigniter as below,

if(count($parsed_array) > 0)
{
    $this->db->where_in('ItemID', $parsed_array);
}

Active record class in codeigniter

answered May 04 '13 at 13:09

Paulraj

3,373
3
36
40

1

If this actually solved the problem, I misunderstood the question :) – Joachim Isaksson May 04 '13 at 16:08

score 0 · Answer 3 · answered May 04 '13 at 12:44

0

Try an IN clause or multiple ORs:

SELECT SupermarketID 
FROM productinsupermarket
WHERE ItemID=parsed_array[0]
OR ItemID=parsed_array[1];

answered May 04 '13 at 12:44

duffymo

305,152
44
369
561

Sql statement with multi ANDs querying the same column

3 Answers3