mySQL query with field replacemnt?

Question

I'm need to run a query from mySQL 5.6 which involves looking up date from different tables. I'm new to mySQL and I'm not sure what this operation is called, or else I would have just googled the subject... I'm hoping to get someone to point me in the right direction... Please note, I can't change the structure of the tables, as this is pre-populated from the application I am using.

example:

3 tables in the same local database: network_segments, Buildings, Departments

network_segments description:

network_segment_ID, display_name, starting_ip, ending_ip, building, department
1, 10.0.0.1, 10.0.0.254, 1, 2
2, 10.1.0.1, 10.1.0.254, 2, 3
3, 10.4.0.1, 10.4.0.254, 3, 1

building table description:

building_ID, description
1, ACHQ
2, EHQ
3, AHQ

departments table description:

department_ID, description
1, marketing
2, sales
3, hr

I want to "select * from network_segments" but in stead of the building and department IDs coming back, I'd like the description field for those IDs:

1, 10.0.0.1, 10.0.0.254, ACHQ, sales
2, 10.1.0.1, 10.1.0.254, EHQ, hr
3, 10.4.0.1, 10.4.0.254, AHQ, marketing

any advise would be appreciated...

Thank you!

• Colin 't Hart: Did you try anything yourself?
• Colin 't Hart: Hint: Try joining.

Answer 1

select network_segment_ID, display_name, starting_ip, ending_ip, bld.description, dept.description
from network_segments
join buildings bld on (building = building_ID)
join departments dept on (department = department_ID);

• Hannah Vernon: Code-only answers are not generally all that helpful. Perhaps you could give some indication of why/how that answers the question?
• Colin 't Hart: Yeah, I was a bit at a loss of what to say for such a fundamental question.

Answer 2

Thank you for the help Colin 't Hart, and for the join examples. I couldn't get your exact solution to work, but I got a less elegant version going for my application DB reporting needs.

Below is working code that will combine all of the lookup tables while only displaying the data columns I need. It's not the inline replacement I was hoping for, but I also didn't know what to ask for when I posted earlier. I've added a union to make the final csv output have headers. This was for the Casper application server by JAMF Software.

Thank you!

(select 'Subnet Name','Starting Address','Ending Address','Distribution Point','Software Update Server','Building Name','Department Name')
union
(select n.display_name, n.starting_address, n.ending_address, dps.server_address, s.server_address, b.building_name, d.department_name
from network_segments as n inner join buildings as b
on n.building_id = b.building_id
inner join departments as d
on n.department_id = d.department_id
inner join distribution_points as dps
on n.distribution_point_id = dps.distribution_point_id
inner join software_update_servers as s
on n.software_update_server_id = s.software_update_server_id
INTO OUTFILE 'c:/tmp/network_subnet_report.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n');