Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sleepless_b
Starting Member
1 Post |
 Posted - 2009-04-14 : 14:33:14
|
| 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). |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-14 : 14:59:09
|
| Use this:SELECT * FROM Object JOIN (SELECT PostCodeRangeFrom, PostCodeRangeTo FROM PostCodeRange JOIN Client_PostCodeRange ON PostCodeRange.RangeID = Client_PostCodeRange.RangeID WHERE ClientID = 1) AS tbl1 ON Object.PostCode BETWEEN tbl1.PostCodeRangeFrom AND tbl1.PostCodeRangeToI don't have a SQL editor in front of me so I can't test this but it should work. Remember to modify ClientID = 1 to the client id you want. |
 |
|
|
|
|
|
Between's FROM/TO values from 1_* relationship