CODEWITHSUNDEEP
sqlsqlite
tctc91
tctc91

Reputation: 1363

SQLite NOT NULL

How can I make this exclude the row if any of the (stage1_choice, stage2_choice or stage3_choice) values = null ?

enter image description here

I've tried:

SELECT  r.breakout_id ,
        r.case_id ,
        r.stage_id ,
        r.chart_id ,
        s.stage1_choice ,
        s.stage2_choice ,
        s.stage3_choice
FROM    Results AS r ,
        Submissions AS s
WHERE   r.breakout_id = '1'
        AND s.breakout_id = '1'
        AND r.case_id = s.case_id
        AND stage_id < 4
        AND s.stage1_choice NOT NULL
        AND s.stage2_choice NOT NULL
        AND s.stage3_choice NOT NULL

But it still returns rows that contain a null in one of the columns.

Upvotes: 1

Views: 517

Answers (2)

TuGordoBello
TuGordoBello

Reputation: 4509

try

SELECT r.breakout_id, r.case_id, r.stage_id, r.chart_id, s.stage1_choice, s.stage2_choice, s.stage3_choice
FROM Results as r, Submissions as s 
WHERE r.breakout_id = '1' AND s.breakout_id = '1' AND r.case_id = s.case_id AND stage_id < 4 AND s.stage1_choice NOT NULL AND s.stage2_choice IS NOT NULL AND s.stage3_choice IS NOT NULL

or

SELECT r.breakout_id, r.case_id, r.stage_id, r.chart_id, s.stage1_choice,   s.stage2_choice, s.stage3_choice
FROM Results as r, Submissions as s 
WHERE r.breakout_id = '1' AND s.breakout_id = '1' AND r.case_id = s.case_id AND stage_id < 4 AND s.stage1_choice != '' AND s.stage2_choice != '' AND s.stage3_choice != ''

Upvotes: 1

Fabio
Fabio

Reputation: 23490

The correct syntax for not null is different, and it's IS NOT NULL

AND s.stage1_choice IS NOT NULL 
AND s.stage2_choice IS NOT NULL 
AND s.stage3_choice IS NOT NULL

From documentation

  • To test for NULL, use the IS NULL and IS NOT NULL operators

Learn more here

Upvotes: 2

Related Questions