Basic MySQL Table Join?

Question

I have these 2 tables:

ASSOCIATION_TABLE: [id, key, name, manager, office, bank, customer]

and

OFFICE_TABLE: [id, name, address, phone]

I am currently running this query to get a data set that I need:

SELECT `name`, `key`, `office`, `manager`, `id` 
FROM `database`.`ASSOCIATION_TABLE`
WHERE `association`.`customer`=4;

How can I modify this query to display the NAME from the OFFICE_TABLE, rather than the ID? I think a table join is my solution, but I'm nut sure what kind, or how exactly to use it.

Thanks in advance.

Answer 1

SELECT `name`, `key`, ot.name AS OFFICE_NAME, `manager`, `id` 
  FROM `ASSOCIATION_TABLE` at
       LEFT OUTER JOIN OFFICE_TABLE ot
       ON ot.id = at.office
 WHERE `association`.`customer`=4;

That's an outer join to OFFICE_TABLE. Your resultset will include any records in the ASSOCIATION_TABLE that do not have records in OFFICE_TABLE.

If you only want to return results with records in OFFICE_TABLE you will want an inner join, e.g.:

SELECT `name`, `key`, ot.name AS OFFICE_NAME, `manager`, `id` 
  FROM `ASSOCIATION_TABLE` at
       INNER JOIN OFFICE_TABLE ot
       ON ot.id = at.office
 WHERE `association`.`customer`=4;

Answer 2

In addition to what @Adam said, you can have a look at the official MySQL documentation

I would also suggest that you look on google for a good SQL tutorial.

Answer 3

This is a great site for giving examples of the different types of joins. Most likely you will want to use a left outer join.

http://www.w3schools.com/sql/sql_join.asp

Answer 4

SELECT o.`name`, `key`, `office`, `manager`, `id` 
FROM `database`.`ASSOCIATION_TABLE`
JOIN `database`.`OFFICE_TABLE` o
USING (id)
WHERE `association`.`customer`=4;