So I am trying to join these tables and my SQL query does not seem to work. Was wondering if I could get some help or guidance on making the correct JOIN.
SELECT da.asset_id, da.ip_address, favf.date, favi.proof, dv.title FROM dim_asset da JOIN fact_asset_vulnerability_finding favf USING (asset_id) JOIN fact_asset_vulnerability_instance favi USING (asset_id) JOIN dim_vulnerability dv USING (vulnerability_id)
Because vulnerability_id appears in more than one of the tables used, you cannot use the USING keyword for the join. Instead, you will need to use the ON keyword, which has a syntax like this:
JOIN dim_vulnerability dv ON dv.vulnerability_id = favf.vulnerability_id
Thank you so much for your help! I had one more question if I wanted to print out dim_tag(name), so all of the tags applied to an asset, and an asset ip_address in the data warehouse. I keep getting an ip_address with only one tag but I would like all of the tags on the same line separated with a ‘|’. A query like this would suffice.
“SELECT DISTINCT da.ip_address, dt.name FROM dim_asset da JOIN dim_asset_tag USING (asset_id) JOIN dim_tag dt USING (tag_id) ORDER BY ip_address”