Re: Select data that has no related categories...
Victor, Wednesday, May 29, 2002, 12:01:31 AM, you wrote: VSA Tells a great deal about how confused I am... VSA I've a database with three tables, one with image data, one with VSA categories and one table which helps me to have several categories per VSA image... VSA The following SQL gives me _all_ the images that has at least one VSA category associated: VSA SELECT images.filename, images.path, images.desc, categories.name FROM VSA images, relate, categories WHERE public = 1 AND relate.fromid = VSA images.id AND relate.toid = categories.id VSA But what I need help with is getting all the images that has no VSA categorie associated with them in the table relate... Something like that: SELECT images.filename, images.path, images.desc FROM images LEFT JOIN relate ON images.id=relate.fromid WHERE relate.fromid IS NULL; VSA Someone, please... VSA Sincerely, VSA Victor -- For technical support contracts, goto https://order.mysql.com/?ref=ensita 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
Select data that has no related categories...
Tells a great deal about how confused I am... I've a database with three tables, one with image data, one with categories and one table which helps me to have several categories per image... The following SQL gives me _all_ the images that has at least one category associated: SELECT images.filename, images.path, images.desc, categories.name FROM images, relate, categories WHERE public = 1 AND relate.fromid = images.id AND relate.toid = categories.id But what I need help with is getting all the images that has no categorie associated with them in the table relate... Someone, please... Sincerely, Victor - 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
RE: Select data that has no related categories...
SELECT images.filename, images.path, images.desc, categories.name FROM images, relate, categories WHERE public = 1 AND relate.fromid = images.id AND relate.toid = categories.id But what I need help with is getting all the images that has no categorie associated with them in the table relate... Give this a try: Select images.filename,images.path, images.desc From images Left Join relate On (relate.fromid=images.id) Where public And relate.toid = 0 -- The LEFT JOIN will return all rows (w/ public 0) in the `images` table. It will try to match each row in the `images` table to a row or several rows in the `relate` table. Any rows that it cannot find a match for will be filled in with 0s or NULL values. These are the ones that you are interested in. If you wanted to find all of the ones with a relationship, you could just drop off the = 0 part, or do a STRAIGHT JOIN (better). You could use any field from the relate table, actually, since they'll all be 0s or NULL, but I just picked `toid` at random. I don't do queries too often like this, so it's possible I might have omitted something by accident, but I think this should work for you. Feel free to get in touch with me directly and I'll see what I can do for ya. Sincerely, Andrew Moyer PHP Coder Linux/MySQL Admin Jetson Direct Mail Services, Inc. - 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
