Getting a value from another table

[jcstanley]

Hi I have two tables with the following fields:

 

Table 1: offices

officeid (Primary Key), name, abbreviation

 

Table 2: members

memid (Primary Key), fname, surname, email, office

 

A form is used to insert a new member into the members table - the value stored in the 'office' field is the officeid which is selected from a dynamic menu which displays lists all of the rows in the offices table.

 

This works fine and is not a problem.

 

I have a page which queries the members table for all rows and columns and displays the results in a table on the page.

 

SELECT memid, surname, fname, email, office FROM members

 

The part where I have got stuck is converting the value stored in the 'office' field in the members table to show associated value stored in the 'abbreviation' field in the offices table.  Basically I want the user to see some text (the abbreviation field) rather than a meaningless number (officeid).

 

I would be grateful if you could point me in the right direction.

 

Hope this makes sense!  If you need more info please let me know.

 

Thanks in advance

 

[fenway]

You need to JOIN these tables:

 

SELECT

m.memid, m.surname, m.fname, m.email, o.abbreviation

FROM members AS m

INNER JOIN offices AS o ON ( o.officeid = m.office )

[jcstanley]

Thanks that worked, but now i have added 2 more office columns to the members table so it now looks like:

 

Table: members

memid (Primary Key), fname, surname, email, office1, office2, office3

 

each of member can belong in upto 3 offices hence office1, 2 and 3.

 

what do i need to change in the code to make it work:

 

SELECT 
m.memid, m.surname, m.fname, m.email, o.abbreviation
FROM members AS m
INNER JOIN offices AS o ON ( o.officeid = m.office )

 

Thanks

 

[fenway]

What you need to do is not mess up your DB normalization -- you need a third table to relate members to offices.