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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
