List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:November 18 2009 7:50pm
Subject:Re: Selecting data from multiple tables
View as plain text  
Ashley M. Kirchner wrote:
> Hi folks,
> 
> I'm trying to, possibly do the impossible here.  I have to select data from
> 4 different tables to come up with the right information and I'm having one
> heck of time trying to figure it out.  This is going to be a long email ...
> 
> Table_1:
> +-----------+--------------------------+------+-----+---------+-------+
> | Field     | Type                     | Null | Key | Default | Extra |
> +-----------+--------------------------+------+-----+---------+-------+
> | photo_uid | int(7) unsigned zerofill | NO   | PRI | NULL    |       |
> | username  | varchar(100)             | NO   |     | NULL    |       |
> | votes     | int(5)                   | YES  |     | 0       |       |
> +-----------+--------------------------+------+-----+---------+-------+
> 
> Table_2:
> +-----------+--------------------------+------+-----+---------+-------+
> | Field     | Type                     | Null | Key | Default | Extra |
> +-----------+--------------------------+------+-----+---------+-------+
> | photo_uid | int(7) unsigned zerofill | NO   | UNI | NULL    |       |
> | username  | varchar(100)             | NO   | PRI | NULL    |       |
> | vote      | int(2)                   | NO   |     | 0       |       |
> | voted_on  | datetime                 | NO   |     | NULL    |       |
> +-----------+--------------------------+------+-----+---------+-------+
> 
> Table_3:
> +------------+---------------------+------+-----+---------+----------------+
> | Field      | Type                | Null | Key | Default | Extra          |
> +------------+---------------------+------+-----+---------+----------------+
> | username   | varchar(100)        | NO   | UNI | NULL    |                |
> | info       | varchar(100)        | NO   |     | NULL    |                |
> +------------+---------------------+------+-----+---------+----------------+
> 
> Table_4:
> +-----------+--------------------------+------+-----+---------+----------------+
> | Field     | Type                     | Null | Key | Default |
> Extra          |
> +-----------+--------------------------+------+-----+---------+----------------+
> | photo_uid | int(7) unsigned zerofill | NO   | PRI | NULL    |
> auto_increment |
> | username  | varchar(100)             | NO   |     | NULL
> |                |
> | photo     | varchar(100)             | NO   |     | NULL
> |                |
> +-----------+--------------------------+------+-----+---------+----------------+
> 
> Data used for query:
> username=foo
> 
> 
> The goal here is several.
>   1. query Table 3 for info where username=foo  (always 1 record)
> 
>   2. query Table 3 for username where info = (result of Q1 above) EXCLUDING
> username=foo
>      (results in 0 to many records)
> 
>   3. query Table 1 for photo_uid where username= (all records in query from
> Q2 above)
> 
>   4. query Table 4 for photo_uid from Q2 above and EXCLUDING username=foo
> 
> 
> Now, I started fiddling with LEFT JOIN and came up with this:
> 
> select * from Table_1 left join Table_2 using (photo_uid) where
> Table_1.username != 'dopey';
> +-----------+----------+-------+----------+------+---------------------+
> | photo_uid | username | votes | username | vote | voted_on            |
> +-----------+----------+-------+----------+------+---------------------+
> |   0000011 | bashful  |     0 | NULL     | NULL | NULL                |
> |   0000010 | bashful  |     0 | NULL     | NULL | NULL                |
> |   0000005 | bashful  |     0 | dopey    |    1 | 2009-11-15 03:56:30 |
> |   0000003 | bashful  |     0 | NULL     | NULL | NULL                |
> |   0000001 | bashful  |     0 | NULL     | NULL | NULL                |
> |   0000014 | grumpy   |     0 | bashful  |    1 | 2009-11-15 03:48:55 |
> +-----------+----------+-------+----------+------+---------------------+
> 
> Close, I need to also set Table_2.username != 'dopey', however the moment I
> do that, I get exactly 1 record returned:
> 
> +-----------+----------+-------+----------+------+---------------------+
> | photo_uid | username | votes | username | vote | voted_on            |
> +-----------+----------+-------+----------+------+---------------------+
> |   0000014 | grumpy   |     0 | bashful  |    1 | 2009-11-15 03:48:55 |
> +-----------+----------+-------+----------+------+---------------------+
> 
> Not the result I was expecting.  I need to keep those that say NULL in the
> username as well.
> 
> 
> After that, I'm stuck trying to figure out how to join the other Tables to
> get the data I need.
> 
> Suggestions?  I really don't want to break this into several foor loops in
> PHP, at least not if I can somehow do it all within MySQL.
> 

What I don't think you understand yet is that for any rows in the 
"right" table that fail to match the ON clause of a LEFT JOIN, you will 
get the value NULL in every column. This is a very useful property for 
detecting matches and non-matches.

Try this query instead:

select *
from Table_1
left join Table_2
   ON Table_1.photo_uid = Table_2.photo_uid
   AND Table_2.username = 'dopey'
where Table_1.username != 'dopey'
   AND Table_2.photo_uid IS NULL;

That will list all photos from table1 not taken by dopey that match the 
photo_uid from table2 also not taken by dopey

The other thing about LEFT JOINs is that as soon as you attempt to 
compare against something from the "right" table (other than using "is 
NULL") in the WHERE clause you force a non-null value to exist in that 
table. That means that all of those missing rows (the non-matches) just 
got tossed out of your potential results.

This general pattern

SELECT ...
FROM A
LEFT JOIN B
   ON A.id = B.id
   AND B.column = value
WHERE B.(any normally non-null column) is NULL

is the generic way to say "Show me all rows from B that match A but 
don't have this value in them".

-- 
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN


Thread
Selecting data from multiple tablesAshley M. Kirchner15 Nov
  • RE: Selecting data from multiple tablesGavin Towey16 Nov
  • Re: Selecting data from multiple tablesShawn Green18 Nov
    • host variables Charles Brown18 Nov