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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
