Basic SQL join question. Can you help me improve my skillset?

Question

Ok.. So I'm trying to improve my SQL skills and have a question. Here is a screen shot of the schema.

(http://img509.imageshack.us/img509/97/screenhunter02nov121946.gif)

Alright so I'm selecting a bunch of Report Bundles and other rows from a table you can see. I've got these two tables joining together correctly and displaying what should be returned. Now I need to add another field onto my result rows that states what type of report this is. How can I join up to the ReportGroupType table through the ReportBundleGroup table without getting a shwack of results?

Here is the query I am using so far.

SELECT * FROM ReportBundleCustomerVisibility INNER JOIN ReportBundle ON ReportBundleCustomerVisibility.ReportBundleID = ReportBundle.ID WHERE ReportBundleCustomerVisibility.ReferenceCustomerID = 2303

Thanks again SO

Answer 1

It sounds like you just need another inner join to get the information you need. You can think about the second join as joining the result of the join with the ReportGroupType table. I added parenthesis to try to join the two sets the second INNER JOIN is operating on.

SELECT * FROM ((ReportBundleCustomerVisibility 
    INNER JOIN ReportBundle ON ReportBundleCustomerVisibility.ReportBundleID = ReportBundle.ID) 
    INNER JOIN ReportGroupType ON ReportBundleGroup.ReportGroupTypeID = ReportGroupType.ID)
    WHERE ReportBundleCustomerVisibility.ReferenceCustomerID = 2303

I also highly suggest against using "SELECT *" in production code or any query you plan on reusing as the table schema can change and possibly effect reports and UI. Explicitly specify the columns instead.

Answer 2

SELECT * 
  FROM ReportBundleCustomerVisibility AS v
    JOIN ReportBundle AS b ON b.ID = v.ReportBundleID
    JOIN ReportBundleGroup AS g ON b.ID = g.ReportBundleID
    JOIN ReportGroupTYpe AS t ON t.ID = g.ReportGroupTypeID
WHERE v.ReferenceCustomerID = 2303