You can use a sub-query for this, so that you limit your records by a factor unrelated to the actual record you're looking at...
SELECT EventCode FROM job a INNER JOIN Event b ON b.job_id = a.job_id WHERE (b.EventCode = 100) AND (a.job_id = '1234567890') AND a.job_id NOT IN (select DISTINCT z.job_id FROM job z INNER JOIN event y ON z.job_id = y.job_id WHERE z.eventCode = 150 AND z.job_id = a.job_id) so you're saying "give me any job where eventCode is 100 and jobID is N, but where the same job doesn't have an eventCode of 150 somewhere else" is that what you're looking for? -Jeff cf coder wrote: >Hello everybody, I have a question with regards to a sql select statement and >was hoping someone could help. I'm trying to query a table >and trying to return row where a condition is true. > >To give you a better idea; picture a event table. A job can have muliple >events ex, an event to indicate the job is logged, >an event to indicate the job is closed, an event to indicate the job is open >etc etc. > >Each job has a unique job id that is stored in the job table and the job_id >column is also present in the event table (foreign key I suppose) > >I'm trying to run a query that returns a recordset if it has an event present >in the event table but also if another event is missing from that table for a >particular job > >This will give you a better idea > >SELECT EventCode >FROM job >INNER JOIN Event ON Event.job_id = job.job_id >WHERE (EventCode = 100) AND EventCode <> 150 AND (job_id = '1234567890') > >The above select statement returns a recordset if the event code 150 is >present in the event table. But I want it to >only return a recordset if the table contains the eventcode 100 and does not >contain the eventcode 150. > >Can somebody show me how to do this please > >Best regards >coder > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195246 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
