table joins and queries

[Mark77]

Hello

I have a database which holds a main table. The main table holds the first part of all UK postcodes. This has three fields, ID, Postcode Outer and Date added.

The second table holds a further ID, the ID number from the main table, the next part of the postcode which is a numeric and again date added.

The third table holds ID, ID from the second table, last two letters of the postcode and date_added

The final table holds ID, ID from the third table, buildings area, contents area and effective date.

Each postcode is given a rating area to allow us to price insurance based on home location. These rating areas change regularly hence the need for an effective date in the fourth table.

I have a query (thanks to Giacomo who's explanation to someone elses query with some adaption allows me to pull the newest rate from the fourth table. The SQL is as follows:

SELECT x.Effective_date, x.Walk_ID, x.Builidngs_Area, x.Contents_Area
FROM tbl_Rating_areas AS x
WHERE (((x.Effective_date)=(select max(effective_date) from tbl_rating_areas where walk_id = x.Walk_ID)));

What I would like to do though is add fields to this query to show information from the first table, second table and third table. However by doing this I then get multiple results from the fourth table showing all results instead of the latest rates.

Please could someone help me understand this or if I have the design wrong any pointers would be gratefully received.

Thanks

 

[VicRauch]

Mark,
I have not looked at this real close, but a quick suggestion would be to use the query you already have, then using that, then join table3 to the matching join table in this query. do the same with the joins of table3 to table2 and table2 to table1. I believe you will then have what you are looking for.
HTH,

 

[giacomo]

Glad you were able to recycle some of my SQL

Can you explain the UK postal code logic and why it's beneficial to split it up into components? I think if I understood it better I could offer a solution. What you're experiencing is called cartesian product and I believe it's happening because you have a many-to-many join somewhere.

hth,
Giacomo

 

[Mark77]

Thanks VicRauch.

I am not entirely sure on how to make that join, however I will try it and post back if I get stuck, I enjoy trying to sort these things out but sometimes just need that little bit of guidance. Thank you for your reply though it is appreciated.

Giacomo

The UK postcode system holds 1.7million full postcodes. The first part, the outward has 2900 combination. If you include the first numeric of the second half the permuations increases to 9500. The final two letters add the final totals.

What I was trying to achieve here was to avoid repeating data as each outward has various sectors which in turn have various walks. Each subsequent walk will then have a rating area for buildings insurance and one for contents.

Hope that brief explanation provides a little more depth for you, sorry I should have included this in the first post!

Regards

Mark