Please can someone explain INNER JOIN ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Please can someone explain INNER JOIN ?

Post by Rippie »

Hi all, just wondered if someone could please explain the INNER JOIN mysql query.

$query = mysql_query("SELECT nameid, productname
FROM people pe
JOIN skills s ON pe.nameid = s.nameid
JOIN products pr ON s.productid = pr.productid
WHERE pr.productname = '$produkt'");

This code is what a friend gave me in order to link 3 tables together. I have people, skills and products tables. the people table contain name and id, product table contain product name and id and the skills table just link people id to products id (many-to-many)

But am i interpreting this right ?

We can select * (everything from the 3 tables) or just some ?
pe, s and pr are just shorten for the different tables ?
and then once we get to the ON bit, then we link tables together ?

If im on the right track here, then i guess i dont need much more information, but just thought i would ask someone with more knowledge than me. also if you can come up with some more examples that would be GREAT !!!

Cheers everyone !
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Please can someone explain INNER JOIN ?

Post by requinix »

Maybe a SQL tutorial would be better for you...

Code: Select all

SELECT nameid, productname
FROM people pe
JOIN skills s ON pe.nameid = s.nameid
JOIN products pr ON s.productid = pr.productid
WHERE pr.productname = '$produkt'
There are three tables: people, skills, and products. The first table is people: skills is added on to it on rows where people's nameid = skills's nameid, then products is added where products's productid matches skills's productid. Of all that, only the nameid and productname fields are returned from only the rows where the productname is $produkt.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Please can someone explain INNER JOIN ?

Post by pickle »

Moved to Databases.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Brian Swan
Forum Newbie
Posts: 17
Joined: Thu Jan 14, 2010 11:56 am

Re: Please can someone explain INNER JOIN ?

Post by Brian Swan »

This topic on Wikipedia provides a good overview of INNER JOIN: http://en.wikipedia.org/wiki/Inner_join#Inner_join

This topic on the MySQL docs provides good examples of INNER JOINs: http://dev.mysql.com/doc/refman/5.0/en/join.html

Hope those help.

-Brian
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Please can someone explain INNER JOIN ?

Post by Rippie »

Cheers !! thank you alot.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Please can someone explain INNER JOIN ?

Post by josh »

you have 2 table in a join statement


left join - lists rows in left table and any corresponding rows from right table
select from `left_table` LEFT join `right_table` on *.

select from `left_table` RIGHT join `right_table` on *.
right - lists rows in right that have corresponding rows in left

select from `left_table` INNER join `right_table` on *.
inner - lists only rows that match up in both tables


Simple. Dont overcomplicate it.
Post Reply