CODEWITHSUNDEEP
mysql
khalnas
khalnas

Reputation: 47

MySQL select where not()..and...and. (or...or)

I am trying to select data from one table.

This query work well:

SELECT * FROM game WHERE NOT (type = 'unity') 
AND (cat_id='3d' OR cat2_id='3d' OR cat3_id='3d') 
ORDER BY hits desc limit 120, 70

But I need run it with published=1

SELECT * FROM game WHERE NOT (type = 'unity') 
AND (cat_id='3d' OR cat2_id='3d' OR cat3_id='3d') 
AND published=1
ORDER BY hits desc limit 120, 70

At this point I see result 0 rows but it should be 50 rows.

Upvotes: 0

Views: 62

Answers (1)

Rahul
Rahul

Reputation: 77896

That means the condition is not matching with the extra condition AND published=1 and so no records returning. Again instead of saying NOT (type = 'unity') it's much redable if you say (type != 'unity').

You can try modifying your query a bit like below

SELECT * FROM game 
WHERE (type != 'unity') 
AND published=1 
AND '3d' in (cat_id, cat2_id, cat3_id) 
ORDER BY hits desc 
limit 120, 70;

If you really sure that with new condition published=1 it should return 50 rows then try adding that condition as a outer query condition with the SQL which works well like below and see what result it returns.

SELECT * FROM
(
SELECT * FROM game WHERE NOT (type = 'unity') 
AND (cat_id='3d' OR cat2_id='3d' OR cat3_id='3d') 
ORDER BY hits desc limit 120, 70
) xxx WHERE published=1;

Upvotes: 1

Related Questions