Re: 3 Table Join question
Solved it with Union :) SELECT images.id,images.name, playlist.title FROM images,playlist WHERE playlist.image_id = images.id UNION SELECT images.id,images.name, media.title FROM images,media WHERE media.image_id = images.id ORDER BY id ASC On Jun 23, 2006, at 6:44 PM, Graham Anderson wrote: I am trying to build a query to 1) Get all the results from one table, 'images' 2) For each entry in the 'images' table, find the correct title from the 'playlist' OR 'media' table where images.id = which_table.images_id images table id, filename playlist table title images_id media table title, images_id So the result would something like id filenametitle 1 file1 playlist-title1 // id matches entry in the Playlist table 2 file2 playlist-title2 3 file3 media-title1//id matches entry in the Media table 4 file4 media-title2 any help is appreciated as my queries have been pretty simple up to this point many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3 Table Join question
I am trying to build a query to 1) Get all the results from one table, 'images' 2) For each entry in the 'images' table, find the correct title from the 'playlist' OR 'media' table where images.id = which_table.images_id images table id, filename playlist table title images_id media table title, images_id So the result would something like id filenametitle 1 file1 playlist-title1 // id matches entry in the Playlist table 2 file2 playlist-title2 3 file3 media-title1//id matches entry in the Media table 4 file4 media-title2 any help is appreciated as my queries have been pretty simple up to this point many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table join question
I suspect 5.x.x is the same as 4.1.x which is 61 tables - dependent on processor. See an earlier posting of mine on the same subject. What they really need to do is replace the #define with a struct and some 'code' to interpret its contents then you could have any limit you wanted - provided you're prepared to wait whilst it works out the result. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] > -Original Message- > From: Rob Brooks [mailto:[EMAIL PROTECTED] > Sent: 29 March 2005 15:57 > To: 'MySQL list' > Subject: table join question > > Although I didn't find it in the docs anywhere, I know from experience > that > you cannot join more than 31 tables in 4.0.21 > > > > I was wondering if anybody knew if this limit has changed in version 5 > > > > Thx > > Rob ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table join question
Although I didn't find it in the docs anywhere, I know from experience that you cannot join more than 31 tables in 4.0.21 I was wondering if anybody knew if this limit has changed in version 5 Thx Rob
RE: Table join question
show us your table structure show us your table values -Original Message- From: Barry J. Wiegan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 05, 2002 11:00 AM To: [EMAIL PROTECTED] Subject: Table join question Hi, I'm trying to build a query to solve a fairly simple problem. I have two tables (A and B) linked by a common ID. I want to locate a specific record in A that is linked to two or more records in B based on second field in B matching certain criteria. I've tried every type of inner, outer, left, and right join to accomplish this, but can't come up with anything that works. EG: Select * from A inner join B on A.ID=B.ID where B.Val='25' and B.Val='35'; This will rturn zero matching records since B.Val can't equal 25 and 35 at the same time. If I substitute "and" with "or", I will get multiple records from A that are linked to either 25 or 25. I just want the one record that is linked to both 25 AND 35. Thanks for any help. Barry Wiegan Senior Software Engineer The Scout Project http://www.scout.cs.wisc.edu/ - 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 - 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
Table join question
Hi, I'm trying to build a query to solve a fairly simple problem. I have two tables (A and B) linked by a common ID. I want to locate a specific record in A that is linked to two or more records in B based on second field in B matching certain criteria. I've tried every type of inner, outer, left, and right join to accomplish this, but can't come up with anything that works. EG: Select * from A inner join B on A.ID=B.ID where B.Val='25' and B.Val='35'; This will rturn zero matching records since B.Val can't equal 25 and 35 at the same time. If I substitute "and" with "or", I will get multiple records from A that are linked to either 25 or 25. I just want the one record that is linked to both 25 AND 35. Thanks for any help. Barry Wiegan Senior Software Engineer The Scout Project http://www.scout.cs.wisc.edu/ - 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
Re: Table join question
On Tuesday 05 February 2002 9:59 am, Barry J. Wiegan wrote: > Hi, > > I'm trying to build a query to solve a fairly simple problem. > I have two tables (A and B) linked by a common ID. I want to > locate a specific record in A that is linked to two or more > records in B based on second field in B matching certain criteria. > I've tried every type of inner, outer, left, and right join > to accomplish this, but can't come up with anything that works. > > EG: Select * from A inner join B on A.ID=B.ID where B.Val='25' > and B.Val='35'; > > This will rturn zero matching records since B.Val can't > equal 25 and 35 at the same time. SELECT * FROM A inner join B on A.ID = B.ID inner join B AS C on A.ID = C.ID where B.Val = '25' and C.val = '35'. Or something similar. I can't be bothered to open my SQL reference at the moment. :) (For the sake of completeness, it is worth realising that you would _often_ need to ensure that the row in B and C are not the same row... not necessary here, though as you know they aren't as B.val and C.val are different). - 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
Table join question
Hi, I'm trying to build a query to solve a fairly simple problem. I have two tables (A and B) linked by a common ID. I want to locate a specific record in A that is linked to two or more records in B based on second field in B matching certain criteria. I've tried every type of inner, outer, left, and right join to accomplish this, but can't come up with anything that works. EG: Select * from A inner join B on A.ID=B.ID where B.Val='25' and B.Val='35'; This will rturn zero matching records since B.Val can't equal 25 and 35 at the same time. If I substitute "and" with "or", I will get multiple records from A that are linked to either 25 or 25. I just want the one record that is linked to both 25 AND 35. Thanks for any help. Barry Wiegan Senior Software Engineer The Scout Project http://www.scout.cs.wisc.edu/ - 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
