select rows in sql with latest date from 3 tables in each group RRS feed

  • Question

  • User2131089582 posted

    How to make select inner join from 3 tables

    Wednesday, March 18, 2020 4:25 AM

All replies

  • User-719153870 posted

    Hi hocamahdi99,

    What i confuse here is how can i select it from 3 tables,

    This is confusing, you have already select the "Before" from these three tables, all you need to do is to implement the approach you found in the thread on this "Before" output.

    Maybe you can SQL SELECT INTO Statement to select this "Before" into a temp table and then deal with this temp table.

    select mAttempt.Id as Id ,mAttempt.QuizModelId as QuizId, mAttempt.StartedAt as StartsOn, mUser.UserName, 
    mDetail.Score as Score
    from UserQuizAttemptModels mAttempt, AspNetUsers mUser, QuizAttemptDetailModels mDetail
    where mAttempt.UserId=muser.Id and mAttempt.QuizModelId=10053  and mDetail.UserQuizAttemptModelId = mAttempt.Id

    Another thing is, the "QuizId" in "Before" is UserQuizAttemptModels.QuizModelId which i assume is the FK of the PK QuizAttemptDetailModels.Id, the QuizAttemptDetailModels.Id value shall be unique since it's PK so the QuizAttemptDetailModels.Id and QuizAttemptDetailModels.Score should be one to one relationship. While in the "Before", The "QuizId" and "Score" is one to many relationshio which conflicts with my assumptions.

    Thus, to avoid redundant guessing, please provide more complete information about these three tables including sample data and anything else necessary to reproduce the situation.

    Also, a further description about "select it from 3 tables" could be very helpful. Thanks!

    Best Regard,

    Yang Shen

    Wednesday, March 18, 2020 7:31 AM