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.
Thanks!
Ashley
