开发者

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.
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜