Another Newb SQL Question

me again. been staring at this and can't see what's wrong.
SELECT tblCustomers.companyName, tblOrders.orderID,
tblOrders.freightCharge, tblOrderDetails.unitPriceOnOrderDate
FROM tblCustomers
JOIN tblOrders
ON tblCustomers.customerID = tblOrders.orderID
JOIN tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
WHERE EXISTS
(
SELECT tblOrders.orderID
FROM tblOrders
JOIN tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
GROUP BY tblOrders.orderID, tblOrderDetails.orderID,
tblOrders.freightCharge
HAVING tblOrders.orderID = tblOrderDetails.orderID and
tblOrders.freightCharge > MIN(tblOrderDetails.unitPriceOnOrderDate)
)
idea is to show all orders whose freight charge (contained in
tblOrders) exceeds the unit price (contained in tblOrderDetails) of any
product in that order.
unfortunately results from my query contain many orders where the
freight charge is nowhere near as high as the prices of the products.
thxPlease post DDL and sample data.
ML|||<A_StClaire_@.hotmail.com> wrote in message
news:1130611630.835990.38500@.g49g2000cwa.googlegroups.com...
> me again. been staring at this and can't see what's wrong.
>
<snip>

> idea is to show all orders whose freight charge (contained in
> tblOrders) exceeds the unit price (contained in tblOrderDetails) of
any
> product in that order.
> unfortunately results from my query contain many orders where the
> freight charge is nowhere near as high as the prices of the
products.
> thx
>
A_StClaire_@.hotmail.com,
Original Query:
SELECT tblCustomers.companyName
,tblOrders.orderID
,tblOrders.freightCharge
,tblOrderDetails.unitPriceOnOrderDate
FROM tblCustomers
JOIN
tblOrders
ON tblCustomers.customerID = tblOrders.orderID
JOIN
tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
WHERE EXISTS
(SELECT tblOrders.orderID
FROM tblOrders
JOIN
tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
GROUP BY tblOrders.orderID
,tblOrderDetails.orderID
,tblOrders.freightCharge
HAVING tblOrders.orderID = tblOrderDetails.orderID
and tblOrders.freightCharge >
MIN(tblOrderDetails.unitPriceOnOrderDate)
)
The outer query has:
tblOrders
tblCustomers
tblOrderDetails
The subquery has:
tblOrders
tblOrderDetails
Note: In the outer query, tblOrder and tblCustomers are JOINed on
customerID = orderID. Is this correct (I can't imagine it would be)?
I changed it in example below in the hopes I was interpreting things
correctly.
--
In order for an EXISTS predicate to function properly (AFAIK, anyway),
the subquery must be correlated with the outer query.
I cannot spot the correlation in the above SQL. Without Table
Aliases, the query optimizer has no way of linking the subquery to the
outer query.
The following is the original Query now reformatted with Table Aliases
and a WHERE clause correlating the subquery and the outer query.
SELECT C1.companyName
,O1.orderID
,O1.freightCharge
,OD1.unitPriceOnOrderDate
FROM tblCustomers AS C1
INNER JOIN
tblOrders AS O1
ON C1.customerID = O1.customerID
INNER JOIN
tblOrderDetails AS OD1
ON O1.orderID = OD1.orderID
WHERE EXISTS
(SELECT O2.orderID
FROM tblOrders AS O2
INNER JOIN
tblOrderDetails AS OD2
ON O2.orderID = OD2.orderID
WHERE O1.orderID = O2.orderID
GROUP BY O2.orderID
,OD2.orderID
,O2.freightCharge
HAVING O2.orderID = OD2.orderID
and O2.freightCharge >
MIN(OD2.unitPriceOnOrderDate)
)
Note: Notice how the WHERE clause refers to a table in the outer query
by using the alias of the table in the outer query.
Note: I am making a fairly huge guess on the proper correlation in the
WHERE clause of the subquery. Pleaes take it for what it is meant to
be, a hint directing you toward a solution. IOW, this is definitely
not tested.
Sincerely,
Chris O.
PS Although meant for microsoft.pulic.sqlserver.programming, the
following link is still applicable for microsoft.pulic.access.queries:
http://www.aspfaq.com/etiquette.asp?id=5006, when it comes to
providing the information that will best enable others to answer your
question.|||<A_StClaire_@.hotmail.com> wrote in message
news:1130611630.835990.38500@.g49g2000cwa.googlegroups.com...
> me again. been staring at this and can't see what's wrong.
>
<snip>

> idea is to show all orders whose freight charge (contained in
> tblOrders) exceeds the unit price (contained in tblOrderDetails) of
any
> product in that order.
> unfortunately results from my query contain many orders where the
> freight charge is nowhere near as high as the prices of the
products.
> thx
>
A_StClaire_@.hotmail.com,
Original Query:
SELECT tblCustomers.companyName
,tblOrders.orderID
,tblOrders.freightCharge
,tblOrderDetails.unitPriceOnOrderDate
FROM tblCustomers
JOIN
tblOrders
ON tblCustomers.customerID = tblOrders.orderID
JOIN
tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
WHERE EXISTS
(SELECT tblOrders.orderID
FROM tblOrders
JOIN
tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
GROUP BY tblOrders.orderID
,tblOrderDetails.orderID
,tblOrders.freightCharge
HAVING tblOrders.orderID = tblOrderDetails.orderID
and tblOrders.freightCharge >
MIN(tblOrderDetails.unitPriceOnOrderDate)
)
The outer query has:
tblOrders
tblCustomers
tblOrderDetails
The subquery has:
tblOrders
tblOrderDetails
Note: In the outer query, tblOrder and tblCustomers are JOINed on
customerID = orderID. Is this correct (I can't imagine it would be)?
I changed it in example below in the hopes I was interpreting things
correctly.
--
In order for an EXISTS predicate to function properly (AFAIK, anyway),
the subquery must be correlated with the outer query.
I cannot spot the correlation in the above SQL. Without Table
Aliases, the query optimizer has no way of linking the subquery to the
outer query.
The following is the original Query now reformatted with Table Aliases
and a WHERE clause correlating the subquery and the outer query.
SELECT C1.companyName
,O1.orderID
,O1.freightCharge
,OD1.unitPriceOnOrderDate
FROM tblCustomers AS C1
INNER JOIN
tblOrders AS O1
ON C1.customerID = O1.customerID
INNER JOIN
tblOrderDetails AS OD1
ON O1.orderID = OD1.orderID
WHERE EXISTS
(SELECT O2.orderID
FROM tblOrders AS O2
INNER JOIN
tblOrderDetails AS OD2
ON O2.orderID = OD2.orderID
WHERE O1.orderID = O2.orderID
GROUP BY O2.orderID
,OD2.orderID
,O2.freightCharge
HAVING O2.orderID = OD2.orderID
and O2.freightCharge >
MIN(OD2.unitPriceOnOrderDate)
)
Note: Notice how the WHERE clause refers to a table in the outer query
by using the alias of the table in the outer query.
Note: I am making a fairly huge guess on the proper correlation in the
WHERE clause of the subquery. Pleaes take it for what it is meant to
be, a hint directing you toward a solution. IOW, this is definitely
not tested.
Sincerely,
Chris O.
PS The link http://www.aspfaq.com/etiquette.asp?id=5006, is excellent
when it comes to detailing how to provide the information that will
best enable others to answer your questions.|||Did it occur to you that "unit_price_on_order_date" is a query and
not a data element? WHERE IS THE IMPLIED HISTORY PRICE TABLE?
Did it occur to you that a customer_id should NEVER be equal to an
order_id?
What you posted is a screwed up mess.
Then you to spit on the people that ate helping you for free, you never
posted DDL.
Would you like to try again? With DDL? With specs that can be
programmed from?|||On 29 Oct 2005 11:47:10 -0700, A_StClaire_@.hotmail.com wrote:
(snip)
>idea is to show all orders whose freight charge (contained in
>tblOrders) exceeds the unit price (contained in tblOrderDetails) of any
>product in that order.
Hi A_StClaire_,
Try if the query below works:
SELECT tblCustomers.companyName, tblOrders.orderID,
tblOrders.freightCharge, tblOrderDetails.unitPriceOnOrderDate
FROM tblCustomers
JOIN tblOrders
ON tblCustomers.customerID = tblOrders.orderID
JOIN tblOrderDetails
ON tblOrders.orderID = tblOrderDetails.orderID
WHERE tblOrders.freightCharge > unitPriceOnOrderDate
(Untested - see www.aspfaq.com/5006 if you prefer a tested solution, or
if the query above doesn't meet your expectations)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Did it occur to you that "unit_price_on_order_date" is a query and
> not a data element? WHERE IS THE IMPLIED HISTORY PRICE TABLE?
> Did it occur to you that a customer_id should NEVER be equal to an
> order_id?
> What you posted is a screwed up mess.
No, what you posted is a screwed up mess. For crying out load, the
guys says that he is a begeinner. Beginners are permitted to make mistakes.
And they should be permitted to make mistakes, without having to be
insulted by morons like you.

> Then you to spit on the people that ate helping you for free, you never
> posted DDL.
>
How can he post DDL when you never explain what it is? And how the hell
can you accuse someone for spitting when he is asking politely?

> Would you like to try again?
The risk is that he will never try again, because he was turned off by
your reply. Is that what you want?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:f1t7m192si2fd8h6ftv78lvotge2mg4h2i@.
4ax.com...
> On 29 Oct 2005 11:47:10 -0700, A_StClaire_@.hotmail.com wrote:
> (snip)
any
> Hi A_StClaire_,
> Try if the query below works:
> SELECT tblCustomers.companyName, tblOrders.orderID,
> tblOrders.freightCharge, tblOrderDetails.unitPriceOnOrderDate
> FROM tblCustomers
> JOIN tblOrders
> ON tblCustomers.customerID = tblOrders.orderID
> JOIN tblOrderDetails
> ON tblOrders.orderID = tblOrderDetails.orderID
> WHERE tblOrders.freightCharge > unitPriceOnOrderDate
> (Untested - see www.aspfaq.com/5006 if you prefer a tested solution,
or
> if the query above doesn't meet your expectations)
> Best, Hugo
> --
>
Hugo,
The above has:

> ON tblCustomers.customerID = tblOrders.orderID
I am still thinking that customerID cannot equal orderID.
Sincerely,
Chris O.|||
> The outer query has:
> tblOrders
> tblCustomers
> tblOrderDetails
> The subquery has:
> tblOrders
> tblOrderDetails
> --
> Note: In the outer query, tblOrder and tblCustomers are JOINed on
> customerID = orderID. Is this correct (I can't imagine it would be)?
> I changed it in example below in the hopes I was interpreting things
> correctly.
> --
you are absolutely right, Chris.

> In order for an EXISTS predicate to function properly (AFAIK, anyway),
> the subquery must be correlated with the outer query.
> I cannot spot the correlation in the above SQL. Without Table
> Aliases, the query optimizer has no way of linking the subquery to the
> outer query.
>
> The following is the original Query now reformatted with Table Aliases
> and a WHERE clause correlating the subquery and the outer query.
>
> SELECT C1.companyName
> ,O1.orderID
> ,O1.freightCharge
> ,OD1.unitPriceOnOrderDate
> FROM tblCustomers AS C1
> INNER JOIN
> tblOrders AS O1
> ON C1.customerID = O1.customerID
> INNER JOIN
> tblOrderDetails AS OD1
> ON O1.orderID = OD1.orderID
> WHERE EXISTS
> (SELECT O2.orderID
> FROM tblOrders AS O2
> INNER JOIN
> tblOrderDetails AS OD2
> ON O2.orderID = OD2.orderID
> WHERE O1.orderID = O2.orderID
> GROUP BY O2.orderID
> ,OD2.orderID
> ,O2.freightCharge
> HAVING O2.orderID = OD2.orderID
> and O2.freightCharge >
> MIN(OD2.unitPriceOnOrderDate)
> )
> Note: Notice how the WHERE clause refers to a table in the outer query
> by using the alias of the table in the outer query.
> Note: I am making a fairly huge guess on the proper correlation in the
> WHERE clause of the subquery. Pleaes take it for what it is meant to
> be, a hint directing you toward a solution. IOW, this is definitely
> not tested.
you derived exactly the intended solution. I am not very clear on many
aspects of SQL including aliases and it's obvious I have a lot to
learn.

> Sincerely,
> Chris O.
> PS Although meant for microsoft.pulic.sqlserver.programming, the
> following link is still applicable for microsoft.pulic.access.queries:
> http://www.aspfaq.com/etiquette.asp?id=5006, when it comes to
> providing the information that will best enable others to answer your
> question.
thx again for your help. I will definitely get up to speed from an
etiquette point of view.|||
> Hugo,
> The above has:
>
> I am still thinking that customerID cannot equal orderID.
>
> Sincerely,
> Chris O.
yes. it would be a very interesting query that could operate in the
manner specified by myself initially.