CODEWITHSUNDEEP
mysqljoin
user1897618
user1897618

Reputation: 9

MySQL JOIN syntax precedence

I have two tables

members
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| mindex     | smallint(4) | NO   | PRI | NULL    | auto_increment | 
| memberid   | smallint(5) | YES  | MUL | NULL    |                | 
| forenames  | varchar(40) | YES  |     | NULL    |                | 
| surname    | varchar(20) | YES  |     | NULL    |                | 
| nameprefix | varchar(30) | YES  |     | NULL    |                | 
| namesuffix | varchar(50) | YES  |     | NULL    |                | 
| died       | smallint(5) | YES  |     | NULL    |                | 
| notes      | text        | YES  |     | NULL    |                | 
+------------+-------------+------+-----+---------+----------------+

and

memberships;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| mshipindex | smallint(4) | NO   | PRI | NULL    | auto_increment | 
| memberid   | smallint(5) | YES  | MUL | NULL    |                | 
| msid       | smallint(5) | YES  |     | NULL    |                | 
| mstype     | varchar(20) | YES  |     | NULL    |                | 
| msyear     | smallint(5) | YES  |     | NULL    |                | 
| msposition | varchar(15) | YES  |     | NULL    |                | 
+------------+-------------+------+-----+---------+----------------+

I want to search on memberships for a year (in memberships.msyear) and get memberships.mstype and members.surname. I just can't get the right JOIN syntax on this.

Upvotes: 0

Views: 316

Answers (2)

Taryn
Taryn

Reputation: 247690

You will use something like this:

select m.surname,
  s.mstype
from members m
left join memberships s
  on m.memberid = s.memberid
where s.msyear = yourYear

See SQL Fiddle with Demo

I used a LEFT JOIN to return the all members, even those who might not have a membership record. If the member does not have a record in the memberships table, then it will return null.

If you need help learning JOIN syntax, here is a great visual explanation of joins

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

If you just need the syntax Here it is :

Select *
from members m
inner join membership ms on (m.memberid = ms.memberid)
where memberships.msyear = 2012

Upvotes: 0

Related Questions