April 14, 2009 at 12:23 pm
Dear all,
I have the following tables (simplified):
Object (An Object represents a house with a postcode)
ObjectID (int) (PK)
PostCode (varchar)
Client (A Client represents a customer)
ClientID (int) (PK)
PostCodeRange (A PostCodeRange represents a range of Postcodes that for some reason we group together. For example a Range could be (1, "Berlin", "5512CX", "5600ZZ") )
RangeID (int) (PK)
RangeName (varchar)
PostCodeRangeFrom (varchar)
PostCodeRangeTo (varchar)
Client_PostCodeRange (A secondary table that holds multiple PostCodeRange records per Client record)
ClientID (int) (PK) (FK-Client)
RangeID (int) (PK) (FK-PostCodeRange)
What is required: For a specific Client we want to find all Object records that "fall" within the Client's preferences (Client_PostCodeRange records).
Currently this is achieved using a stored procedure that employs a simple iteration: For each PostCodeRange a Client might have, we search for the Objects whose PostCode is BETWEEN PostCodeRangeFrom AND PostCodeRangeTo. We store these Object records in a temporary table and in the end we just return the temporary table.
I was curious if there is a way to avoid the aforementioned iteration and handle it with a join of some kind (or whatever is more efficient).
April 14, 2009 at 12:34 pm
vboucharas (4/14/2009)
Dear all,I have the following tables (simplified):
Object (An Object represents a house with a postcode)
ObjectID (int) (PK)
PostCode (varchar)
Client (A Client represents a customer)
ClientID (int) (PK)
PostCodeRange (A PostCodeRange represents a range of Postcodes that for some reason we group together. For example a Range could be (1, "Berlin", "5512CX", "5600ZZ") )
RangeID (int) (PK)
RangeName (varchar)
PostCodeRangeFrom (varchar)
PostCodeRangeTo (varchar)
Client_PostCodeRange (A secondary table that holds multiple PostCodeRange records per Client record)
ClientID (int) (PK) (FK-Client)
RangeID (int) (PK) (FK-PostCodeRange)
What is required: For a specific Client we want to find all Object records that "fall" within the Client's preferences (Client_PostCodeRange records).
Currently this is achieved using a stored procedure that employs a simple iteration: For each PostCodeRange a Client might have, we search for the Objects whose PostCode is BETWEEN PostCodeRangeFrom AND PostCodeRangeTo. We store these Object records in a temporary table and in the end we just return the temporary table.
I was curious if there is a way to avoid the aforementioned iteration and handle it with a join of some kind (or whatever is more efficient).
is a multiple way join
something like
select Distinct o.ObjectID
from Object o
inner join
PostCodeRange pr
ON o.PostCode BETWEEN pr.PostCodeRangeFrom AND pr.PostCodeRangeTo
inner join
Client_PostCodeRange cr
ON pr.RangeID = cr.RangeID
inner join
Client c
ON c.ClientId = cr.ClientID
where c.ClientID = x
* Noel
April 14, 2009 at 12:36 pm
I think this will do what you want:
SELECT
O.OBJECTID,
O.PostCode
FROM
OBJECT AS O JOIN
PostCodeRange AS PCR ON
O.PostCode BETWEEN PCR.PostCodeRangeFrom AND PostCodeRangeTo JOIN
Client_PostCodeRange CPCR ON
PCR.RangeID = CPCR.RangeID
WHERE
CPCR.ClientID = @ClientID
Edit: Noel won.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2009 at 1:24 pm
Jack, that not only was a neat solution, it also opened up a whole new world of joins to me! Thank you very very much! Do you know of any good source (i.e book, for such advanced sql joins? I would be really interested in learning more).
p.s.: Noel, I do not know if your solution works (I didn't test it) but it seems it can work.
April 14, 2009 at 1:35 pm
Thanks, but Noel's solution is the same with respect to the joins. Noel just added distinct and accesses the Client as well, but is essentially the same.
You can use any operator in a join, =, <>, , CASE, etc... Some perform better than others.
Any book by Itzik Ben-Gan would be a good one. Probably his fundamentals book would be the best starting point.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2009 at 1:47 pm
Thank you!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply