These table names are extraordinarily confusing; especially since your schema is de-normalized. One of these tables should have (user_id int unsigned not null auto increment primary key, username varchar(100) ); All the rest should be using user_id.
Anyway, to answer your first question: select * from Table_1 left join Table_2 using (photo_uid) where Table_1.username != 'dopey' and Table_2!='dopey'; You need to move the conditions on Table_2 into the join clause: select * from Table_1 left join Table_2 where Table_1.photo_id=Table_2.photoid AND Table_2.username != 'dopey' where Table_1.username != 'dopey'; Regards, Gavin Towey -----Original Message----- From: Ashley M. Kirchner [mailto:kira...@gmail.com] Sent: Sunday, November 15, 2009 4:38 AM To: mysql@lists.mysql.com Subject: Selecting data from multiple tables 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 The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
