>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
