LEFT OUTER JOIN PROBLEM
-
Hi everyone
I've run into an irritating problem and
although I believe the solution must be
quite obvious, I just don't seem to see it.
I am running SQL 7. Here follows a summary
of the code from one of my views.
------------------------------
SELECT
Customers.Cust_Name,
CASE WHEN Accounts.Account_No IS NOT NULL
THEN Accounts.Account_No
ELSE 'NO ACCOUNT' END
FROM Customers
LEFT OUTER JOIN Accounts
ON Customers.Cust_Name = Accounts.Cust_Name
AND Customers.Location = Accounts.Location
AND (Customers.Acc_Type = Accounts.Type OR Accounts.Acc_Type = 'ALL')
WHERE Accounts.Active = 'True'
------------------------------
Now the problem is in the second last line.
What I require is to attempt a match on the Acc_Type column and if not then look at a value of 'ALL' for Acc_Type on the Accounts table.
Currently I get more than one result where there is a match on Acc_Type and another line with Acc_Type = 'ALL' exists. I only wan't one result, if any!
Any suggestions?
-
Have you tried moving that line to the where clause?
SELECT
Customers.Cust_Name,
CASE WHEN Accounts.Account_No IS NOT NULL
THEN Accounts.Account_No
ELSE 'NO ACCOUNT' END
FROM Customers
LEFT OUTER JOIN Accounts
ON Customers.Cust_Name = Accounts.Cust_Name
WHERE (Customers.Location = Accounts.Location)
AND (Customers.Acc_Type = Accounts.Type OR Accounts.Acc_Type = 'ALL')
AND Accounts.Active = 'True'
-Bill
-
Thanks, but that will not work. It will still give me two results. What I wan't to happen is a test for the first condition and if the condition is not satisfied then a test on the second condition ('ALL'). Currently if both conditions are met then I will get two results, which is wrong. It should only revert to the second condition check if the first one fails.
-
Given the particular columns that you are projecting from your query, why not simply add a DISTINCT clause:
SELECT DISTINCT ...
I wouldn't recommend this if you were doing a SELECT *..., since there would obviously be differences in the two "duplicate" rows that your query is returning, but as long as all of the data in the Cust_Name and Account_No data is the same in the two "duplicate" rows, then the DISTINCT statement should resolve your problem.
Matthew Burr
-
hmm... I don't think this is a problem that DISTINCT can fix. The problem is, joepin is getting both account types returned. The account_no for different account type is probably different, so DISTINCT won't work.
The correct result should display the Cust_Name once, with the Account_No of either the matching Account.Type, or if that doesn't exist, the Account_No w/ Type='ALL'. (Actually this isn't true if the tables are not up to 3rd normal form and from the look of things, they aren't. Hey are there a lot of accounts w/ Type='ALL'?)
I guess I don't really understand the table... but from the given info, can we do it in a CASE statement? Like this:
SELECT Customers.Cust_Name,
CASE WHEN Accounts.Account_No IS NOT NULL
THEN Accounts.Account_No
ELSE 'NO ACCOUNT'
END
FROM Customers
LEFT OUTER JOIN Accounts
ON Customers.Cust_Name = Accounts.Cust_Name
WHERE Accounts.Active = 'True'
AND Customers.Location = Accounts.Location
AND Accounts.Type =
CASE Customers.Acc_Type
WHEN (SELECT a.Type FROM Accounts a WHERE a.Active = 'True' AND a.Cust_Name = Customers.Cust_Name AND a.Location = Customers.Location)
THEN Customers.Acc_Type
ELSE 'ALL'
END
--
Looks so stupid... but I think it will work.
-
Thanks for the help. I am still hacking away at the problem.
-
First for a stupid, off-topic remark. You might consider replacing the CASE construct in your SELECT clause with the ISNULL function. IMHO easier to read and maintain, and achieves the same result.
Now, for the serious stuff. I am pretty sure this will work, but I'm afraid to even think about the performance of this solution. So only use it in emergencies.
SELECT
Customers.Cust_Name,
ISNULL(Accounts.Account_No,'NO ACCOUNT')
FROM Customers
LEFT OUTER JOIN Accounts
ON Customers.Cust_Name = Accounts.Cust_Name
AND Customers.Location = Accounts.Location
AND (Customers.Acc_Type = Accounts.Type OR
(Accounts.Type = 'ALL' and Customers.cust_name NOT IN (SELECT
C2.Cust_Name
FROM Customers C2
LEFT OUTER JOIN Accounts A2
ON C2.Cust_Name = A2.Cust_Name
AND C2.Location = A2.Location
AND C2.Acc_Type = A2.Type)
)
)
WHERE Accounts.Active = 'True'Edited by - NPeeters on 10/29/2002 2:15:25 PM
-
OK, so my first edit was to use the ISNULL function. Stupid to not do it the first time round, if I'm mentioning it in the post itself
So I'm not going to edit once more, but I just noticed that I forgot the 'WHERE A2.Active='True'' stuff in the SELECT for the NOT IN part.
-
Thanks NPeeters, you are absolutely correct about the use of the ISNULL. I created this sample SQL in quite a hurry. But anyway this not part of my real problem 🙂
Your solution will unfortunately not be suitable here. Firstly because you are using a LEFT OUTER JOIN which will always return a result from the Customers table. Should rather be an INNER JOIN?
Like I was saying this is just a small sample of the original SQL which consists of 8 LEFT OUTER JOINS on a whole list of different tables. So basically the JOIN criteria used in the first part of the SQL will have to be replicated in the "NOT IN" part. And as you mentioned, this is were performance will become a serious issue as this VIEW will be used very frequently.
But I still find it difficult to believe that there is not a simple solution for this problem. Simply joining two tables and if one condition is met then ignore the second condition else look at the second condition?
J
-
It's not that simple. What you're talking about - on the surface - sounds like a typical "OR" operator, but the problem is that if you have a row that meets one of the conditions of your expression, and another row that meets the other condition of your expression, and both rows belong to the same customer, you only want to see one of the rows. When SQL Server is selecting data from a table or filtering data in a result set it is - in essence - ignorant to any rows that it has already selected or filtered; in other words, it evaluates each row on an individual basis, without regard to any other rows in the table or result set. If the row meets the conditions, it keeps it, otherwise it discards it. So, if it finds a row that meets one condition of your OR expression, it will keep it. And as it continues working through the set of data, and finds another row that meets the other condition of your expression, it will keep that too; it will not go back through the result set and attempt to find rows that previously matched the expression.
What you are looking to do must be done AFTER the data has been selected or by using correlated subqueries to evaluate a record in the light of some other recordset. Here's one solution:
SELECT Customers.Cust_Name,
ISNULL(Accounts.Account_No, 'NO ACCOUNT')
FROM Customers
JOIN Accounts
ON Customers.Cust_Name = Accounts.Cust_Name
AND Customers.Location = Accounts.Location
AND Customers.Acc_Type = Accounts.Type
AND Accounts.Active = 'True'
UNION
SELECT Customers.Cust_Name,
ISNULL(Accounts.Account_No, 'NO ACCOUNT')
FROM Customers
LEFT OUTER JOIN Accounts
ON Customers.Cust_Name = Accounts.Cust_Name
AND Customers.Location = Accounts.Location
AND Accounts.Acc_Type = 'ALL'
AND Accounts.Active = 'True'
WHERE NOT EXISTS (SELECT *
FROM Customers AS C
JOIN Accounts AS A
ON C.Cust_Name = A.Cust_Name
AND C.Location = A.Location
AND C.Acc_Type = A.Type
AND A.Active = 'True'
WHERE C.Cust_Name = Customer.Cust_Name)That is one solution; I'm not going to claim it's the best - it looks like a pain to me - but it will work. Essentially, we perform the query twice. The first time around, we look for rows that match the first condition: Customers.Acc_Type = Accounts.Type. The second time, we look for rows that meet the second condition, but we use a correlated subquery to ensure that there is no row for the Customers.Cust_Name that also meets the first condition. Then we combine the results of these two sets using UNION operator, which will also apply a DISTINCT operator, so that any duplicates between the two result sets will be merged (and there will likely be duplicates) into one row. The correlated subquery was necessary, though, since the query had to have some way of taking each row and comparing it to all other rows to ensure that there was no row that matched the first condition. Any solution is going to need to do something similar, or use some other post-select operation (such as a GROUP BY) to combine duplicates into one row. Incidentally, this is what Npeeters' solution did, just in a different way. You might find his approach yields better results; although, I would discourage the use of "NOT IN"; I would replace it with "NOT EXISTS".
Matthew Burr
Edited by - mdburr on 10/30/2002 10:54:19 AM
Edited by - mdburr on 10/30/2002 10:55:45 AM
Edited by - mdburr on 11/01/2002 7:24:06 PM
-
Well, looks like there is no shorter way of doing this. Thanks for the input, Matthew.
J
-
Try the union of two more restrictive select statements.
-
I have had similar problems like this and have used the following approach. I do not if it will work and give you the results you want or how it will perform.
SELECT c.Cust_Name,
ISNULL(a.Account_No,ISNULL(b.Account_No,'NO ACCOUNT')) AS 'Account_No '
FROM Customers c
LEFT OUTER JOIN Accounts a
ON a.Cust_Name = c.Cust_Name
AND a.Location = c.Location
AND a.Acc_Type = c.Acc_Type
AND a.Active = 'True'
LEFT OUTER JOIN Accounts b
ON b.Cust_Name = c.Cust_Name
AND b.Location = c.Location
AND b.Acc_Type = 'ALL'
AND b.Active = 'True'
Far away is close at hand in the images of elsewhere.
Anon. -
Hi,
By modifying the right hand side of the OR clause to exclude the match given on the left hand side we get this
SELECT
Customers.Cust_Name,
ISNULL (Accounts.Account_No, 'NO ACCOUNT')
FROM
Customers LEFT OUTER JOIN Accounts
ON Customers.Cust_Name = Accounts.Cust_Name
AND Customers.Location = Accounts.Location
AND (Customers.Acc_Type = Accounts.Type OR
(Accounts.Acc_Type = 'ALL' AND Customers.Acc_Type <> Accounts.Type))
WHERE
Accounts.Active = 'True'
which has no unions, subqueries or NOT INs.
Maybe this is better, as we exclude rows in the WHERE not the JOIN.
Cheers
Shawn
SELECT
Customers.Cust_Name,
ISNULL (Accounts.Account_No, 'NO ACCOUNT')
FROM
Customers LEFT OUTER JOIN Accounts
ON Customers.Cust_Name = Accounts.Cust_Name
AND Customers.Location = Accounts.Location
WHERE
Accounts.Active = 'True' AND
(Customers.Acc_Type = Accounts.Type OR
(Accounts.Acc_Type = 'ALL' AND Customers.Acc_Type <> Accounts.Type))
-
8 left outer joins?
Perhaps some denormalisation is required?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply