By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Need help with a query - may be easy for you but not me

P: 5
I have a table with three fields. I need to mine out some simple info, but I can't for the life of me figure out the logic of how to do it.

Here is the table:

job_ref job_type object_ref
100110 RETRIEVE 200111
100113 ROUTE 200333
100116 RETRIEVE 200333
100119 RETRIEVE 200333
100121 RETRIEVE 200333
100124 RETRIEVE 200144
100129 RETRIEVE 200144
100133 RETRIEVE 200333


These are retrieve and route jobs for objects.

There will only be one route job per object, but there may be multiple retrieve jobs for that same object.

What I need to get (select) is the job ref of any route job where the object
of that job has more than 3 retrieve jobs.

Can anyone help me with that? I'd be mighty beholdin' to you.
Mar 24 '09 #1
Share this Question
Share on Google+
3 Replies


P: 5
It may be easier to forget the Route job and think of it this way.

What is the distinct object_ref of any RETREIVE jobs where there are more than three RETRIEVE jobs for that object_ref?

I'm thinking it may need some kind of "HAVING" statement, but I just can't seem to get it.
Mar 24 '09 #2

ck9663
Expert 2.5K+
P: 2,878
try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. select distinct object_ref from yourtable where job_type = 'RETRIEVE' group by object_ref having count(*) > 3
  3.  
  4.  
or
Expand|Select|Wrap|Line Numbers
  1.  
  2. select object_ref from yourtable a 
  3. inner join 
  4. (select distinct object_ref from yourtable where job_type = 'RETRIEVE' group by object_ref having count(*) > 3) b on a.object_ref = b.object_ref
  5. where a.job_type = 'ROUTE'
  6.  
  7.  

-- CK
Mar 24 '09 #3

P: 5
Those are exactly what I was looking for. With this, I am going to be able to make a script that will save hours and hours of having to manually delete those jobs. Thank you so much.
Mar 25 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.