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.