Jochem, When i updated my query to your second version below i get:
Syntax error (missing operator) in query expression 'CASE WHEN bugs.bug_type_id = 0 THEN CAST (0 AS VARCHAR) ELSE bug_type.bug_type'. Can you explain "CAST (0 AS VARCHAR)". Btw, at this stage of development i am using Access2000 but will be changing to SQL2000 soon, so the bug_type field is simply "text". Thanks again. -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: 07 January 2003 13:07 To: CF-Talk Subject: Re: newbie: help with sql query Azeem Huda wrote: > > I have the following query and was hoping that someone could help me out. > I am trying to retrieve a field named bug_type in another table (bug_type) > IF the bugs.bug_type_id != 0 (since i know that bug_type.bug_type_id does > not exist). Conversely if bugs.bug_type_id = 0 then simply add that > statement to the query. if .. then .. else .. endif in SQL is: CASE WHEN .. THEN .. [WHEN .. THEN ..] [ELSE ..] END > <cfquery datasource="#variables.DSName#" name="bug_details"> > > SELECT bugs.bug_id, bugs.bug_type_id, bugs.bug_site_id, > bugs.bug_section_id, > etc etc > FROM bugs, bug_site, etc etc > WHERE bug_site.bug_site_id = bugs.bug_site_id > AND bug_status.bug_status_id = bugs.status_id > AND priority.priority_id = bugs.priority_id > AND (IF bug_type_id !=0 > bug_type IN > (SELECT bug_type, bug_type_id > FROM bug_type > WHERE bug_type.bug_type_id = bugs.bug_type_id) > ELSE bug_type_id =0) > > </cfquery> If I understand your intentions correctly it should be (presuming that bug_type is a varchar field): SELECT bugs.bug_id, bugs.bug_site_id, bugs.bug_section_id, etc etc, CASE WHEN bugs.bug_type_id = 0 THEN CAST(0 AS VARCHAR) ELSE bug_type.bug_type END FROM bug_site, etc etc, bugs NATURAL JOIN bug_type WHERE bug_site.bug_site_id = bugs.bug_site_id AND bug_status.bug_status_id = bugs.status_id AND priority.priority_id = bugs.priority_id If there is no entry in the bug_type table for bug_type_id = 0 this becomes: SELECT bugs.bug_id, bugs.bug_site_id, bugs.bug_section_id, etc etc, CASE WHEN bugs.bug_type_id = 0 THEN CAST(0 AS VARCHAR) ELSE bug_type.bug_type END FROM bug_site, etc etc, bugs LEFT JOIN bug_type ON bug_type.bug_type_id = bugs.bug_type_id WHERE bug_site.bug_site_id = bugs.bug_site_id AND bug_status.bug_status_id = bugs.status_id AND priority.priority_id = bugs.priority_id Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
