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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
