What you need is a LEFT JOIN. When you use a LEFT JOIN, you get all rows from your main table, with either the data from the penpals_privmsgs_block table if there is corresponding data, or NULL if there is no related row. Take a look here: http://www.mysql.com/doc/en/JOIN.html for more information.
Here's a quick rewrite, you fill in the blanks: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID FROM useronline LEFT JOIN penpals_privmsgs_block ON useronline.something = penpals_privmsgs_block.somethingelse, penpals_fav WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = $colname AND penpals.privmsgs_block.something IS NULL Regards, Mike Hillyer www.vbmysql.com > -----Original Message----- > From: vernon [mailto:[EMAIL PROTECTED] > Sent: Monday, June 23, 2003 8:42 AM > To: [EMAIL PROTECTED] > Subject: SQL query - 3 tables - 3rd one conatins records to > not display > > > I have a SQL query that needs to reference three different > tables. I'm > creating an online buddy list of members who are online. I > have all of this > functioning but am trying to also reference another table > where the user is > being blocked, in which case I do not what the user's name to > be shown in > the user's buddy list. I'm using the code below: > > //SET $colname TO USER'S ID SESSION > $colname = $HTTP_SESSION_VARS['svUserID']; > > //SELECT THE TABLES FROM DATABASE > SELECT distinct useronline.uname, penpals_fav.fav_user_id, > penpals_fav.ID > FROM useronline, penpals_fav, penpals_privmsgs_block > > // HERE I CHECK IF THE USER'S NAME IS IN THE FAVORITIES USER TABLE > // AND THE ONLINE USER TABLE ALL OF WHICH WORKS FINE > WHERE penpals_fav.fav_user_name = useronline.uname AND > penpals_fav.user_id = > $colname AND penpals_privmsgs_block.user_id = $colname > > It gets tricky here when I try to select from another table > because this > table will not always have the user's name in it, only if the user is > blocked by another user will there be a record, in which case > we do not want > the user's name to be displayed. I was thinking this, but > then realized that > this would only create a list of those who are being blocked > which not what > I'm trying to do. > > AND penpals_privmsgs_block.blocked_id != colname > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
