CODEWITHSUNDEEP
sqlpostgresql
Ezy
Ezy

Reputation: 49

Joining 3 tables -SQL

I have three tables:

entry:

                   _________________________
                  |memberid | tourid | year |
                  --------------------------
                  |  118    |  24    | 2011 |
                  |  224    |  40    | 2012 |
                  ___________________________

member:

   ______________________________________________________________________________________________
  | memid | lname | fname   | memtype | phone | handicap | joindate | memteam | gender | lifemem |
  -----------------------------------------------------------------------------------------------
  | 118   | James | Melissa | Junior  | 50154 |   30     | 2004-5-5 |  TeamB  |  F     |   0     |  
  | 138   | Allset| Micheal | Senior  | 45215 |   30     | 2008-4-2 |  TeamC  |  M     |   1     |
   _______________________________________________________________________________________________

tournament:

   _______________________________________
  |tiourid | tourname | tourtype | active | 
  ----------------------------------------  
  | 24     |  London  | social   |   y    |
  | 40     |  Leeds   | open     |   n    |
   _______________________________________

I am trying to achieve-> Member id and last name for every member who has ever entered tournament 40

This is my code:

        SELECT entry.memberid, member.lastname, entry.year from entry join member on entry.MemberId 
         = member.MemberId  where tourid = 40;

I am not sure the above code is right, would some one please help me.

Thank you.

Upvotes: 0

Views: 30

Answers (1)

Popeye
Popeye

Reputation: 35900

Your code seems correct. Just use DISTINCT to prevent duplicate member details in case any member participated in tournament id 40 multiple times in the same year.

SELECT distinct entry.memberid,
       member.lastname, 
       entry.year 
  from entry join member on entry.MemberId = member.MemberId  
 where entry.tourid = 40;

Upvotes: 1

Related Questions