> Hi all, > > i want to create a SQL query above 2 tablespaces. In the query i want > to compare if the value i searched fore is in tsA or tsB. > > I tryed it with something like this: > SELECT * FROM tableA, tableB WHERE tableA.ts IN > (#preserveSingleQuotes(myDocumentsA)#) OR tableB.ts IN > (#preserveSingleQuotes(myDocumentsA)#) > > So, i want to check if the value is in A or in B. But the result is > all entrys from tableA. > > How must i change the SQL query? > > Thank you in advance & with best regards > > nic.
SELECT COALESCE(tableA.ts,tableB.ts) as Searched, CASE WHEN LEN(rtrim(ltrim(tableA.ts))) > 0 and LEN(rtrim(ltrim(tableB.ts))) > 0 THEN 'BOTH' WHEN LEN(rtrim(ltrim(tableA.ts))) > 0 THEN 'tableA' WHEN LEN(rtrim(ltrim(tableB.ts))) > 0 THEN 'tableB' ELSE 'NONE' end as whattable FROM tableA FULL OUTER JOIN tableB ON tableA.ts=tableB.ts where tableA.ts IN(LIST) or tableB.ts IN(LIST) --This will display what value was searched and what tables it resides in. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290798 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
