[thelist] MySQL table joins

[rudy]

hi adrian

one of these days i simply must start using mysql, it looks like fun

i think i actually understood the weird "do-unless" structure


> I think I have the right syntax for the table references before the
> WHERE statement but it all turns bad after that.

yeah, i hate it when that happens

>      SELECT Email
>          FROM tblDockets
>                    , tblEmails
>                    , tblAds,
>                    , tblAdvise
>      WHERE Email='". sqlEncode($form{'email'}) . "'
>               OR '".sqlEncode($form{'email2'}) . "'");

you haven't provided the "join conditions" which connect only the
appropriate rows of the tables being joined

you can recognize a join condition because it operates on the columns of
two different tables

a typical three-way join would look something like this

    select a.stuff, b.stuff, c.stuff
       from a,b,c
     where a.key = b.key and b.key = c.key
         and a.foo = 'bar'

in addition to the join conditions, here you can also see a "local
predicate" which is a condition that operates on only one table

if the above example three-way join were written in sql-92 syntax, it would
be

    select a.stuff, b.stuff, c.stuff
       from a join b on a.key = b.key join c on b.key = c.key
       where a.foo = 'bar'

notice that the local predicates are all that remain in the WHERE clause


in your query, you have a local predicate on the Email column, but since
your join conditions are missing, you are getting the equivalent of a CROSS
JOIN (see Ben Dyer's tip yesterday)

since a cross join brings back a, um, boatload of records, the "unless"
part of your structure (if it works the way i assume it does, remember, i
don't use mysql) is preventing your "do" part from, um, doing


helps?


rudy