>>If you are on access, the outer join won't work and you have to do a union:
Sorry...that's not true. Access supports outer joins, but the syntax is either RIGHT JOIN or LEFT JOIN rather than OUTER JOIN. >>Conversely if bugs.bug_type_id = 0 then simply add that statement to the query. . Azeem, if you're still listening, the above comment and the code at the end of your original post to output the data indicates that you are retrieving *all* bug_type_ids, whether they are 0 or not. If that is true, you don't need the last six lines of your SQL statement since you can control your output based on a CFIF condition, which you've done. If you *do* want to filter out all bug_type_ids that are 0, simply add "AND bugs.bug_type_id <> '0'" to your subquery (but don't forget to correct the join syntax in the subquery). Since you appear to be joining multiple tables: bugs, bug_type, bug_status, and priority using an inner join, please be aware that Access does not support the "WHERE PK=FK" inner join syntax (that's fine in SQL Server though). Instead you will need to join the tables in the FROM clause. The easiest way to build an Access query is to use the Access Query Designer, switching to SQL view to run it and see the syntax. When you get the results you expect, just copy/paste into CF. *** Regarding your question about why you cannot output the bug_type, it may be that CF is seeing the datatype as numeric. Therefore, try eliminating the quotes around 0. As an aside, it is unnecessary to wrap your variables with pound signs in the CFIF tag. And, logically, you should change <cfelseif #bug_type_id# eq "0"> to <CFELSE>. ~Dina ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
