need help with JOIN

[AbstractHipHop]

i'm trying to figure out how to use join
from what i've read online you can use it to connect tables that share id or something like
for example
lets say i have 4 tables

appletype - typeid, name
applelist - typeid,infoid
appleinfo - infoid,originid,color
appleorigin - originid, location

this is how they're all connected

appletype.typeid = applelist.typeid
applelist.infoid = appleinfo.infoid
appleinfo.originid = appleorigin.originid

using this i want to SELECT

appletype.name
appleinfo.color
appleorigin.location

it's not the most efficient database setup, but i figured that's the easiest example i could show clearly
this can be done easily with 4 queries
but i'm hoping with the help of JOIN it can be done with 1 or maybe 2 queries

thanx for the help

[Dangermouse]

This is a database question and there is a section dedicated to it, could a mod move this there?

[AbstractHipHop]

you're right...apologies
was in this forum and programming PHP
my brain didn't make the connection sorry

[ToolboxTech]

LEFT JOIN is a very powerful MySQL command because it can cut down query counts exponentially when loading lots of information (no need to use the awful recursive query anymore).

Here's my attempt at your query:

SELECT appletype.name, appleinfo.color, appleorigin.location
FROM `appletype`
LEFT JOIN `applelist` ON appletype.typeid = applelist.typeid
LEFT JOIN `appleinfo` ON applelist.infoid = appleinfo.infoid
LEFT JOIN `appleorigin` ON appleorigin.originid = appleinfo.originid
ORDER BY appletype.name

That should do the trick.

[AbstractHipHop]

thanks, i'm gonna test that out...

[AbstractHipHop]

no success

[r937]

please show a few rows from each table

[AbstractHipHop]

i rewrote it like this
and it worked perfectly

SELECT appletype.name, appleinfo.color, appleorigin.location
FROM appletype,applelist,appleinfo,appleorigin
WHERE (appletype.typeid = applelist.typeid AND applelist.infoid =appleinfo.infoid AND appleorigin.originid =appleinfo.originid )
ORDER BY appletype.name ASC

is this any difference technically than using the JOIN
it pulls the same data in the same format as the JOIN would

[r937]

yes, that's the "table list" syntax, with the join conditions included in the WHERE clause

table list syntax is equivalent to INNER JOIN

silverlime suggested LEFT OUTER JOIN

outer joins would be required whenever you have some rows without matching rows in related tables