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_开发者_如何学CCOMMENTS.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.
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.
精彩评论