MYSQL Query not showing data properly

Question

I am performing a filter by selected value this how my database looks like:

contactgroup  media  gender  age  isdelete  married  children driverslicens restatus retype
        -----------------------------------------------------------
        contactgroup1     SMS        Male     28     0   yes   yes     yes   owner    apart           
        contactgroup1     SMS        Female   26     0   no    null    no     rent  house
        contactgroup2     SMS        Male     32     0   null   no      null    owner  null
        contactgroup2     SMS        Male     38     0   yes    yes     no      null   null

This is my query:

SELECT * FROM contact
  where isdeleted = 0
    AND contactgroup in ('Contactgroup1', '')
    and media = 'sms'
    AND (`gender` = 'female' OR `gender` = 'male' OR `gender` is null)
    AND (`married` = 'yes' OR `married` = 'no' OR `married` is null)
    AND (`children` = 'yes' OR `children` = 'no' OR `children` is null)
    AND (`driverslicense` = 'yes' OR `driverslicense` = 'no' OR `driverslicense` is null)
    AND (`retype` = 'owner' OR `retype` = 'renting' OR `retype` is null)
    AND (`restatus` = 'apart' OR `restatus` = 'house' OR `restatus` is null)
    and age BETWEEN 18 AND 60

This query should show the data regarding the contactgroup1, but its showing all the four data can anyone tell me why it’s showing all the data where I went wrong?

M Khalid Junaid · Accepted Answer · 2014-06-01 06:40:37Z

3

You need to organize your query and group OR conditions

SELECT 
  * 
FROM
  contact 
WHERE isdeleted = 0 
  AND contactgroup IN ('Contactgroup1', '') 
  AND media = 'sms' 
  AND (
    `gender` = 'female' 
    OR `gender` = 'male' 
    OR `gender` = 'null'
  ) 
  AND age BETWEEN 18 
  AND 60

Demo

Edit as question is updated with new column

You need to identify null by using IS NULL

SELECT 
  * 
FROM
  contact 
WHERE isdelete = 0 
  AND contactgroup IN ('Contactgroup1', '') 
  AND media = 'sms' 
  AND (
    `gender` = 'female' 
    OR `gender` = 'male' 
    OR `gender` = 'null'
  ) 
  AND (
    `married` = 'yes' 
    OR `married` = 'no' 
    OR `married` IS NULL
  ) 
  AND age BETWEEN 18 
  AND 60

Demo 2

edited Jun 1, 2014 at 6:40

answered Jun 1, 2014 at 6:29

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

3 Comments

Xavi Over a year ago

thanks for reply it works,but i added new coloumn please check my edieted code,this time this query not showing any data

Xavi Over a year ago

hi @M M Khalid Junaid,there is another issue i found i added more coloumns this is my new query SELECT * FROM contact where isdeleted = 0 AND contactgroup in ('Contactgroup1', '') and media = 'sms' AND (gender = 'female' OR gender = 'male' OR gender is null) AND (married = 'yes' OR married = 'no' OR married is null) AND (children = 'yes' OR children = 'no' OR children is null) AND (driverslicense = 'yes' OR driverslicense = 'no' OR driverslicense is null) AND (restatus = 'apart' OR restatus = 'house' OR restatus is null) and age BETWEEN 18 AND 60

M Khalid Junaid Over a year ago

@Xavi see my comment in fiddle you have used wrong column name against values fiddle here columns name issue and also see correct fiddle here hope you got my point

Fluffeh · Accepted Answer · 2014-06-01 06:30:26Z

Conditions in queries need to be popped in backets where you want some more than rudimentary logic applied to the search:

SELECT 
    * 
FROM 
    contact 
where 
    isdeleted = 0 
    AND contactgroup in ('Contactgroup1', '') 
    and media = 'sms' 
    AND
    ( 
        `gender` = 'female' 
        OR `gender` = 'male' 
        OR `gender` = 'null' 
    )
    and age BETWEEN 18 AND 60

Any where clauses that introduce an OR statement will basically cause the database to ignore any of the other AND statements and return any matching data.

In your query, you had

OR `gender` = 'male' 
OR `gender` = 'null'

Which basically meant bring back any rows that matched this data. The change in the query now makes it that all the other AND statements have to be met - as long as at least ONE of the OR statements is met inside the brackets.

oezi · Accepted Answer · 2014-06-01 06:32:55Z

0

use brackets around your OR-conditions like this:

SELECT 
  *
FROM 
  contact 
WHERE isdeleted = 0
AND contactgroup in ('Contactgroup1', '')
AND media = 'sms' 
AND (gender = 'female' OR gender = 'male' OR gender = 'null')
AND age BETWEEN 18 AND 60

this is called Operator Precedence - AND is more important than OR, so if you don't use brackets, it's like you're doing this:

SELECT 
  *
FROM 
  contact
WHERE (isdeleted = 0 AND contactgroup in ('Contactgroup1', '') and media = 'sms' AND gender = 'female')
OR (gender = 'male')
OR (gender = 'null' AND age BETWEEN 18 AND 60)

answered Jun 1, 2014 at 6:32

oezi

51.9k10 gold badges102 silver badges119 bronze badges

Collectives™ on Stack Overflow

MYSQL Query not showing data properly

3 Answers 3

Demo

Demo 2

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related