Knowledge Base

Ask A Question

Questions

4
ANSWERED

Nexpose: SQL Query Export: Convenient Technique to Join Site Name to Assets [jaldridge]

One idiom that I've been having to use over and over again involves showing assets with their site names. But, this involves joining three tables, and I'd like to share a notational technique to make this more manageable. Note that I've been meaning to post a rant that the intermediate table, `dim_site_asset`, did not need to be a part of the design (as such tables are usually only justified when a many-to-many relationship, which this is not). However, as much time as I've been pondering the issue, I've found a notational convenience that alleviates some of my frustration. Having learned my early SQL skills on old systems and primitive embedded systems, I have been conditioned over the years to do everything the hard way, with lots of repeated/redundant SQL code, And since it's become apparent that modern SQL has cured much of that old coding malaise, I've been putting myself through a crash course to make the most of modern SQL features permitted through the NeXpose reporting interface. So, if you're new to SQL or if you're suffering from lots of ancient SQL habits, you might find this useful and time saving. The key to the trick is in how `SELECT *` works in a join. The following simply adds the column `site_id` to the table `dim_asset`: ```sql SELECT * FROM dim_asset JOIN dim_site_asset USING (asset_id) ``` This can then be used and re-used as defined sub-query in the 'WITH' block. Add to this a narrowed definition of the table dim_site, and you can use the same trick again to add in only the site name. ```sql WITH dim_asset_site AS ( SELECT * FROM dim_asset JOIN dim_site_asset USING (asset_id) ), dim_site_name AS ( SELECT site_id, name AS site_name FROM dim_site ) SELECT * FROM dim_asset_site JOIN dim_site_name USING (site_id) ``` To go ahead and make the whole thing reusable: ```sql WITH dim_asset_site AS ( SELECT * FROM dim_asset JOIN dim_site_asset USING (asset_id) ), dim_site_name AS ( SELECT site_id, name AS site_name FROM dim_site ), dim_asset_site_name AS ( SELECT * FROM dim_asset_site JOIN dim_site_name USING (site_id) ) SELECT * FROM dim_asset_site_name ``` For the number of times that I've joined site names to assets, this is certainly a technique I would like to have known about from the start, but I'm very happy to have learned that modern SQL has eliminated some of the nonsense that had existed way back in ancient history.

Posted by Edward Sheehy about a year ago