0

i want to select records contain given ID and given Branch so i came up with this query and i want to know whether this is the correct way to write this

im using codeigniter

$orderids = array('2', '3');
$branch = array('branch1', 'branch2');

$this->db->where_in('order_id', $orderids);
$this->db->where_in('branch', $branch);
$query = $this->db->get('tbl_order_data');

this makes the query

SELECT *
FROM (`tbl_m_order_collection_data`)
WHERE `order_id` IN ('2', '3')
AND `branch` IN ('branch1', 'branch2')

my table looks like

+----+----------+-------------+-----------+
| ID | order_id | branch      | item_code |
+----+----------+-------------+-----------+
|  1 |        1 | branch1     | 4R1       |
|  2 |        1 | branch2     | 4R11      |
|  3 |        1 | branch2     | ACS20x20  |
|  4 |       *2 | branch1     | ACS20x27  |
|  5 |       *2 | branch1     | ACS20x20  |
|  6 |        1 | branch1     | ACS20x20  |
|  7 |        2 | branch2     | ACS20x27  |
|  8 |       *3 | branch2     | ACS20x20  |
+----+----------+-------------+-----------+

what im trying is to get star marked records from table. so the above query is valid for use?

Improve this question
6
  • Seems fine? Have you tried it? Commented Oct 5, 2011 at 18:15
  • yes seems to be working fine but i have no idea how where and IN with AND works. thats why i though of asking Commented Oct 5, 2011 at 18:18
  • 1
    Note that the query as written will also return the row with ID #7, because '2' is in (2,3) and 'branch2' is in (branch1,branch2). IN clauses are independent of one another. Commented Oct 5, 2011 at 18:19
  • 3
    The query you gave will select all of the rows you starred as well as row 7. Were you trying to get SELECT * FROM table WHERE (order_id = 2 AND branch = 'branch1') OR (order_id = 3 AND branch = 'branch2') (that would only give you the 3 rows you starred)? Commented Oct 5, 2011 at 18:20
  • thanks a lot, seems like thats the query i want it there Commented Oct 5, 2011 at 18:31

3 Answers 3

Reset to default
1

From the comments it seems you want to know how IN works.

The simple answer is that IN looks for the values stated before the word in the array after the word

Example one:

'banana' IN ('apple','orange','banana')

This yields true.

Example two:

1234 IN ('hello','world!')

This yields false.

You can read more about IN here

AND is a logical operator and only yields true if bothe the statements on either side is true. Like this:

true  AND true = true

false AND true = true AND false = false

If we combine Example one and two we get:

'banana' IN ('apple','orange','banana')
AND
1234 IN ('hello','world!')

As Example one was true, but exampe two was false, this will come back as false.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Your WHERE clause..

WHERE `order_id` IN ('2', '3') AND `branch` IN ('branch1', 'branch2')

..could be read, in English, as "The records whose order_id is '2' or '3' and whose branch is 'branch1' or 'branch2.'"

In other words, it's roughly equivalent to:

WHERE (`order_id` = '2' OR `order_id` = '3')
  AND (`branch` = 'branch1' OR `branch` = 'branch2')

The advantage of WHERE ... IN is that you could specify lots of different values, e.g. order_id IN ('2', '3', '4', '5'), and you can even put a SELECT (subquery) after IN to see if the value is "in" the set returned by the subquery.

Improve this answer

Comments

0

Yes, this is the correct way to do it. You can make this a lot more succinct by chaining the methods together though:

$query = $this->db
    ->where_in('order_id', $orderids)
    ->where_in('branch', $branch)
    ->get('tbl_order_data');
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.