I am on MSSQL.
So: <cfelse>
<!---here....give me what specifically does not have a
record in the other table--->
FULL OUTER JOIN #request.hold_table# ht on ht.order_id =
co.corder_id AND IN (SELECT corder_id FROM co)
?
Thanks for helping me out.
-----Original Message-----
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 19, 2005 12:15 PM
To: CF-Talk
Subject: RE: Select by whats not there?
Eric,
Depending on what dbms you are using, you could just do a subselect.
Add 'AND IN (SELECT field FROM table)' to your join.
Some dbms do not support subselects in the join statement, in which case you
can likely work your query to function with the subselect in the where
statement.
--
Jillian
-----Original Message-----
From: Eric Hoffman [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 19, 2005 11:03 AM
To: CF-Talk
Subject: Select by whats not there?
I hae two tables, related to each other by order id.
I need to find out programatically what orders exist in the main table
without any corresponding lines in the other table based upon the
relationship of the order id.
How can I do a SQL query in which I ask during the join for what does not
exist specifically? Left join, for example, gives me everything;
record or
not. Right join would be only if something exists in that second table...
Must be easy, but I can't figure it out.
Thanks!
from #request.cust_table# cu
JOIN #request.orders_table# co on co.customer_id =
cu.cust_id
<cfif url.errorid NEQ 4>
RIGHT OUTER JOIN #request.hold_table# ht on ht.order_id =
co.corder_id
<cfelse>
<!---here....give me what specifically does not have a
record in the other table--->
LEFT OUTER JOIN #request.hold_table# ht on ht.order_id =
co.corder_id
Eric
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:221513
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
