I'd go with a UNION. The reason a union is useful is that you want B.content and C.content to appear as the same column in your record set, which is what a union does well. Note that with unions, the order you list the columns and the number of columns is critical. Also only add one ORDBER BY clause if you need it right at the end.
SELECT A.status, B.content FROM A INNER JOIN B ON A.status=B.status UNION SELECT A.status, C.content FROM A INNER JOIN C ON A.status=C.status -----Original Message----- From: Roberto Perez [mailto:[EMAIL PROTECTED] Sent: Wednesday, 20 July 2005 11:21 a.m. To: CF-Talk Subject: "or" operator in WHERE statement Hi all, Quick question: is "OR" a valid operator in a WHERE statement? I have a main table A with a 'status' field which value can be either 'open' or 'closed'. Then I have a table B where the 'status' field is 'open' by default (not editable), and a table C where the 'status' field is 'closed' by default (not editable). I thought that if my WHERE statement was: WHERE A.status = B.status OR A.status = C.status ......I would get data from either B or C, according to the value of 'status' in table A. In other words, I though if the first condition was not met, the second condition would be parsed and executed. But what happens is that the field that is defined first in the SELECT statement is the one that is always displayed in my output, regardles of whether the value of 'status' in table A is 'open' or 'closed'. If OR is not a possible operator, any idea how to achieve what I'm trying to do (i.e., for the query to bring data from one table or the other according to the value of a single shared field)? I tried joins with no good results either... Thanks in advance, Roberto Perez ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:212279 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
