• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

Advice on SQL Query


I still trying to get some more complex SQL queries written - I can manage simpler stuff but I would like to ensure that I am writing reasonable efficient queries also.

In my database I have a table ijob - this is linked to from ijobelement which holds the elements of a job and which is itself linked to from item which defines the type of item in each element.  

I want to return a list of all jobs which do not have any job elements of type 2 and as such have produced the following query

select * from ijob where ijob_id NOT IN
(select ijob_id from ijobelement where iitem_id IN (SELECT iitem_id from iitem where iitem_type=2))

Works but I think that there must be a better way of doing this as I'm not that keen on the use of 'IN' as I suspect it may be quite slow.

I also need to extend this query so that I can check for jobs which have no job elements of type 2 but have 1 or more job elements of type 2.  How could I do that other than just adding another IN clause to the query.

Any suggestions welcome on how to improve and extend this query would be welcome!
3 Solutions
Ephraim WangoyaCommented:
select * 
from ijob 
inner join ijobelement on ijob.ijob_id = ijobelement.ijob_id 
inner join iitem on iitem.iitem_id  = ijobelement.iitem_id 
where iitem_type<>2

Open in new window

NOT EXISTS is an alternaitive to NOT IN

Select * from ijob j
( Select 1 from ijobelelment e
  where e.ijob_id = j.ijob_id
  and iitem_id  in (SELECT iitem_id from iitem where iitem_type=2)

Everyone always advises against NOT IN because of the posiblity of full table scans.   However,  I don't always find it is the slowest method.    I think optimisers have a big part to play in this.   Try alternative syntaxes and see which work for you.

SharathData EngineerCommented:
Check this.
SELECT t1.* 
  FROM ijob t1 
       LEFT JOIN (SELECT t2.ijob_id 
                    FROM ijobelement t2 
                         JOIN iitem t3 
                           ON t2.iitem_id = t3.iitem_id 
                   WHERE t3.iitem_type = 2) t4 
         ON t1.ijob_id = t4.ijob_id 
 WHERE t4.ijob_id IS NULL

Open in new window

ChrisMDrewAuthor Commented:
Thanks for the help guys - all of these suggestions gave me some useful ideas and have helped me to get a query which works quickly!

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now