Arnab Arnab - 8 months ago 36
SQL Question

get data for users with properties not present in all rows -sql

My data has orderid, userid and userage along with a lot other data.
Now age is something that the user can provide if and whenever he/she wishes.

Sample data

orderid userid userage
1 1
2 2
3 1
4 1 18
5 3 25

Now, if I wish to find all orderids for userage=18, I can not do something as
Select orderid from table where userage=18
as it will give me only orderid '4'.
I want a single query which should preferably work on any db (as though I'm using sql server, it might change any time) which will give me result which has orderid '1', '3' and '4'.


Answer Source

You haven't disclosed how you represent missing data. Many DBMS designs use NULL values for that. If that's your situation

SELECT orderid FROM table WHERE (userage = 18 OR userage IS NULL)

will work.

If you use -1 for missing data (you probably don't, but I'm trying to make a point here)

SELECT orderid FROM table WHERE (userage = 18 OR userage  = -1)

will work.

Notice that when a column value is NULL, no test for equality or inequality ever comes back try. You must is IS NULL or IS NOT NULL to test NULLs.

In Oracle, zero length text strings are NULL. That's not so in other DBMSs.

If you're looking for all orders from a user who has ever given an age of 18, this will do the trick

 SELECT orderid 
   FROM table
   WHERE userid IN (SELECT userid FROM table where userage = 18)