Home » SQL & PL/SQL » SQL & PL/SQL » How to throw out a "set' of data? (Oracle 10G, version 2, UNIX)
How to throw out a "set' of data? [message #341050] Fri, 15 August 2008 18:09 Go to next message
stevekerver
Messages: 19
Registered: January 2008
Junior Member
Hi there- I'm looking to answer the following question:

Suppose I have a table with the two columns USER_ID and EVENT_ID.

What I'm looking to do is to formulate a query to find those distinct USER_ID's which ONLY have EVENT_ID's 13 and/or 14.
...If they have any other events, I want to throw them out.

So- for example:

USER_ID   EVENT_ID
---------- ----------
         2          1
         2         14
         3         13
         3         14
         4         13
         5          1
         5          2
         5          7
         6         14
         7          8
         7          6
         7         14
         7         13
         8          8
         8         13


We would throw out user 2, because they have other events in addition to event 14.
We would keep user 3, because they have both events 13 and 14 only.
We would keep user 4, because they have event 13 only.
We would throw out user 5, because they didn't have events 13 or 14.
We would keep user 6, because they have event 14 only.
We would throw out user 7, because they have other events in addition to 13 and 14.
We would throw out user 8, because they have other events in addition to 13.

...Hope that makes sense- any help would be greatly appreciated.


Re: How to throw out a "set' of data? [message #341051 is a reply to message #341050] Fri, 15 August 2008 18:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Consider using MINUS
Re: How to throw out a "set' of data? [message #341075 is a reply to message #341050] Sat, 16 August 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use COUNT to count the events that are not in 13,14 and only keep those with count=0.

Regards
Michel
Re: How to throw out a "set' of data? [message #341288 is a reply to message #341075] Mon, 18 August 2008 03:58 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
@Michel - the events You keep with count=0 could be not in [13,14] so the MINUS operator will help:
select user_id from <table>
where event_id  in (13,14)
MINUS
select user_id from <table>
where event_id not in (13,14)

Re: How to throw out a "set' of data? [message #341291 is a reply to message #341288] Mon, 18 August 2008 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the events You keep with count=0 could be not in [13,14]

Then filter them.
I confirm you can do it with COUNT.

Regards
Michel
Re: How to throw out a "set' of data? [message #341389 is a reply to message #341050] Mon, 18 August 2008 12:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Lots of other variations too. I like the WITH clause. All depends upon what you want to do with your code.

Good luck, Kevin

with
--
-- find unique users from source table
--
      user_list as (
                     select distinct user_id
                     from <sometable>
                   )
--
-- limit users to those without undesireable events
--
    , filtered_user_list as (
                              select *
                              from user_list b
                              where not exists
                                 (
                                   select null
                                   from <sometable> c
                                   where c.user_id = b.user_id
                                   and c.event_id in (13,14)
                                 )
                            )
--
-- get all rows for the filtered users
--
   , result as (
                 select *
                 from <sometable>
                 where user_id in (
                                    select user_id
                                    from filtered_user_list
                                  )
               )
--
-- show it
--
select *
from result
/
Re: How to throw out a "set' of data? [message #341582 is a reply to message #341050] Tue, 19 August 2008 07:24 Go to previous messageGo to next message
rajeshwaranbtech
Messages: 4
Registered: August 2008
Location: Chennai
Junior Member
Here the solution for your question.

SELECT USER_ID,
COUNT( CASE WHEN event_id NOT IN (14,13) THEN 1 ELSE NULL END)
FROM t
GROUP BY user_id
HAVING COUNT( CASE WHEN event_id NOT IN (14,13) THEN 1 ELSE NULL END) = 0
Re: How to throw out a "set' of data? [message #341591 is a reply to message #341582] Tue, 19 August 2008 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rajeshwaranbtech

Read and follow site rules.
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: How to throw out a "set' of data? [message #341807 is a reply to message #341050] Wed, 20 August 2008 06:48 Go to previous messageGo to next message
uguddu
Messages: 5
Registered: August 2008
Junior Member
Hi,

Try query below...


SELECT t1.user_id,t1.event_id
FROM test t1
WHERE event_id IN (13,14)
AND user_id NOT IN (SELECT t2.user_id
FROM test t2
WHERE t2.event_id NOT IN (13,14))



Thanks & Regards,

Avadhesh

Re: How to throw out a "set' of data? [message #341823 is a reply to message #341807] Wed, 20 August 2008 07:56 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@uguddu

Read my previous post.
In addition, this is (far) not a better way that the previous ones.

Regards
Michel
Previous Topic: How to generate the spool (.LST) file using PL/SQL?
Next Topic: AVERAGE
Goto Forum:
  


Current Time: Sun Dec 11 08:15:20 CST 2016

Total time taken to generate the page: 0.20999 seconds