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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply