CODEWITHSUNDEEP
mysqlsql
Nizam Ali
Nizam Ali

Reputation: 241

Not able to get the result of two queries into one from mysql

I'm having two queries. First will return be 10 rows and second query will return me 2 rows. But, when I combine the queries into one, it still returns me only 10 rows.

Here are my queries.

SELECT * 
FROM   contentmsg 
WHERE  apmc_id = '5284a327e4b0bc1eaf3d118e' 
       AND commodity_id IS NULL 
       AND lang_id IS NULL 
       AND date < '2014-07-05 09:25' 
       AND date >= '2014-06-13 14:17:22' 
ORDER  BY -date 
LIMIT  0, 10

output two records

SELECT * 
FROM   contentmsg 
WHERE  apmc_id = '5284a327e4b0bc1eaf3d118e' 
       AND commodity_id IN ( '5226d086e4b05917e5ca33bc', '5226d086e4b05917e5ca33b6', '5226d086e4b05917e5ca33d9', '5226d086e4b05917e5ca3408',
                             '5226d086e4b05917e5ca33e1', '5226d086e4b05917e5ca33ec', '5226d086e4b05917e5ca33b4' )
       AND lang_id = '51fb5a2ee4b0e18f7f89ecc8' 
       AND date < '2014-07-05 09:25' 
       AND date >= '2014-06-13 14:17:22' 
ORDER  BY date 
LIMIT  0, 10

output 10 records..

The only difference between those queries is commodity_id is set to null and lang_id is set to null in the first query..

I've tried with this query where I've added "OR"

SELECT * 
FROM   contentmsg 
WHERE  apmc_id = '5284a327e4b0bc1eaf3d118e' 
       AND commodity_id IS NULL 
        OR commodity_id IN ( '5226d086e4b05917e5ca33bc', '5226d086e4b05917e5ca33b6', '5226d086e4b05917e5ca33d9', '5226d086e4b05917e5ca3408',
                             '5226d086e4b05917e5ca33e1', '5226d086e4b05917e5ca33ec', '5226d086e4b05917e5ca33b4' )
           AND lang_id IS NULL 
        OR lang_id = '51fb5a2ee4b0e18f7f89ecc8' 
           AND date < '2014-07-05 09:25' 
           AND date >= '2014-06-13 14:17:22' 
ORDER  BY -date 
LIMIT  0, 10

Is there any mistake in the query?

Upvotes: 2

Views: 28

Answers (1)

Kermit
Kermit

Reputation: 34063

You need to take care with the order of precedence with AND & OR operators (AND is evaluated before OR). To fix this, you enclose your operations in parentheses:

SELECT * 
FROM   contentmsg 
WHERE  apmc_id = '5284a327e4b0bc1eaf3d118e' 
       AND ( commodity_id IN ( '5226d086e4b05917e5ca33bc', '5226d086e4b05917e5ca33b6', '5226d086e4b05917e5ca33d9', '5226d086e4b05917e5ca3408',
                               '5226d086e4b05917e5ca33e1', '5226d086e4b05917e5ca33ec', '5226d086e4b05917e5ca33b4' )
             AND lang_id = '51fb5a2ee4b0e18f7f89ecc8' ) 
        OR ( commodity_id IS NULL 
             AND lang_id IS NULL ) 
           AND date < '2014-07-05 09:25' 
           AND date >= '2014-06-13 14:17:22' 
ORDER  BY -date

Upvotes: 2

Related Questions