List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 12 1999 4:55am
Subject:Re: Funky inner join problems
View as plain text  
On Wed, 1999-08-11 14:53:20 -0500, Bryan Porter wrote:
> When I do a "SELECT skid_num FROM tblSystems WHERE skid_num = '25';"
> I am given 2 rows.
> 
> When I execute the following inner join, again asking for only rows
> where the skid_num = 25, I get 770 rows.
> 
> Here's the second SQL I execute ...
[slightly reformatted:]
| 
| SELECT
|        s.skid_num
|      , m.manufacturer
|      , m1.modem_name
|      , c1.cdrom_name
|      , s1.sndcrd_name
| FROM
|        tblSystems      s
|      , tblManufacturer m
|      , tblModems       m1
|      , tblCdrom        c1
|      , tblSoundCards   s1
| WHERE
|      (   m.manufacturer_id = s.manufacturer_id
|       OR m1.modem_id       = s.modem_id
|       OR c1.cdrom_id       = s.cdrom_id
|       OR s1.soundcard_id   = s.soundcard_id
|      )
|   AND s.skid_num = '25';

Think about it!  If for example the first of this four OR terms is
true (m.manufacturer_id = s.manufacturer_id), then all of m1, c1, and
s1 may have any values - and you'll get one result record for every
combination of these values.

Look at this smaller example:

   Table t1"                Table "t2"               Table "t3"
+------+------+          +------+------+          +------+------+         
| id   | x    |          | id   | y    |          | id   | z    |         
+------+------+          +------+------+          +------+------+         
|    0 | a    |          |    0 | i    |          |    1 | u    |         
|    1 | b    |          |    1 | j    |          |    2 | v    |         
|    2 | c    |          +------+------+          +------+------+         
+------+------+                                                           

Now this query:

    SELECT *
    FROM t1,t2,t3
    WHERE ( t1.id=t2.id OR t1.id=t3.id )
    ORDER BY t1.id, t2.id, t3.id;
    +------+------+------+------+------+------+
    | id   | x    | id   | y    | id   | z    |
    +------+------+------+------+------+------+
    |    0 | a    |    0 | i    |    1 | u    |
    |    0 | a    |    0 | i    |    2 | v    |
    |    1 | b    |    0 | i    |    1 | u    |
    |    1 | b    |    1 | j    |    1 | u    |
    |    1 | b    |    1 | j    |    2 | v    |
    |    2 | c    |    0 | i    |    2 | v    |
    |    2 | c    |    1 | j    |    2 | v    |
    +------+------+------+------+------+------+

If I want a single result record for every id, which has all the
information of all three table combined, the first (wrong) attempt is:

    SELECT *
    FROM t1,t2,t3
    WHERE t1.id=t2.id AND t1.id=t3.id
    ORDER BY t1.id, t2.id, t3.id;
    +------+------+------+------+------+------+
    | id   | x    | id   | y    | id   | z    |
    +------+------+------+------+------+------+
    |    1 | b    |    1 | j    |    1 | u    |
    +------+------+------+------+------+------+

    The ids 0 and 2 are missing!  That because there's no counterpart
    of id=0 in t3 and no counterpart of id=2 in t2.

That's where LEFT JOIN comes in!  The first (left) table in a LEFT
JOIN is the main table, and if there's no matching record in the
second table simply a record with NULL values is used.

    SELECT *
    FROM   t1 LEFT JOIN t2  ON t1.id=t2.id
              LEFT JOIN t3  ON t1.id=t3.id
    ORDER BY t1.id, t2.id, t3.id;
    +------+------+------+------+------+------+
    | id   | x    | id   | y    | id   | z    |
    +------+------+------+------+------+------+
    |    0 | a    |    0 | i    | NULL | NULL |
    |    1 | b    |    1 | j    |    1 | u    |
    |    2 | c    | NULL | NULL |    2 | v    |
    +------+------+------+------+------+------+

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Funky inner join problemsBryan Porter11 Aug
  • Re: Funky inner join problemsMartin Ramsch12 Aug