Network Engineer -- A Looking Glass
A look into my work as a Network Engineer
Monday, August 04, 2008
MySQL Double Joins
devices table
device_id (PK) | device_name
site_contacts table
device_id (FK) | contact_name | contact_email
site_address table
device_id (FK) | address
Select specific info from 2 tables without having to pull all info from all 3 tables:
################################################
SELECT contact_name, contact_email, address
FROM site_contacts INNER JOIN devices
ON site_contacts.device_id = devices.device_id
INNER JOIN site_address
ON site_address.device_id = devices.device_id
WHERE devices.device_name = 'fake_router.mrn';
################################################
LEFT (aka OUTER) JOIN:
Returns all records from the "LEFT" table (the table listed after the "FROM" clause) even the records that have no matching row in the "RIGHT" table (the table listed after the "LEFT JOIN" clause).
LEFT JOIN NOTES:
If both tables in a left join have the same column name, you can simplify your query with the following:
################################################
SELECT *
FROM devices LEFT JOIN site_address
USING (device_id);
################################################
*Note: In this case, all records from the 'devices' table would be returned even if there were no corresponding record in the 'site_address' table.
INNER (aka CROSS) JOIN:
Only returns matching row records that exist in 2 or more tables
################################################
SELECT device_name, address
FROM devices, site_address
WHERE devices.device_id = site_address.device_id;
################################################
*Note: in this case, only records from the 'devices' table that have matching records in the 'site_address' table will be returned (or vice-versa). The order of the tables listed in your query will not matter (because you are performing an INNER JOIN).
Newer Posts
Older Posts
Home
Subscribe to:
Posts (Atom)