CODEWITHSUNDEEP
phpsqldatabaseselect
Optiq
Optiq

Reputation: 3264

SELECT statement not working right

I'm using this code to retrieve data from a table I created.

SELECT id,shirt_name,boys FROM shirts WHERE boys IS NOT NULL

Instead of just selecting the rows where the boys column has input, it selects all of them. here's the way I created the table

CREATE TABLE shirts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    shirt_name VARCHAR(20) NOT NULL,
    men VARCHAR(10) NULL,
    women VARCHAR(10) NULL,
    boys VARCHAR(10) NULL,
    girls VARCHAR(10) NULL,
    babies VARCHAR(10) NULL,
)ENGINE=INNODB;

INSERT INTO shirts(shirt_name,men,women,boys,girls,babies) VALUES
    ('Crewneck Tee','me_crn','wo_crn','bo_crn','gi_crn','ba_crn'),
    ('V-Neck Tee','me_vnc','wo_vnc','','',''),
    ('Scoop Neck Tee','','wo_sco','','',''),
    ('Raglan Tee','me_rag','wo_rag','bo_rag','gi_rag',''),
    ('Ringer Tee','me_rin','wo_rin','bo_rin','gi_rin',''),
    ('Cap Sleeve Tee','','wo_cap','','gi_cap',''),
    ('Tank Top','me_tan','wo_tan','bo_tan','gi_tan',''),
    ('Spaghetti Strap','','wo_spa','','',''),
    ('Hoodie','me_hod','wo_hod','bo_hod','gi_hod','ba_hod');

what did I do wrong?

Upvotes: 1

Views: 89

Answers (2)

craighandley
craighandley

Reputation: 156

SELECT id,shirt_name,boys FROM shirts WHERE boys != ''

Is what you need to use with this data

Upvotes: 1

Joey
Joey

Reputation: 354854

You did not insert any records with NULL in the boys column. Only records where it's an empty string. And an empty string ('') is different from NULL, so it matches IS NOT NULL.

Upvotes: 4

Related Questions