[LUG] FW: MySQL Query problems

["Robin Cornelius" <robin@xxxxxxxxxxxxxxxxxxxxx>]

Hi everyone, I hope someone can help with a small mysql problem I am having.
I have two (related) tables called contacts and pinksheet, contacts is
name/address etc pinksheet is details of specific enquiries. The mapping is
one contact may have none, one or many pinksheet entries, a field in the
pinksheet holds the index key of the row in the contact table.  I what to
get a list of all the contacts and if they have a pinksheet entry extract
some details from it and print name,address,enquiry details etc. I am
currently using something like:-

"SELECT pinksheet.requirment,contact.phone,contact.fax FROM contact,
pinksheet WHERE pinksheet.contactid=contact.PKey"

This only produces contacts with one or more associated pinksheet entries.
How can i change this to select all contacts but also select the appropriate
pinksheet data if it exists for the given contact row, or is this impossible
(my sql is not very good i am afraid!).

Secondly Is there a way of ensuring if more than one pinksheet entries exist
for a contact that i select the newest (i do have date fields in the
pinksheet table). I notice on mysql 4.1 sub queries are supported so i could
use a "SELECT MAX(somedatefield)" within the WHERE clause but i am using
mysql server is v3.23.40, so this is not supported and the only other way
looks hideous.


Many thanks

Robin Cornelius@xxxx


--
The Mailing List for the Devon & Cornwall LUG
Mail majordomo@xxxxxxxxxxxx with "unsubscribe list" in the
message body to unsubscribe.