470,648 Members | 1,396 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,648 developers. It's quick & easy.

REQ: Help with M:N cross referenced tables requested

Dear databasers,

here's what I need to accomplish in mysql:

Table "person":
Fields: pid, name, email

Table "address":
Fields: aid, street, city, zip, state, validfrom

Table "xref_pers_addr":
Fields: refid, pid, aid, timestamp

for security reasons I want to configure the user's access to the
database to be write only, no delete or drop should be allowed. Hence
when an address of a person has to be updated (e.g. because he or she
moved) a ne entry has to be made in the address table with an updated
"validfrom" field. In the long run there will be more than one address
entry per person, and only the youngest entry is the valid one. Which
SQL query allows to get a list like

pid, name, street, city, zip, state, validfrom

that only shows the latest address per person?

I assume there must be an sql hack for mysql version < 4.0 which
accomplishes that without using subqueries that are only available in
mysql >=4.1. which i do not intend to deploy on my server.

My guess was this:

SELECT pid, name, CONCAT(MAX(validfrom)," ", street," ", city," ",
zip," ", state) FROM person,address,xref_pers_addr WHERE
person.pid=xref_pers_addr.pid AND address.aid=xref_pers_addr.aid GROUP
BY pid;

That grabs only the latest address, but I obtain the whole address in
one field an I have to disassemble it later into the respective fields
which is quite cumbersome.

Any other option to do that without using subqueries?

Best regards and thanks in advance - dudelman.
Jul 19 '05 #1
0 954

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by John F Dutcher | last post: by
reply views Thread by Tim Sawlor | last post: by
3 posts views Thread by bp | last post: by
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.