That worked. You rock...now I've learned a new trick as well! Thanks!
Regards, Eric Hoffman Datastream Connexion -----Original Message----- From: DeShazo, Jonathan P. (Keane) [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 10:29 AM To: CF-Talk OK, yeah. You are right. When you add discriminators on the customer_levels table like 'AND customer_levels.customer_id = 20735' it messes up the outer join. 'inventory.client_id = 1' won't hurt but I don't know what table the active column is on. What I would do is narrow down the data in the customer_levels table to the right client_id BEFORE I outer join it to inventory. Try using an inline view like this: Select inventory.service_id, inventory.service_name, inventory.service_rate, inventory.client_id, inventory.category_id, client.shortname AS Client_Name, inventory_category.category_name, customer_levels.id, customer_levels.price, customer_levels.inv_id from inventory JOIN inventory_category on inventory_category.id = inventory.category_id JOIN client on client.id = inventory.client_id LEFT OUTER JOIN ( SELECT * FROM customer_levels WHERE customer_id = 20735 ) AS customer_levels on customer_levels.inv_id = inventory.service_id where active = 1 AND inventory.client_id = 1 order by category_id ASC Does this work for you? I like this problem because I am not used to the ANSI notation for joins and wouldn't think to use them unless I had to. Thanks for the practice:) -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 10:46 AM To: CF-Talk Subject: RE: SQL Join Problem (Easy ?) Let me see if we are seeing eye to eye...I want to return all Inventory records...and any customer_levels (custom prices can be none, one, or more levels per item) if they exist and only for the one customer we are looking up... Here's my results right now: http://www.heinzconnect.com/hzc.cfm Only getting records with a matching customer_levels record, no records that have no customer_levels match. Is there something else monkeying this up (I switched to left join) I believe my AND clause with the customer id is funking it up maybe? How else to filter it then? Maybe it is my joins.. :-) Thanks so much!! (the last piece of the proverbial puzzle on this) Select inventory.service_id, inventory.service_name, inventory.service_rate, inventory.client_id, inventory.category_id, client.shortname AS Client_Name, inventory_category.category_name, customer_levels.id, customer_levels.price, customer_levels.inv_id from inventory JOIN inventory_category on inventory_category.id = inventory.category_id JOIN client on client.id = inventory.client_id LEFT OUTER JOIN customer_levels on customer_levels.inv_id = inventory.service_id where active = 1 AND inventory.client_id = 1 AND customer_levels.customer_id = 20735 order by category_id ASC Regards, Eric -----Original Message----- From: DeShazo, Jonathan P. (Keane) [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 9:29 AM To: CF-Talk >From what you have got there, you should be returning all records from the customer_levels table, along with the records in inventory that match. It sounds like you want all records in the inventory table with cusomter_level data if it exists. You were almost there with the query you have but you put the lock on the wrong side of the door. Change your RIGHT OUTER JOIN to a LEFT OUTER JOIN and you should be all set. ps. In case I read this wrong: you can return all inventory records AND all customer_level data whether they match or not (like a union) by using a full outer join. Have a good Monday... -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, August 03, 2003 11:50 PM To: CF-Talk Subject: SQL Join Problem (Easy ?) This I bet is easy, but I am not seeing it... Here is the query: <cfquery name="inv_list_1" datasource="#heinz.dsn#"> Select inventory.service_id, inventory.service_name, inventory.service_rate, inventory.client_id, inventory.category_id, client.shortname AS Client_Name, inventory_category.category_name, customer_levels.inv_id, customer_levels.price, customer_levels.id from inventory JOIN inventory_category on inventory_category.id = inventory.category_id JOIN client on client.id = inventory.client_id RIGHT OUTER JOIN customer_levels on inventory.service_id = customer_levels.inv_id where active = 1 AND inventory.client_id = 1 AND customer_levels.customer_id = 20735 order by category_id ASC </cfquery> All we need to do is return all the rows right now. This ends as a four related subselect...the end result isn't important, simply need the query returning every option, thus the join attempts. In review, how do we join that customer_level table on there so it does't exclude records when there are no related records in customer_levels, but add multiple rows when there are. Example output: Dispensing Jug Pumps Vol-Pak Valve Only 2.0000 Dispensing Jug Pumps Vol-Pak Rack and Valve set 2.0000 Dispensing Jug Pumps Vol-Pak Rack and Valve set 3.0000 Dispensing Jug Pumps Vol-Pak Rack and Valve set 4.0000 Dispensing Jug Pumps Jug pump - Pour & Store #10 2.5000 Dispensing Jug Pumps Jug pump - Pour & Store #11 Dispensing Jug Pumps Jug pump - Pour & Store #12 THANKS!! Regards, Eric J. Hoffman DataStream Connexion www.datastreamconnexion.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
