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!