Re: SQL Select statment query - kindly help
>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" That was exactly what I was trying to acheive. You sql worked straight away. Thank you again for your reply. 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:195352 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Select statment query - kindly help
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
SQL Select statment query - kindly help
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 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195206 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
