reading data from three tables using mysql

I have to join the data from two tables. There are three table record,address and names.Id is the primary key in record table and foreign key in address and names table.There can be multiple name records for particular record.

RECORD(id (PK),text,search)
address(address_id(PK),type,street,city, id (FK))
name(name_id(PK),name,id (FK))

I want to get the data from these three tables in a way so that i will get the only first name record for each record id,not all the name records for record id's.

insert into RECORD values (1,record1,record1search);
insert into RECORD values (2,record2,record2search);

insert into name values (1,xxx,1);
insert into name values name(2,yyy,1)
insert into name values name(3,zzz,2)

i want to retriev the values in way:

record_i开发者_运维知识库d,text,namename_id:

1,record1,xxx,1
2,record2,zzz,3

I want the only first name record not the second or thirrd name record for particular record.

please help.

You will need an interim query on name by the record ID to find the FIRST ID and use THAT as basis to join to the actual name table. Also, you didn't provide any sample data for your address table, but that would be done in a similar fashion as the first names query performed here. Also, would the address table be associated to a specific named person and not just the ID? Will THAT be 1:many relationship to the Record ID?

select
      r.id,
      r.text,
      n2.name,
      n2.name_id
   from 
      Record r,
         join ( select n.id, min( n.name_id ) FirstNameID
                  from name n
                  group by n.id ) FirstNames
           on r.id = FirstNames.id
           join name n2
              on FirstNames.FirstNameID = n2.name_id

   SELECT RECORD.id, RECORD.text, name.name, name.name_id
     FROM RECORD
LEFT JOIN address
       ON address.id = RECORD.id
LEFT JOIN name
       ON name.id = RECORD.id
 GROUP BY RECORD.id