----- Original Message ----- From: "Victoria Reznichenko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, 14 March, 2002 12:48 Subject: a quick sanity check...
: denonymous, : Wednesday, March 13, 2002, 9:06:48 PM, you wrote: : : d> Hi all -- I just need a quick sanity check on a query. : : : d> I have two tables: : : d> CREATE TABLE users ( : d> userID VARCHAR(10) NOT NULL PRIMARY KEY, : d> [many other fields] : d> ) : : d> CREATE TABLE subscriptions ( : d> userID VARCHAR(10) NOT NULL, : d> listID INT NOT NULL : d> ) : : : d> In these tables, users.userID == subscriptions.userID. : d> The users table keeps 1 record per userID, and the subscriptions table keep : d> multiple records per userID (one for each listID to which they're : d> subscribed). : : d> If I wanted to select all users from the users table that *do not* appear in : d> the subscriptions table, I'd want the following query: : : d> SELECT users.* : d> FROM users : d> LEFT JOIN subscriptions : d> ON users.userID=subscriptions.userID : d> WHERE subscriptions.userID IS NULL subscriptions.userID will never be NULL (the field is created that way). maybe a good idea is to let it be NULL so you can tell when there is no coresponding record in the users table. : : d> Correct? : : Looks like it is correct. You can check your query result on any test : database. : : d> I know that this is a pretty base example, but I'd like to confirm it : d> (outside of an example in a book) with someone a bit more knowledgable than : d> me. There are a couple other reports here that calculate this number in : d> other ways, and I'd like to be able to positively state that this is the : d> be-all end-all for the needed data. : : d> Much thanks! : : : : : -- : For technical support contracts, goto https://order.mysql.com/ : This email is sponsored by Ensita.net http://www.ensita.net/ : __ ___ ___ ____ __ : / |/ /_ __/ __/ __ \/ / Victoria Reznichenko : / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] : /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net : <___/ www.mysql.com : : : : : --------------------------------------------------------------------- : Before posting, please check: : http://www.mysql.com/manual.php (the manual) : http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail <[EMAIL PROTECTED]> : To unsubscribe, e-mail <[EMAIL PROTECTED]> : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
