March 6, 2008 at 2:24 pm
I am used to using the *= syntax for outer joins in sqlserver 2000, but I heard it's unsupported in 2005, so I'm trying to write properly syntaxed queries in preparation for migration.
Here is the query that works
SELECT
Customer_rate_quote_account.return_indic,
coalesce(Customer_rate_quote_return.rate_quote_id,'0') AS rate_quote_id
FROM Customer_rate_quote_account ,Customer_rate_quote_return
WHERE (Customer_rate_quote_account.cust_id = 1117)
AND (Customer_rate_quote_return.rate_quote_id = 1800450)
and Customer_rate_quote_account.cust_id *= Customer_rate_quote_return.cust_id
and since there is no return in Customer_rate_quote_return, I get a rowset like
return_indicrate_quote_id
20
which is what I want.
However, when I translated it, I got
SELECT Customer_rate_quote_account.return_indic,
COALESCE (Customer_rate_quote_return.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account LEFT OUTER JOIN Customer_rate_quote_return
ON (Customer_rate_quote_account.cust_id = Customer_rate_quote_return.cust_id)
WHERE (Customer_rate_quote_account.cust_id = 1117)
AND (Customer_rate_quote_return.rate_quote_id = 1800450)
which returns
return_indicrate_quote_id
(0 row(s) affected)
What gives????
March 6, 2008 at 2:58 pm
nbraasch (3/6/2008)
SELECT Customer_rate_quote_account.return_indic,
COALESCE (Customer_rate_quote_return.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account LEFT OUTER JOIN Customer_rate_quote_return
ON (Customer_rate_quote_account.cust_id = Customer_rate_quote_return.cust_id)
WHERE (Customer_rate_quote_account.cust_id = 1117)
AND (Customer_rate_quote_return.rate_quote_id = 1800450)
Try checking the joined table value for null...like this
WHERE (Customer_rate_quote_account.cust_id = 1117)
AND (Customer_rate_quote_return.rate_quote_id = 1800450
OR Customer_rate_quote_return.rate_quote_id IS NULL)
If it was easy, everybody would be doing it!;)
March 6, 2008 at 3:04 pm
No luck. I tried the following query:
SELECT Customer_rate_quote_account.return_indic,
COALESCE (Customer_rate_quote_return.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account LEFT OUTER JOIN Customer_rate_quote_return
ON (Customer_rate_quote_account.cust_id = Customer_rate_quote_return.cust_id)
WHERE (Customer_rate_quote_account.cust_id = 1117)
AND ((Customer_rate_quote_return.rate_quote_id = 1800450)
OR (Customer_rate_quote_return.rate_quote_id IS NULL))
and still got
return_indicrate_quote_id
(0 row(s) affected)
weird, huh?
March 6, 2008 at 3:25 pm
That's because this is not an OUTER JOIN. Your original query is looking for rows where the cust_id is not equal. When you re-wrote it, you say cust_id = cust_id in your join predicate. This is not the same thing! Also, since your WHERE clause has a check for rate_quote_id in your return table, the row must exist in that table, thereby making this an INNER JOIN since rows must exist in both tables.
SELECT Customer_rate_quote_account.return_indic,
COALESCE (Customer_rate_quote_return.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account LEFT OUTER JOIN Customer_rate_quote_return
ON (Customer_rate_quote_account.cust_id <> Customer_rate_quote_return.cust_id)
WHERE (Customer_rate_quote_account.cust_id = 1117)
AND (Customer_rate_quote_return.rate_quote_id = 1800450)
It may not be the most efficient way to go, but with the information that we have, this should work.
March 6, 2008 at 3:44 pm
John -
Thanks for the reply.
My original post criteria is all rows from account where cust id = 1117, and any rows from return where (the cust_id = 1117 AND the rate_quote_id = 1800450), returning a zero for the rate quote id if there are no rows matching that criteria.
Take a closer look at the original - I know it works that way because the code has been in production for 3+ years....
Again - I appreciate your help.
March 6, 2008 at 4:01 pm
That's the problem with the *= operator, it leads to an ambiguous query. Try moving the condition into the JOIN clause
SELECT a.return_indic,
COALESCE (r.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account a
LEFT OUTER JOIN Customer_rate_quote_return r
ON a.cust_id <> r.cust_id
AND r.rate_quote_id = 1800450
WHERE a.cust_id = 1117
March 6, 2008 at 6:47 pm
John Rowan (3/6/2008)
SELECT a.return_indic,COALESCE (r.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account a
LEFT OUTER JOIN Customer_rate_quote_return r
ON a.cust_id <> r.cust_id
AND r.rate_quote_id = 1800450
WHERE a.cust_id = 1117
Unless I'm missing something, I don't think this will work...
ON a.cust_id <> r.cust_id
You want to join the tables where the cust_id's are the same, not different.
I agree with putting the condition in the 'FROM'...
FROM Customer_rate_quote_account a
LEFT OUTER JOIN Customer_rate_quote_return r
ON a.cust_id = r.cust_id
AND r.rate_quote_id = 1800450
but it will also work as I showed it using an 'OR" and checking for null.
nbraasch, I built some test data using a query constructed exactly like yours below, where only the primary table had data in it (like yours)
SELECT Customer_rate_quote_account.return_indic,
COALESCE (Customer_rate_quote_return.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account LEFT OUTER JOIN Customer_rate_quote_return
ON (Customer_rate_quote_account.cust_id = Customer_rate_quote_return.cust_id)
WHERE (Customer_rate_quote_account.cust_id = 1117)
AND ((Customer_rate_quote_return.rate_quote_id = 1800450)
OR (Customer_rate_quote_return.rate_quote_id IS NULL))
and it worked perfectly.
Maybe double-check your data in the tables....???? Let us know if you figure it out. 🙂
If it was easy, everybody would be doing it!;)
March 7, 2008 at 7:07 am
Okay, I agree that in theory this should work. Could there be some server setting that is making it not work, like an ANSI Nulls setting?
March 7, 2008 at 7:26 am
could you explain what leads you to believe that it's "not working"? What are you getting or not getting that you were expecting? Give us some specific examples of the data you're getting back.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 7, 2008 at 7:46 am
Thanks for your attention on this. It's driving me a little batty.
I believe it's not working because of the following:
in the account table, there is a row with a cust_id and a return_indic.
In the return table, there is no row matching the rate_quote_id, however there are rows matching the cust_id.
what I want returned is two columns: the return_indic from the account table in column 1, and a 0 from the return table in column 2 (because there is no match). If there is a match, I want the return_indic in column 1, and the rate_quote_id in column 2.
Here is the script for setting up the test:
CREATE TABLE [Customer_rate_quote_account_test] (
[cust_id] [int] NOT NULL ,
[return_indic] [tinyint] NOT NULL ,
CONSTRAINT [Customer_rate_quote_account_pk] PRIMARY KEY CLUSTERED
(
[cust_id]
)
)
GO
Insert into Customer_rate_quote_account_test(cust_id,return_indic)
values('1117','2')
go
CREATE TABLE [Customer_rate_quote_return_test] (
[customer_rate_quote_return_id] [int] IDENTITY (1, 1) NOT NULL ,
[cust_id] [int] NOT NULL ,
[rate_quote_id] [int] NOT NULL ,
CONSTRAINT [Customer_rate_quote_return_pk] PRIMARY KEY CLUSTERED
(
[customer_rate_quote_return_id]
)
)
GO
insert into Customer_rate_quote_return_test(cust_id,rate_quote_id)
values ('1117','1000000')
go
Then, run these two queries against these tables:
SELECT Customer_rate_quote_account_test.return_indic,
COALESCE (Customer_rate_quote_return_test.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account_test LEFT OUTER JOIN Customer_rate_quote_return_test
ON (Customer_rate_quote_account_test.cust_id = Customer_rate_quote_return_test.cust_id)
WHERE (Customer_rate_quote_account_test.cust_id = 1117)
AND ((Customer_rate_quote_return_test.rate_quote_id = 1800450)
OR (Customer_rate_quote_return_test.rate_quote_id IS NULL))
This one currently returns 0 rows. I want it to return 1 row, with the return_indic, and 0 for the rate_quote_id.
SELECT Customer_rate_quote_account_test.return_indic,
COALESCE (Customer_rate_quote_return_test.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account_test LEFT OUTER JOIN Customer_rate_quote_return_test
ON (Customer_rate_quote_account_test.cust_id = Customer_rate_quote_return_test.cust_id)
WHERE (Customer_rate_quote_account_test.cust_id = 1117)
AND ((Customer_rate_quote_return_test.rate_quote_id = 1000000)
OR (Customer_rate_quote_return_test.rate_quote_id IS NULL))
This one returns 1 row, as it should, since the rate_quote_id now matches.
I suspect it has something to do with the IS NULL test.....
March 7, 2008 at 7:52 am
Okay, here it is. I changed John's query to have the cust_id's equal each other, like so:
SELECT a.return_indic,
COALESCE (r.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account a
LEFT OUTER JOIN Customer_rate_quote_return r
ON a.cust_id = r.cust_id
AND r.rate_quote_id = 1800450
WHERE a.cust_id = 1117
and the query works perfectly.
I was left outer joining on account, instead of return. What John's query did was put those in the proper order. I ALWAYS want data from account, so I should be selecting from that, then left outer joining to return.
Thank you John!!
March 7, 2008 at 7:54 am
I got it to work, but had to move the condition to the 'FROM'
SELECT a.return_indic,
COALESCE (b.rate_quote_id, '0') AS rate_quote_id
FROM Customer_rate_quote_account_test a
LEFT OUTER JOIN Customer_rate_quote_return_test b
ON (a.cust_id = b.cust_id)
AND (b.rate_quote_id = 1800450)
WHERE (a.cust_id = 1117)
I guess my data I tried yesterday was a bit different, so it worked with the condition in the 'Where'.
(edit)...going back now, I can see why the condition didn't work in the 'Where'. This was a good exercise. 🙂
If it was easy, everybody would be doing it!;)
March 7, 2008 at 8:32 am
No problem. Looking back today, I don't know what I was thinking with the <> join?? Oh well, I'm glad you've got it working. This one was a group effort!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply