3 table joins one field from one table and one field from another?
From your other questions, this looks to be what you are after. Please start using shorter names (hopefully still meaningful) for aliases.
Related Questions
Query with multiple joins will only return one of my left joins?
Indexing for quick JOINs between a regularly truncated table and a large table in MySQL?
Sql joins - Joining more than one table?
AWK/BASH: how to match a field in one file from a field in another?
Sql Server Indexing on a table with no joins and predictable query?
There are three field ensembles out of the fifty ATSC field ensembles that have no video. How should the performance of the CECBs be determined for these field ensembles?