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
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
