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