June 3, 2010 at 9:57 am
I'm working on redoing a really old application that was written almost 15 years ago in VB4 (now in VB6). Anyway, I won't bore you with all of the details. The reason I'm writing this post is I'm trying to understand what the original developer was trying to do with some SQL he put into a view. I consider myself to be pretty good, at SQL, but this developer used CROSS JOIN in the view with a WHERE clause. I've never used a CROSS JOIN in anything. I've looked up CROSS JOIN's in BOL and see that if the WHERE clause is used, then it is (sort of) like an INNER JOIN. Than, why not just use an INNER JOIN? OK, let me give you a little bit of information so you'll know what I'm talking about, as I'd appreciate your help in trying to understand what the developer was trying to do with his view. (BTW, that develop left years ago.)
OK, there are only 3 tables involved in the view. Cutting out extraneous info, here's what's important with the first table:
CREATE TABLE RequestForServices(
ClientNumber int NOT NULL,
CaseNumber tinyint NOT NULL,
CONSTRAINT aaaaaRequestForServices_PK PRIMARY KEY NONCLUSTERED
(
ClientNumber ASC,
CaseNumber ASC
)
Now, here's the second table definition (it's the complete definition):
CREATE TABLE ReferralSourceTypes(
ReferralSourceType tinyint NOT NULL,
ReferralSourceDesc char(50) NOT NULL,
CONSTRAINT PK_ReferralSourceTypes PRIMARY KEY NONCLUSTERED
(
ReferralSourceType ASC
)
And now for the third table (also this is complete):
CREATE TABLE SourceOfReferral(
ReferralSource smallint NOT NULL,
[Description] varchar(50) NULL,
ReferralSourceType tinyint NULL,
CurrentlyValid bit NOT NULL,
CONSTRAINT aaaaaSourceOfReferral_PK PRIMARY KEY NONCLUSTERED
(
ReferralSource ASC
)
And last, but not least, the code that forms the view:
CREATE VIEW vwReferralSource AS
SELECT ClientNumber,CaseNumber,ReferralSourceTypes.ReferralSourceType,ReferralSourceTypes.ReferralSourceDesc FROM
RequestForServices,ReferralSourceTypes
WHERE ReferralSourceTypes.ReferralSourceType=
( SELECT SourceOfReferral.ReferralSourceType FROM SourceOfReferral WHERE
RequestForServices.ReferralSource = SourceOfReferral.ReferralSource )
Looking over these 3 table definitions and the view definition, what was the original developer trying to accomplish?
Kindest Regards, Rod Connect with me on LinkedIn.
June 3, 2010 at 10:19 am
Maybe:
SELECT S.ClientNumber
,S.CaseNumber
,T.ReferralSourceType
,T.ReferralSourceDesc
FROM RequestForServices S
JOIN SourceOfReferral R
ON S.ReferralSource = R.ReferralSource
JOIN ReferralSourceTypes T
ON S.ReferralSourceType = T.ReferralSourceType
June 3, 2010 at 10:36 am
Assume all your 3 tables has only one column.
Values in Table RequestForServices:
A1
A2
A3
A4
Values in Table ReferralSourceTypes:
B1
B2
B3
Values in Table SourceOfReferral
A1
A2
So your following query :
SELECT ClientNumber,CaseNumber,ReferralSourceTypes.ReferralSourceType,ReferralSourceTypes.ReferralSourceDesc FROM
RequestForServices,ReferralSourceTypes
will produce a Cartesian Product of the above values, like :
RequestForServicesReferralSourceTypes
A1B1
A1B2
A1B3
A2B1
A2B2
A2B3
A3B1
A3B2
A3B3
A4B1
A4B2
A4B3
And finally, your follwing code:
WHERE ReferralSourceTypes.ReferralSourceType=
( SELECT SourceOfReferral.ReferralSourceType FROM SourceOfReferral WHERE
RequestForServices.ReferralSource = SourceOfReferral.ReferralSource )
will filter out the above result in 2 steps
1. Retrieve all the ReferralSourceType values from SourceOfReferral table where values of ReferralSource are present in RequestForServices.ReferralSource
2. From the above set of ReferralSourceType, filter the Cartesian Product with those values; so your final result set will be like:
RequestForServicesReferralSourceTypes
A1B1
A1B2
Hope this clears the air.. If still you have doubt, i will give u one sample here; execute one by one and see for yourself :
SET NOCOUNT ON
DECLARE @T1 TABLE (C1 CHAR(2))
DECLARE @T2 TABLE (C2 CHAR(2))
DECLARE @T3 TABLE (C3 CHAR(2),C4 CHAR(2))
INSERT INTO @T1 VALUES('A1')
INSERT INTO @T1 VALUES('A2')
INSERT INTO @T1 VALUES('A3')
INSERT INTO @T1 VALUES('A4')
INSERT INTO @T2 VALUES('B1')
INSERT INTO @T2 VALUES('B2')
INSERT INTO @T2 VALUES('B3')
INSERT INTO @T3 VALUES('A1','B1')
INSERT INTO @T3 VALUES('A2','B2')
-- THIS WILL PRODUCE THE CARTESIAN PRODUCT
SELECT * FROM @T1 T1 , @T2 T2
/*
RESULT:
C1 C2
---- ----
A1 B1
A2 B1
A3 B1
A4 B1
A1 B2
A2 B2
A3 B2
A4 B2
A1 B3
A2 B3
A3 B3
A4 B3
*/
-- THE WHERE CLAUSE FILTER; STEP 1
SELECT T3.C4 FROM @T3 T3 WHERE T3.C3 IN (SELECT C1 FROM @T1)
/*
RESULT:
C4
----
B1
B2
*/
-- THE WHERE CLAUSE FILTER; STEP 2
-- FIND ONLY THE VALUES THAT MATCH THE ABOVE QUERY'S RESULTS
SELECT * FROM @T1 T1 , @T2 T2
WHERE T2.C2 = (SELECT T3.C4 FROM @T3 T3 WHERE T3.C3 = T1.C1)
/*
RESULT:
C1 C2
---- ----
A1 B1
A2 B2
*/
Happy learning!!
C'est Pras!
June 3, 2010 at 1:02 pm
Ken McKelvey (6/3/2010)
Maybe:
SELECT S.ClientNumber
,S.CaseNumber
,T.ReferralSourceType
,T.ReferralSourceDesc
FROM RequestForServices S
JOIN SourceOfReferral R
ON S.ReferralSource = R.ReferralSource
JOIN ReferralSourceTypes T
ON S.ReferralSourceType = T.ReferralSourceType
To me, that's a more elegant solution.
Kindest Regards, Rod Connect with me on LinkedIn.
June 3, 2010 at 7:50 pm
Rod at work (6/3/2010)
Thank you, ColdCoffee, for using a simpler example. That makes the concept of CROSS JOIN with a WHERE clause easier to understand.
Wow, such a nice feeling that my blabbering made someone understand a concept.. Thanks, Rod! 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply