reset password
Author Message
msargent
Posts: 519
Posted 14:56 Feb 27, 2013 |

Would this be the right syntax for a single inner join with 3 conditions?

 

-- Find the number of projects led by John Doe.
 
select count(*) from projects p inner join employees e
on e.first_name = "John"
where e.last_name = "Doe"
and p.leader_id = e.id;
Last edited by msargent at 15:07 Feb 27, 2013.
msargent
Posts: 519
Posted 15:08 Feb 27, 2013 |

Never mind, I found an answer in the exercises file online:

 

select count(*) from projects p
    inner join project_members m on p.id = m.project_id
    inner join employees e on m.member_id = e.id
    where e.first_name = 'John' and e.last_name = 'Doe';

The second inner join has 3 conditions. 

cysun
Posts: 2935
Posted 15:10 Feb 27, 2013 |

It should be

select count(*) from projects p inner join employees e
on p.leader_id = e.id
where e.first_name = 'John' and e.last_name = 'Doe';

The matching columns of the two tables (i.e. the join columns) follow "ON", and the other conditions are in "WHERE".

cysun
Posts: 2935
Posted 15:12 Feb 27, 2013 |
msargent wrote:

Never mind, I found an answer in the exercises file online:

 

select count(*) from projects p
    inner join project_members m on p.id = m.project_id
    inner join employees e on m.member_id = e.id
    where e.first_name = 'John' and e.last_name = 'Doe';

The second inner join has 3 conditions. 

That example shows a 3-table join so there are two "inner join" and two "on", but the rules are the same.