To visualize what I think you're asking, I'm using a county layer and a city layer.
The city and county layers both have a county code attribute that we can link these with, but a regular join won't bring in the geometry of the county into the city layer for us. After bringing in the county and city layers into QGIS, we can use the QGIS DB Manager to produce the layer we want without generating a new file (unless we want to). The DB Manager is found in the Database menu.
Expand the Virtual Layers, then expand the QGIS layers to see the layers in your QGIS project. Click on the SQL window button to open the query tab as shown.
I will use the id column from the cities layer, the county name, the city name, and use the geometry from the county layer.
SELECT cities.gid, counties.county_nam, cities.corporatio,counties.geometry
FROM cities, counties
WHERE cities.county_cod=counties.county_cod
ORDER BY cities.county_cod,cities.corporatio
The ORDER BY line is optional, but makes it easier for me.
Click the Execute button. The table window will populate the result.
When satisfied with the result, check the Load as new layer box.
Check the Column with unique values box and use the id column (gid in my case). Check the Geometry column box and select the geometry column form the drop down if necessary.
Add a layer name to the Layer name (prefix) line (I've called mine county_geom here) and then click the Load button. This will add your newly conjured layer to your map. You'll notice a hit in draw performance because it is re-running the query the source layers. If the performance hit is too much, you can save the layer in your prefered format and use that.
As you've probably guessed, the county_geom layer has a county geometry for every city in the associated county. This layer is what we will build the atlas upon. At this point, you're probably seeing where this is going.
In the Layout window, select your new layer as the Coverage layer in the Atlas tab. Next, you'll need to determine a Page name that the city layer can match to. Since in my case the two layers only have the county_code column in common and not the county name I want, I will do a join on the city layer with the county layer to get the county name. And then with that I can use that to only show the city for the page.
For the Atlas page name, I'm using:
"corporatio" || '\n('|| "county_nam" ||' COUNTY)'
And for the Rule-based Symbology on the cities layer, I'm using:
"corporatio" || '\n('|| "counties_county_nam" ||' COUNTY)' = @atlas_pagename
Then I duplicate the county_geom layer and do the same Rule-based symbology trick on it.
Now as we go through each page of the Atlas, it zooms to the county geometry from the county_geom layer, highlights the city on top of the highlighted county.
I hope this is clear enough and about what you were looking for.
