Q:

join tables based on if the ID = 2

I have 3 tables SP_COMMENTS, SP_STEPS, SP_TRANSACTIONS
SP_COMMENTS stores comments made about steps that are stored within SP_STEPS
Since a comment can relate to multiple steps, I have the SP_TRANSACTIONS table that keeps track of all those.
This is how they are related:
SP_COMMENTS.SP_COMMENTS_ID = SP_TRANSACTIONS.SP_TRANSACTIONS_COMMENT_ID

SP_STEPS.SP_STEPS_ID = SP_TRANSACTIONS.SP_TRANSACTIONS_STEP_ID

What I want to do is display all the comments WHERE SP_STEPS.SP_STEPS_ID = 2
I am horrible at determining what kind of join this should be and would appreciate the help - thank you.

A:

SELECT columns
FROM SP_COMMENTS c
INNER JOIN SP_TRANSACTIONS t ON t.SP_TRANSACTIONS_COMMENT_ID=c.SP_COMMENTS_ID
WHERE t.SP_TRANSACTIONS_STEP_ID=2

Additionally, I have a few notes on your naming conventions:

Prefacing table names with "SP" is bad, because in the database world the SP abbreviation has another (more significant) meaning: stored procedure.  By using sp as a table prefix, you're really gonna confuse future maintainers of your application.
Your transactions table is also poorly named.  This table is an intersection  between your Comments and Steps tables.  It is not a transaction, which, again, means something else entirely in a database context.  

