CODEWITHSUNDEEP
sql
Arnab
Arnab

Reputation: 2354

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'.

Thanks

Upvotes: 0

Views: 63

Answers (3)

O. Jones
O. Jones

Reputation: 108686

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 true. 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)

Upvotes: 3

JimNicholson
JimNicholson

Reputation: 351

I think you are asking for all orderid where the userid has a userage value of 18.

In which case the following should work:

SELECT orderid 
FROM TABLE 
WHERE userid IN (SELECT DISTINCT userid 
                 FROM TABLE 
                 WHERE userage = 18)

Upvotes: 4

Jyoti mishra
Jyoti mishra

Reputation: 597

Please try this:

Select orderid from table where orderid in (1,3,8)

Upvotes: -1

Related Questions