October 31, 2002 at 7:05 am
Have you tried sorting the results based on the priority of the two records and then taking the TOP 1. Therefore if the query returns more than one row it doesn't matter.
SELECT TOP 1
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'
ORDER BY Accounts.Type
I am not sure what column the ORDER BY should be on.
October 31, 2002 at 7:34 am
Why not just use a temp table?
SELECT C.Cust_Name,
A.Account_No,
C.Location,
C.Acc_Type
INTO #Customers
FROM Customers C,
Accounts A
WHERE C.Cust_Name *= A.Cust_Name
AND C.Location *= A.Location
AND C.Acc_Type *= A.Type
AND A.Active = 'True'
INSERT INTO #Customers
SELECT C1.Cust_Name,
A.Account_No,
C1.Location,
C1.Acc_Type
INTO #Customers
FROM Customers C1,
Accounts A
WHERE C1.Cust_Name *= A.Cust_Name
AND C1.Location *= A.Location
AND A.Acc_Type = 'ALL'
AND A.Active = 'True'
AND NOT EXISTS (
SELECT 1
FROM #Customers C2
WHERE C2.Cust_Name = C1.Cust_Name
AND C2.Location = C1.Location)
SELECT Cust_Name,
IsNull(Accounts.Account_No, 'NO ACCOUNT') Account_No
FROM #Customers
Sorry for the *=. It's the way I was raised.
October 31, 2002 at 9:10 am
quote:
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'
This is a very elegant solution; I like it. Should have reasonably good performance - when compared with the other suggestions - too.
Matthew Burr
Edited by - mdburr on 10/31/2002 4:24:23 PM
November 1, 2002 at 2:13 am
Great stuff.
Both the solutions that DavidBurrows and GBN gave work well. The performance is a bit better on GBN's solution though. Must be the fact that there is only one JOIN as compared to two. Thanks a lot.
And again SQL SERVER CENTRAL RULES!
November 1, 2002 at 10:46 am
I'm surprised that GBN's solution doesn't give you errant results. Consider a situation where you have:
CREATE TABLE Customers
(Cust_Name VARCHAR(30),
Acc_Type VARCHAR(10),
Location VARCHAR(10))
CREATE TABLE Accounts
(Cust_Name VARCHAR(30),
Type VARCHAR(10),
Location VARCHAR(10),
Acc_Type VARCHAR(10),
Account_No VARCHAR(20),
Active VARCHAR(5))
INSERT INTO Customers
VALUES ('John Doe', 'Checking', 'BankA')
INSERT INTO Customers
VALUES ('Jane Doe', 'Savings', 'BankB')
INSERT INTO Accounts
VALUES ('John Doe', 'Checking', 'BankA', 'Private', 1, 'True')
INSERT INTO Accounts
VALUES ('John Doe', 'Savings', 'BankA', 'ALL', 2, 'True')
INSERT INTO Accounts
VALUES ('Jane Doe', 'Checking', 'BankB', 'ALL', 3, 'True')
Now, according to your requirements, you'd want John Doe's first account, Account_no 1, to appear in the results, and not his second (since his first row met the first condition; you would only want his second account, Account_no 2, to appear if there were no rows that met the condition: Customers.Acc_type = Accounts.Type), while in Jane Doe's case - since she doesn't have a row that meets your first condition - her other row - which meets your second condtion - should be in the results.
Now, try GBN's query, and see what you get. You should find that BOTH of John Doe's results were returned, as opposed to just returning the first row. So, either the query results don't match your requirement, or they do match your requirements, but you mistated your requirements or I misunderstood your requirements. So, which is it?
Matthew Burr
Edited by - mdburr on 11/01/2002 10:49:17 AM
November 1, 2002 at 3:11 pm
Just caught onto this thread and not sure if this has already been said but you should never reference a table from an outer join in a where clause, except for testing NULLs. If you do it forces an inner join.
You can also use COALESCE
i.e SELECT c.Cust_Name,
COALESCE(a.Account_No,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'
You also need to be careful or one of the left joins returning >1 row, don't know your data and so don't know if this could be a problem
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 1, 2002 at 7:22 pm
Yes, simonsabin is right about this. Because of this fact, I went back and rewrote my original solution to move the "Accounts.Active = 'True'" into the JOIN clause and out of the WHERE clause. Incidentally, I also recognized a flaw in the correlated subquery I had. Since it contained a LEFT OUTER JOIN it would always return results (unless the inner table is empty) and so the NOT EXISTS statement would always be false; thus, the second query in the UNION would never return results. This was masked originally, because the "Accounts.Active = 'True'" in that statement was causing it to be handled as an inner join, so it was occasionally not returning results. Consequently, I've corrected that subquery. It should use an inner join and not an outer join, and in a similar vein the first query in the union should also use an inner join and not an outer join. Anyway, I made the corrections in my original suggested solution.
Matthew Burr
November 4, 2002 at 1:38 am
No, Matthew, you actually did understand the requirements correctly. It was my test data that was not complete, that's why GBN's solution worked at the time. I replaced it with the two LEFT JOIN's solution. And I will also look at the posibility of one of the JOINS returning more than one result. Thanks for that Simon.
J
November 5, 2002 at 4:17 am
Hi all,
try this
SELECT Customers.Cust_Name, accounts.acc_type,
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 AND 1 = (CASE WHEN Customers.Acc_Type = Accounts.Type THEN 1 ELSE 0 END)) OR (Accounts.Acc_Type = 'ALL' AND 1 = CASE WHEN Customers.Acc_Type = Accounts.Type THEN 0 ELSE 1 END))
WHERE Accounts.Active = 'True'
November 5, 2002 at 9:23 am
replace AND (Customers.Acc_Type = Accounts.Type OR Accounts.Acc_Type = 'ALL')
with
AND (Customers.Acc_Type = Accounts.Type) OR (Customers.Acc_Type <> Accounts.Type and Accounts.Acc_Type = 'ALL')
November 6, 2002 at 12:46 pm
Does the DISTINCT keyword solve the problem?
SELECT DISTINCT
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'
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
November 12, 2002 at 1:00 am
Hi Steve
The use of DISTINCT won't work since
the record where
Accounts.Acc_Type = 'ALL' might be matched before Customers.Acc_Type = Accounts.Type is matched and then the 'ALL' will be displayed due to DISTINCT where in fact I wanted the
match Customers.Acc_Type = Accounts.Type to be displayed.
But as I mentioned earlier the use of two LEFT OUTER JOINS solved my problem.
Thanks anyway
J
December 16, 2002 at 12:18 pm
might this work?
SELECTC.Cust_Name,
CASE
WHEN A.Account_No IS NOT NULL
THEN A.Account_No
ELSE 'NO ACCOUNT'
END
FROMCustomers C LEFT JOIN
Accounts A
ONC.Cust_Name = A.Cust_Name
ANDC.Location = A.Location
ANDA.Type IN (C.Acc_Type,'ALL')
WHEREA.Active = 'True'
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply