April 28, 2010 at 9:26 pm
create table A (
CusNo_A int,
)
create table B (
CustNo_B int,
Second_CustNo int
)
create table C (
CustNo_C int,
CustName char(10)
)
Code
insert into A(CusNo_A)values(1)
insert into A(CusNo_A)values(3)
insert into A(CusNo_A)values(5)
insert into B(CustNo_B,Second_CustNo)values(1,2)
insert into B(CustNo_B,Second_CustNo)values(3,4)
insert into B(CustNo_B,Second_CustNo)values(6,7)
insert into C(CustNo_C,CustName)values(2,'nguyennd12')
insert into C(CustNo_C,CustName)values(3,'nguyennd23')
insert into C(CustNo_C,CustName)values(4,'nguyennd34')
insert into C(CustNo_C,CustName)values(5,'nguyennd56')
Flow Process:
If [A].[CusNo_A] exist .[CustNo_B] then Get [Second customer no]
-Join [C] ON [Second_CustNo] = [CustNo_C] to Get [customer name]
Else
-Join [C] ON [CusNo_A] = [CustNo_C] to Get [customer name]
Result:
[Name]
nguyennd12
nguyennd34
nguyennd56
Who can process it on a query. Pls, help me. Thanks
April 28, 2010 at 10:07 pm
I have a SQL for my solution:-D
select * from
A
left join B
on A.CusNo_A = B.CustNo_B
inner join C
on B.Second_CustNo = C.CustNo_C OR A.CusNo_A = C.CustNo_C
where
(Second_CustNo = CustNo_C)
or
(CusNo_A = CustNo_C AND CustNo_B IS NULL)
but the query is low performance
April 28, 2010 at 10:23 pm
Any guidance would be appreciated.... Thanks!
April 29, 2010 at 8:33 pm
The following code will give you the same result, but I don't think it will change your performance. The execution plan is pretty straightforward, and appears to be identical in both cases. In production, I'd make sure that tables A, B, and C are all indexed on the customer number columns. Beyond that, I don't know what to tell you without seeing the execution plan when you run your query with production volumes. Good luck.
select *
from A
left join B on A.CusNo_A = B.CustNo_B
left join C on C.CustNo_C = COALESCE(B.Second_CustNo,A.CusNo_A)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2010 at 9:03 pm
@The Dixie Flatline : Thanks you very much.
The query is short more.. Of course, tables A, B, and C are all indexed on the customer number columns.
May 2, 2010 at 9:10 pm
Using EXISTS with pass-through conditions may be more efficient:
USE tempdb;
GO
CREATE TABLE A (CusNo_A INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE B (CustNo_B INTEGER NOT NULL UNIQUE, Second_CustNo INTEGER UNIQUE);
CREATE TABLE C (CustNo_C INTEGER PRIMARY KEY, CustName CHAR(10) NOT NULL);
GO
INSERT A(CusNo_A) VALUES(1);
INSERT A(CusNo_A) VALUES(3);
INSERT A(CusNo_A) VALUES(5);
INSERT B(CustNo_B,Second_CustNo) VALUES (1,2);
INSERT B(CustNo_B,Second_CustNo) VALUES (3,4);
INSERT B(CustNo_B,Second_CustNo) VALUES (6,7);
INSERT C(CustNo_C,CustName) VALUES (2,'nguyennd12');
INSERT C(CustNo_C,CustName) VALUES (3,'nguyennd23');
INSERT C(CustNo_C,CustName) VALUES (4,'nguyennd34');
INSERT C(CustNo_C,CustName) VALUES (5,'nguyennd56');
GO
SELECT C.CustName
FROM C
WHERE 1 =
CASE
WHEN EXISTS (SELECT * FROM B WHERE B.Second_CustNo = C.CustNo_C) THEN 1
WHEN EXISTS (SELECT * FROM B WHERE B.CustNo_B = C.CustNo_C) THEN 0
WHEN EXISTS (SELECT * FROM A WHERE A.CusNo_A = C.CustNo_C) THEN 1
ELSE 0
END;
GO
DROP TABLE A, B, C;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 2, 2010 at 10:04 pm
@paul-2 White NZ : Thanks you for help.
But about require of business:
WHEN EXISTS (SELECT * FROM B WHERE B.Second_CustNo = C.CustNo_C) THEN 1
If not extist (A.CustNo_A = B.CustNo_B) is incorrect.
May 2, 2010 at 10:24 pm
nguyennd (5/2/2010)
@Paul White NZ : Thanks you for help.But about require of business:
WHEN EXISTS (SELECT * FROM B WHERE B.Second_CustNo = C.CustNo_C) THEN 1If not extist (A.CustNo_A = B.CustNo_B) is incorrect.
USE tempdb;
GO
CREATE TABLE A (CusNo_A INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE B (CustNo_B INTEGER NOT NULL UNIQUE, Second_CustNo INTEGER, UNIQUE (Second_CustNo, CustNo_B));
CREATE TABLE C (CustNo_C INTEGER PRIMARY KEY, CustName CHAR(10) NOT NULL);
GO
INSERT A(CusNo_A) VALUES(1);
INSERT A(CusNo_A) VALUES(3);
INSERT A(CusNo_A) VALUES(5);
INSERT B(CustNo_B,Second_CustNo) VALUES (1,2);
INSERT B(CustNo_B,Second_CustNo) VALUES (3,4);
INSERT B(CustNo_B,Second_CustNo) VALUES (6,7);
INSERT C(CustNo_C,CustName) VALUES (2,'nguyennd12');
INSERT C(CustNo_C,CustName) VALUES (3,'nguyennd23');
INSERT C(CustNo_C,CustName) VALUES (4,'nguyennd34');
INSERT C(CustNo_C,CustName) VALUES (5,'nguyennd56');
GO
SELECT C.CustName
FROM C
WHERE 1 =
CASE
WHEN EXISTS (SELECT * FROM B JOIN A ON A.CusNo_A = B.CustNo_B WHERE B.Second_CustNo = C.CustNo_C) THEN 1
WHEN EXISTS (SELECT * FROM B WHERE B.CustNo_B = C.CustNo_C) THEN 0
WHEN EXISTS (SELECT * FROM A WHERE A.CusNo_A = C.CustNo_C) THEN 1
ELSE 0
END;
GO
DROP TABLE A, B, C;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 2, 2010 at 10:51 pm
Yes, I think so.
I rewrite your query:
WHEN EXISTS (SELECT * FROM B JOIN A ON A.CusNo_A = B.CustNo_B WHERE B.Second_CustNo = C.CustNo_C) THEN 1
WHEN EXISTS (SELECT * FROM A WHERE A.CusNo_A = C.CustNo_C) THEN 1
ELSE 0
Thanks.
nguyennd
May 2, 2010 at 11:23 pm
nguyennd (5/2/2010)
Yes, I think so.
You think so...what? I didn't ask a question.
I rewrite your query:
WHEN EXISTS (SELECT * FROM B JOIN A ON A.CusNo_A = B.CustNo_B WHERE B.Second_CustNo = C.CustNo_C) THEN 1
WHEN EXISTS (SELECT * FROM A WHERE A.CusNo_A = C.CustNo_C) THEN 1
ELSE 0
Why? It produces the wrong results if you do that!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 1:05 am
nguyennd (5/2/2010)
Yes, I think so.
WHEN EXISTS (SELECT * FROM B WHERE B.Second_CustNo = C.CustNo_C) THEN 1
=>WHEN EXISTS (SELECT * FROM B JOIN A ON A.CusNo_A = B.CustNo_B WHERE B.Second_CustNo = C.CustNo_C) THEN 1
I rewrite your query:
WHEN EXISTS (SELECT * FROM B JOIN A ON A.CusNo_A = B.CustNo_B WHERE B.Second_CustNo = C.CustNo_C) THEN 1
WHEN EXISTS (SELECT * FROM A WHERE A.CusNo_A = C.CustNo_C) THEN 1
ELSE 0
Sorry, I confused.It produces the wrong results. Thanks
May 3, 2010 at 1:06 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply