CODEWITHSUNDEEP
mysqlsql
hahaha
hahaha

Reputation: 1037

SQL query not working as expected (from a beginners point of view)

I am trying to get all the rows with the specific session_id and also that do not have '3223' for the group_with column. So I have this SQL statement :

SELECT * FROM pr_cart WHERE session_id=203130570714 AND group_with != 3223

Which to me it looks fine, but for some reason it does not return anything.

In my table I have entries that should not meet the criteria. i.e.

session_id=203130570714 | group_with=3225 which should be returned. session_id=203130570714 | group_with=NULL which should be returned. session_id=203130570714 | group_with=3223 which should not be returned.

I tried:

  1. <> instead of !=
  2. NOT (group_with=3223)
  3. group_with NOT LIKE '3223'

How can I get all the rows with the specified session_id and that also have a different number than 3223 for the group_with

edit session_id is varchar and group_with is int

Upvotes: 9

Views: 217

Answers (2)

RickyRam
RickyRam

Reputation: 181

If SQL Server than try the below:-

Let try the below script if the datatype of the session_id and group_with is int means,

SELECT * FROM pr_cart WHERE session_id in(203130570714) AND group_with not in(3223)

If datatype id varchar try the below script,

SELECT * FROM pr_cart WHERE session_id in('203130570714') AND group_with not in('3223')

you will get your output.

Upvotes: -1

Ulrich Thomas Gabor
Ulrich Thomas Gabor

Reputation: 6654

NULL is a special value which you must take special care of:

SELECT * FROM pr_cart WHERE session_id="203130570714" AND
  (group_with != 3223 OR group_with IS NULL)

General recommendation

Basically all operations, which involve NULL as one operand, yield NULL as result.

SELECT NULL + 5; # NULL
SELECT NULL * 5; # NULL
SELECT NULL NOT IN (234); # NULL
SELECT 5 IN (NULL); # NULL

NULL will never be interpreted neither as true nor as false and is therefore literally useless for deciding if a row should be contained in the result set or not.

Whenever one has a column definition, which allows NULL values, one must take special care of these values.

Upvotes: 5

Related Questions