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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:221509 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
