If you are on access, the outer join won't work and you have to do a
union:
SELECT bugs.bug_id, bugs.bug_site_id,
bugs.bug_section_id, bug_type.bug_type,
etc etc
FROM bugs, bug_site, bug_type, 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 bug_type.bug_type_id = bugs.bug_type_id
AND bugs.bug_type_id <> 0
UNION
SELECT bugs.bug_id, bugs.bug_site_id,
bugs.bug_section_id, '' AS bug_type,
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 bugs.bug_type_id = 0
If ther is no bug_type.bug_type_id equal to 0, you can leave out the
line:
AND bugs.bug_type_id <> 0
-----Original Message-----
From: Azeem Huda [mailto:[EMAIL PROTECTED]]
Sent: dinsdag 7 januari 2003 14:35
To: CF-Talk
Subject: RE: newbie: help with sql query
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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
