RE: Join syntax problem
As Tom Worster said, print($query); would show you what the query was trying to run. Without testing it, you also have some other whitespace issues between the hw.wildlife and FROM, and also, i m unsure of the asterix infront of the *images. On another note, when I do my JOINs, I tend to write ON (table1.field=jointable.field) rather than just ON (field). And on a final thought, the where cause, seems to be the join clause as well, so isn't that redundant? (or is would that only be in the way that i said i do my joins?) ++ | Steven Staples | ++ | I may be wrong, but at least I tried...| ++ -Original Message- From: Gary [mailto:g...@paulgdesigns.com] Sent: April 26, 2010 10:29 PM To: mysql@lists.mysql.com Subject: Re: Join syntax problem Thanks for the replies. It was my understanding that whitespace is ignored, and I did not think that not having space, in particular with . would result in an error message. Gary Gary gp...@paulgdesigns.com wrote in message news:20100426233621.10789.qm...@lists.mysql.com... I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5063 (20100426) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date: 04/26/10 02:31:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join syntax problem
I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join syntax problem
On 4/26/10 7:36 PM, Gary gp...@paulgdesigns.com wrote: I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . looks like there's no space between 'im' and 'JOIN' in the line above WHERE ky.image_id = im.image_id; try: print($query); Gets me this error message. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join syntax problem
I am reading this on a tiny screen but it looks like you need whitespace before the where. On 4/26/10, Gary gp...@paulgdesigns.com wrote: I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=wult...@gmail.com -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join syntax problem
Thanks for the replies. It was my understanding that whitespace is ignored, and I did not think that not having space, in particular with . would result in an error message. Gary Gary gp...@paulgdesigns.com wrote in message news:20100426233621.10789.qm...@lists.mysql.com... I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5063 (20100426) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem With Join Syntax
Keith, I agree that would be a good option to change the table. Unfortunately, that's not an option at this point. I don't control the schema. Thanks for the suggestion anyway. Albert Padley On Jun 14, 2006, at 3:59 PM, Keith Roberts wrote: Hi Chris. I cannot see how it can be done with the current table schema. Maybe you need to redeclare your table so the values in the value column are more distinct? What is value supposed to contain anyway? First name, last name and email address? What about a structure like: id | userid | ipf_1 | ipf_2 | ipf_3 1 2 JohnSmith email_addy Which will allow you to retrieve all the values you want from the table as one row without having to repeat the userid column? HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. On Wed, 14 Jun 2006, Chris White wrote: To: mysql@lists.mysql.com From: Chris White [EMAIL PROTECTED] Subject: Re: Problem With Join Syntax On Wednesday 14 June 2006 10:55 am, Albert Padley wrote: A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. This sounds like somewhat of a strange requirement. Why do they need to be in a single row? There MIGHT be a way to do it with stored procedures, I'm just not sure how.. Thanks. Albert Padley -- Chris White PHP Programmer/DB Fighter Interfuel -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem With Join Syntax
I have the following table schema in MySQL 4.1.18 which I didn't create, but have to work with. CREATE TABLE `phplog_userinput` ( `id` int(11) NOT NULL auto_increment, `inputfieldid` int(11) NOT NULL default '0', `userid` int(11) NOT NULL default '0', `value` varchar(150) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With Join Syntax
On Wednesday 14 June 2006 10:55 am, Albert Padley wrote: A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. This sounds like somewhat of a strange requirement. Why do they need to be in a single row? There MIGHT be a way to do it with stored procedures, I'm just not sure how.. Thanks. Albert Padley -- Chris White PHP Programmer/DB Fighter Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With Join Syntax
Albert, MySQL's GROUP_CONCAT function might work for you: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html In your case something like this: SELECT userid, GROUP_CONCAT(value) GROUP BY userid HTH, Dan Albert Padley wrote: I have the following table schema in MySQL 4.1.18 which I didn't create, but have to work with. CREATE TABLE `phplog_userinput` ( `id` int(11) NOT NULL auto_increment, `inputfieldid` int(11) NOT NULL default '0', `userid` int(11) NOT NULL default '0', `value` varchar(150) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. Thanks. Albert Padley --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]
Re: Problem With Join Syntax
Dan, Thanks. I'll take a further look at GROUP_CONCAT. Albert On Jun 14, 2006, at 1:16 PM, Dan Buettner wrote: Albert, MySQL's GROUP_CONCAT function might work for you: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html In your case something like this: SELECT userid, GROUP_CONCAT(value) GROUP BY userid HTH, Dan Albert Padley wrote: I have the following table schema in MySQL 4.1.18 which I didn't create, but have to work with. CREATE TABLE `phplog_userinput` ( `id` int(11) NOT NULL auto_increment, `inputfieldid` int(11) NOT NULL default '0', `userid` int(11) NOT NULL default '0', `value` varchar(150) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. Thanks. Albert Padley --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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With Join Syntax
Hi Chris. I cannot see how it can be done with the current table schema. Maybe you need to redeclare your table so the values in the value column are more distinct? What is value supposed to contain anyway? First name, last name and email address? What about a structure like: id | userid | ipf_1 | ipf_2 | ipf_3 1 2 JohnSmith email_addy Which will allow you to retrieve all the values you want from the table as one row without having to repeat the userid column? HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. On Wed, 14 Jun 2006, Chris White wrote: To: mysql@lists.mysql.com From: Chris White [EMAIL PROTECTED] Subject: Re: Problem With Join Syntax On Wednesday 14 June 2006 10:55 am, Albert Padley wrote: A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. This sounds like somewhat of a strange requirement. Why do they need to be in a single row? There MIGHT be a way to do it with stored procedures, I'm just not sure how.. Thanks. Albert Padley -- Chris White PHP Programmer/DB Fighter Interfuel -- 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]
Re: join syntax
Please reply to the list, rather than to me, personally. Keeping the thread on the list improves your odds of geting a solution, and allows others with similar questions to see the answers. Marco wrote: Michael Stassen wrote: What do you mean, it doesn't work? Michael Marco wrote: The query select * from a join b on a.x = b.y; works on mySQL 3.23.56 but doesn't work on mySQL 3.23.58. I had to change it in select * from a,b where a.x = b.y; Is it a well-known behavior or is it a bit strange? Thanks, Marco The join on syntax works on 3.23.56 but doesn't work on 3.23.58 Repeating yourself won't help. The query select * from a join b on a.x = b.y; is perfectly valid syntax and should produce the same result in 3.23.56 and 3.23.58. You need to say what you mean by doesn't work. Do you get an error message? If so, what is it?. Do you get unexpected results? If so, what are they, and what were you expecting? Either way, I'm guessing that what you've posted here is an example, rather than your real query. As your example is valid syntax, it seems likely that the problem stems from something in your real query. Therefore, it would be better to post the real query. In other words, we can't diagnose the problem until you describe in detail what is wrong. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join syntax
What do you mean, it doesn't work? Michael Marco wrote: The query select * from a join b on a.x = b.y; works on mySQL 3.23.56 but doesn't work on mySQL 3.23.58. I had to change it in select * from a,b where a.x = b.y; Is it a well-known behavior or is it a bit strange? Thanks, Marco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Join syntax diff 3.23 to 4.x?
Done. On Thu, 14 Aug 2003, Jack Dare wrote: Doh! You are so right. Thanks for the help. Got so used to not needing the INNER word that I forgot all about it. -Original Message- From: Kevin Fries [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 1:27 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Join syntax diff 3.23 to 4.x? Try changing JOIN list_states to INNER JOIN list_states Looks like the earlier version didn't like your (synonymous) wording. -Original Message- From: Jack Dare [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 8:35 AM To: [EMAIL PROTECTED] Subject: Join syntax diff 3.23 to 4.x? Is there something about 3.23 that makes this illegal? It is fine on 4.012. SELECT [lots of columns] FROM new_people p LEFT JOIN new_address ad ON p.address_id = ad.id JOIN list_states ls ON ad.state = ls.id WHERE p.company_id = 1 Will some minor rewording help it run on both versions OK? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- === Vladimir Dubnikov (Unix System Group) Computation center, Mount Scopus, Hebrew University of Jerusalem Tel:02-5883070 E-mail: [EMAIL PROTECTED] === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join syntax diff 3.23 to 4.x?
Is there something about 3.23 that makes this illegal? It is fine on 4.012. SELECT [lots of columns] FROM new_people p LEFT JOIN new_address ad ON p.address_id = ad.id JOIN list_states ls ON ad.state = ls.id WHERE p.company_id = 1 Will some minor rewording help it run on both versions OK? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Join syntax diff 3.23 to 4.x?
Try changing JOIN list_states to INNER JOIN list_states Looks like the earlier version didn't like your (synonymous) wording. -Original Message- From: Jack Dare [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 8:35 AM To: [EMAIL PROTECTED] Subject: Join syntax diff 3.23 to 4.x? Is there something about 3.23 that makes this illegal? It is fine on 4.012. SELECT [lots of columns] FROM new_people p LEFT JOIN new_address ad ON p.address_id = ad.id JOIN list_states ls ON ad.state = ls.id WHERE p.company_id = 1 Will some minor rewording help it run on both versions OK? -- 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]
RE: Join syntax diff 3.23 to 4.x?
Doh! You are so right. Thanks for the help. Got so used to not needing the INNER word that I forgot all about it. -Original Message- From: Kevin Fries [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 1:27 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Join syntax diff 3.23 to 4.x? Try changing JOIN list_states to INNER JOIN list_states Looks like the earlier version didn't like your (synonymous) wording. -Original Message- From: Jack Dare [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 8:35 AM To: [EMAIL PROTECTED] Subject: Join syntax diff 3.23 to 4.x? Is there something about 3.23 that makes this illegal? It is fine on 4.012. SELECT [lots of columns] FROM new_people p LEFT JOIN new_address ad ON p.address_id = ad.id JOIN list_states ls ON ad.state = ls.id WHERE p.company_id = 1 Will some minor rewording help it run on both versions OK? -- 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]
help with join syntax
Hi,
I have the following tables:
mysql describe lawfac_pub;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| hawkid | varchar(16) | | PRI | | |
| emailalias | varchar(128) | YES | | NULL| |
| first_name | varchar(64) | YES | | NULL| |
| last_name | varchar(64) | YES | | NULL| |
| title | varchar(128) | YES | | NULL| |
| building | varchar(128) | YES | | BLB | |
| phone | varchar(64) | YES | | NULL| |
| room | varchar(255) | YES | | NULL| |
| notes | varchar(255) | YES | | NULL| |
++--+--+-+-+---+
mysql describe fac_stud_lunch;
+---++--+-+-+---+
| Field | Type | Null | Key |
Default | Extra |
+---++--+-+-+---+
| instructor_hawkid | varchar(16)| | PRI
| | |
| meal_time | datetime | | PRI | -00-00
00:00:00 | |
| meal_type | enum('lunch','supper') | | |
lunch | |
| location |
varchar(128) | | | | |
| num_students | int(11)| YES | |
NULL| |
+---++--+-+-+---+
mysql describe fac_stud_lunch_join;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| instructor_hawkid | varchar(16) | | PRI | | |
| student_hawkid| varchar(16) | | PRI | | |
| meal_time | datetime| | PRI | -00-00 00:00:00 | |
+---+-+--+-+-+---+
And this is my query I run from php:
SELECT DISTINCT lawfac_pub.first_name, lawfac_pub.last_name,
date_format(fac_stud_lunch.meal_time, %W, %M %D, %Y) as formatted_date,
fac_stud_lunch.meal_time AS fac_stud_lunch_meal_time,
fac_stud_lunch.instructor_hawkid, fac_stud_lunch.meal_type,
fac_stud_lunch.location, fac_stud_lunch.num_students
FROM lawfac_pub INNER JOIN (fac_stud_lunch INNER JOIN fac_stud_lunch_join
ON fac_stud_lunch.instructor_hawkid =
fac_stud_lunch_join.instructor_hawkid) ON lawfac_pub.hawkid =
fac_stud_lunch_join.instructor_hawkid
WHERE (((fac_stud_lunch_join.student_hawkid) Not Like 's1')) order by
fac_stud_lunch.meal_time;
And I keep getting an error on the inner join. I've usually been able to
get the syntax for joining 3 or 4 tables by using Access, but it seems to
have failed me this time.
Any ideas why this select statement is failing?
Thanks,
Bob
==
Bob Ramsey Applications Development Support II
ph: 1(319)335-9956 216 Boyd Law Building
fax: 1(319)335-9019 University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
-
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: help with join syntax
Hi.
This is not at all an answer to your problem,
and perhaps you're already aware of my tip:
I usually use phpMyAdmin and paste my SQL statements
in there to see what is causing the error, and when it works
you get a pretty good picture of how your virtual table
looks like.
Just my 2 cents.
Cheers, Martin
On Tue, 4 Mar 2003, Bob Ramsey wrote:
Hi,
I have the following tables:
mysql describe lawfac_pub;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| hawkid | varchar(16) | | PRI | | |
| emailalias | varchar(128) | YES | | NULL| |
| first_name | varchar(64) | YES | | NULL| |
| last_name | varchar(64) | YES | | NULL| |
| title | varchar(128) | YES | | NULL| |
| building | varchar(128) | YES | | BLB | |
| phone | varchar(64) | YES | | NULL| |
| room | varchar(255) | YES | | NULL| |
| notes | varchar(255) | YES | | NULL| |
++--+--+-+-+---+
mysql describe fac_stud_lunch;
+---++--+-+-+---+
| Field | Type | Null | Key |
Default | Extra |
+---++--+-+-+---+
| instructor_hawkid | varchar(16)| | PRI
| | |
| meal_time | datetime | | PRI | -00-00
00:00:00 | |
| meal_type | enum('lunch','supper') | | |
lunch | |
| location |
varchar(128) | | | | |
| num_students | int(11)| YES | |
NULL| |
+---++--+-+-+---+
mysql describe fac_stud_lunch_join;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| instructor_hawkid | varchar(16) | | PRI | | |
| student_hawkid| varchar(16) | | PRI | | |
| meal_time | datetime| | PRI | -00-00 00:00:00 | |
+---+-+--+-+-+---+
And this is my query I run from php:
SELECT DISTINCT lawfac_pub.first_name, lawfac_pub.last_name,
date_format(fac_stud_lunch.meal_time, %W, %M %D, %Y) as formatted_date,
fac_stud_lunch.meal_time AS fac_stud_lunch_meal_time,
fac_stud_lunch.instructor_hawkid, fac_stud_lunch.meal_type,
fac_stud_lunch.location, fac_stud_lunch.num_students
FROM lawfac_pub INNER JOIN (fac_stud_lunch INNER JOIN fac_stud_lunch_join
ON fac_stud_lunch.instructor_hawkid =
fac_stud_lunch_join.instructor_hawkid) ON lawfac_pub.hawkid =
fac_stud_lunch_join.instructor_hawkid
WHERE (((fac_stud_lunch_join.student_hawkid) Not Like 's1')) order by
fac_stud_lunch.meal_time;
And I keep getting an error on the inner join. I've usually been able to
get the syntax for joining 3 or 4 tables by using Access, but it seems to
have failed me this time.
Any ideas why this select statement is failing?
Thanks,
Bob
==
Bob Ramsey Applications Development Support II
ph: 1(319)335-9956 216 Boyd Law Building
fax: 1(319)335-9019 University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
-
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: help with join syntax
At 03:57 PM 3/4/2003, Martin Ostlund wrote: I usually use phpMyAdmin Thanks for the tip. Unfortunately it isn't my server and they haven't finished installing phpMyAdmin on it yet. That's why I usually make odbc connections in access and build the queries graphically. But it just keeps choking on the join statement. You have an error in your SQL syntax near '(fac_stud_lunch INNER JOIN fac_stud_lunch_join ON fac_stud_lunch.instructor_hawk' at line 2 Thanks, Bob == Bob Ramsey Applications Development Support II ph: 1(319)335-9956 216 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == - 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: help with join syntax
try thi
FROM lawfac_pub INNER JOIN fac_stud_lunch ON lawfac_pub.hawkid =
fac_stud_lunch_join.instructor_hawkid INNER JOIN fac_stud_lunch_join ON
fac_stud_lunch.instructor_hawkid = fac_stud_lunch_join.instructor_hawkid
-Original Message-
From: Martin Ostlund [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 04, 2003 4:58 PM
To: Bob Ramsey
Cc: [EMAIL PROTECTED]
Subject: Re: help with join syntax
Hi.
This is not at all an answer to your problem,
and perhaps you're already aware of my tip:
I usually use phpMyAdmin and paste my SQL statements
in there to see what is causing the error, and when it works
you get a pretty good picture of how your virtual table
looks like.
Just my 2 cents.
Cheers, Martin
On Tue, 4 Mar 2003, Bob Ramsey wrote:
Hi,
I have the following tables:
mysql describe lawfac_pub;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| hawkid | varchar(16) | | PRI | | |
| emailalias | varchar(128) | YES | | NULL| |
| first_name | varchar(64) | YES | | NULL| |
| last_name | varchar(64) | YES | | NULL| |
| title | varchar(128) | YES | | NULL| |
| building | varchar(128) | YES | | BLB | |
| phone | varchar(64) | YES | | NULL| |
| room | varchar(255) | YES | | NULL| |
| notes | varchar(255) | YES | | NULL| |
++--+--+-+-+---+
mysql describe fac_stud_lunch;
+---++--+-+-
+---+
| Field | Type | Null | Key |
Default | Extra |
+---++--+-+-
+---+
| instructor_hawkid | varchar(16)| | PRI
| | |
| meal_time | datetime | | PRI | -00-00
00:00:00 | |
| meal_type | enum('lunch','supper') | | |
lunch | |
| location |
varchar(128) | | | | |
| num_students | int(11)| YES | |
NULL| |
+---++--+-+-
+---+
mysql describe fac_stud_lunch_join;
+---+-+--+-+-+--
-+
| Field | Type| Null | Key | Default |
Extra |
+---+-+--+-+-+--
-+
| instructor_hawkid | varchar(16) | | PRI | |
|
| student_hawkid| varchar(16) | | PRI | |
|
| meal_time | datetime| | PRI | -00-00 00:00:00 |
|
+---+-+--+-+-+--
-+
And this is my query I run from php:
SELECT DISTINCT lawfac_pub.first_name, lawfac_pub.last_name,
date_format(fac_stud_lunch.meal_time, %W, %M %D, %Y) as formatted_date,
fac_stud_lunch.meal_time AS fac_stud_lunch_meal_time,
fac_stud_lunch.instructor_hawkid, fac_stud_lunch.meal_type,
fac_stud_lunch.location, fac_stud_lunch.num_students
FROM lawfac_pub INNER JOIN (fac_stud_lunch INNER JOIN fac_stud_lunch_join
ON fac_stud_lunch.instructor_hawkid =
fac_stud_lunch_join.instructor_hawkid) ON lawfac_pub.hawkid =
fac_stud_lunch_join.instructor_hawkid
WHERE (((fac_stud_lunch_join.student_hawkid) Not Like 's1')) order by
fac_stud_lunch.meal_time;
And I keep getting an error on the inner join. I've usually been able to
get the syntax for joining 3 or 4 tables by using Access, but it seems to
have failed me this time.
Any ideas why this select statement is failing?
Thanks,
Bob
==
Bob Ramsey Applications Development Support II
ph: 1(319)335-9956 216 Boyd Law Building
fax: 1(319)335-9019 University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
-
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
Re: three table join (RE brent baisley's post) BETTER join syntax?
same results and both varying between .01, .02 seconds to execute. 6 of one half dozen of another or is there an advantage to one? My guess is that the second syntax is preferred given the reduced row count for events in it's explain table. The first statement uses left joins, the second use's Brent suggestion. Also, is the second statement what they call a straight join? thanks, jb mysql explain select people.id, concat(lastname, ', ', firstname) as name, titl e, - organizations.org, ptype, groupname, meeting, room, location, - moderator,addnotes, dtime, edtime - from people - left join organizations on people.org=organizations.id - left join otype on organizations.otype=otype.id - left join pgroups on people.id=pgroups.pid - left join groups on pgroups.gid=groups.id - left join mgroups on pgroups.gid=mgroups.gid - left join meetings on mgroups.mid=meetings.id - left join rooms on meetings.rid=rooms.id - left join locations on rooms.lid=locations.id - left join events on meetings.eid=events.id - where events.id=2 order by type; +---++---+-+-+-- ---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-- ---+--+-+ | people| ALL| NULL | NULL|NULL | NULL | 17 | Using temporary; Using filesort | | organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org |1 | | | otype | eq_ref | PRIMARY | PRIMARY | 2 | organizations.oty pe |1 | | | pgroups | ALL| NULL | NULL|NULL | NULL | 14 | | | groups| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid |1 | | | mgroups | ALL| NULL | NULL|NULL | NULL |6 | | | meetings | eq_ref | PRIMARY | PRIMARY | 2 | mgroups.mid |1 | | | rooms | eq_ref | PRIMARY | PRIMARY | 2 | meetings.rid |1 | | | locations | eq_ref | PRIMARY | PRIMARY | 2 | rooms.lid |1 | | | events| eq_ref | PRIMARY | PRIMARY | 2 | meetings.eid |1 | where used; Using index | +---++---+-+-+-- ---+--+-+ 10 rows in set (0.00 sec) === === mysql explain select people.id, concat(lastname, ', ', firstname) as name, titl e, - organizations.org, ptype, groupname, meeting, room, location, - moderator, addnotes, dtime, edtime - from people, organizations, otype, pgroups, groups, mgroups, meeting s, rooms, locations, events - where people.org=organizations.id - and organizations.otype=otype.id - and people.id=pgroups.pid - and pgroups.gid=groups.id - and pgroups.gid=mgroups.gid - and mgroups.mid=meetings.id - and meetings.rid=rooms.id - and rooms.lid=locations.id - and meetings.eid=events.id - and events.id=2 order by type; +---++---+-+-+-- ---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-- ---+--+-+ | events| const | PRIMARY | PRIMARY | 2 | const |1 | Using temporary; Using filesort | | pgroups | ALL| NULL | NULL|NULL | NULL | 14 | | | mgroups | ALL| NULL | NULL|NULL | NULL |6 | where used | | people| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.pid |1 | | | groups| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid |1 | | | organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org |1 | | | otype | eq_ref | PRIMARY | PRIMARY | 2 | organizations.oty pe |1 |
outer join syntax
I would like to return rows, with specific columns, for each individual in my NAMES table, with address and e-mail if these exist. Thought I had the syntax down but I'm getting NULL results where I know there is data. I'm used to using SQL Server shortcut * for outer joins, guess that's catching up with me now :( I would have thought the syntax would be: SELECT A.LAST_NAME, A.FIRST_NAME, B.CITY, B.STATE, C.EMAIL FROMNAMES_TBL A LEFT JOIN ADDRESS_TBL B ON A.DBID = B.DBID LEFT JOIN EMAIL_TBL C ON A.DBID = C.DBID WHERE A.LAST_NAME = 'Smith' Am I doing it wrong? chris - 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: MySQl LEFT JOIN Syntax
* yan zhang My MySQL LEFT JOIN Syntax as following is wrong? I cannot get the right result. What result do you get? SELECT name, email form T1 LEFT JOIN T2 ON T1.Snum=T2.Bnum Except from the typo form - FROM, this looks like a valid statement. Do you get an error? T1(Snum,name,email,address) T2(Bnum, notes, time) I wnat ot get the result(name, email, notes). If you want 'notes' to appear in the result, you must mention it in the field list: SELECT name, email, notes FROM T1 LEFT JOIN T2 ON T1.Snum=T2.Bnum -- Roger - 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
MySQl LEFT JOIN Syntax
Hi there, My MySQL LEFT JOIN Syntax as following is wrong? I cannot get the right result. SELECT name, email form T1 LEFT JOIN T2 ON T1.Snum=T2.Bnum T1(Snum,name,email,address) T2(Bnum, notes, time) I wnat ot get the result(name, email, notes). Any suggestion, comments? Thanks Yan __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.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
Re: MySQl LEFT JOIN Syntax
suggestion: if you have a windows machine that you can use as a front end, download a copy of corereader. http://corereader.com it lets you create joins by clicking on objects in your database. you can quickly experiment with the logic instead of worrying about syntax. let corereader take care of the details as you develop the query that you want. i use it, but it seems to be especially beneficial to newcomers. Hi there, My MySQL LEFT JOIN Syntax as following is wrong? I cannot get the right result. SELECT name, email form T1 LEFT JOIN T2 ON T1.Snum=T2.Bnum T1(Snum,name,email,address) T2(Bnum, notes, time) I wnat ot get the result(name, email, notes). Any suggestion, comments? Thanks Yan -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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
Re: Problem with join syntax
Hello, thanx for your help. Anyhow this statement does not return the wanted fields. I would like to return the website and the signature of the user, but only if those values are available. There might be none of them available, but maybe 1 or even both. this query: SELECT w.website, s.signature FROM user_websites LEFT JOIN user_signature AS s ON s.user_id = '3' LEFT JOIN user_websites AS w ON w.user_id = '3' does return all records of the table website (45000!) Your stmt does join the website table with the comment (in my case signature) table: SELECT w.website, c.comment FROM user_websites AS w LEFT JOIN user_comments AS c ON c.user_id = w.user_id WHERE w.user_id = '10' But thats not what I want. I do want just to know if the user has a website, or a signature and then pull them out if they are there. Thanx for your help, Andy - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Cc: andy [EMAIL PROTECTED] Sent: Monday, May 20, 2002 3:56 PM Subject: Re: Problem with join syntax * andy [EMAIL PROTECTED] thank you roger for your reply. I am wondering how to form the stmt if I do already know the user_id I tryed this, but it does not work though. Anyhow there has to be a from field. But on which table? SELECT w.website, c.comment LEFT JOIN user_websites AS w ON w.user_id = '10' LEFT JOIN user_comments AS c ON c.user_id = '10' That is correct, when you use LEFT JOIN (or any join, actually) you must select FROM one table, and then JOIN with the other table(s). Try this: SELECT w.website, c.comment FROM user_websites AS w LEFT JOIN user_comments AS c ON c.user_id = w.user_id WHERE w.user_id = '10' Note that the ON expression contains one field from each of the two tables. This expression is also known as the 'join condition'. It is perfectly legal to use a constant, like you did, but that will not be possible in most cases, like if you want to list more than one user. The above statement will list the website for the user with id '10', even if he does not have an associated comment, because of the LEFT JOIN. The following statement would only find website-rows _with_ comments, ie, if user_id '10' does not have a comment, you will get 0 rows: SELECT w.website, c.comment FROM user_websites AS w, user_comments AS c WHERE c.user_id = w.user_id AND w.user_id = '10' This statement does not contain the word JOIN, but it is still a joined select. The comma between the tables in the FROM caluse means 'INNER JOIN'. This is the _excact_ same statement: SELECT w.website, c.comment FROM user_websites AS w INNER JOIN user_comments AS c WHERE c.user_id = w.user_id AND w.user_id = '10' ...and this is also legal (in versions 3.23.16 and later): SELECT w.website, c.comment FROM user_websites AS w INNER JOIN user_comments AS c ON c.user_id = w.user_id WHERE w.user_id = '10' The join condition can be in the WHERE clause, but it is still a join condition. I hope this makes things clearer. :) -- Roger sql - 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: Problem with join syntax
* andy
thanx for your help. Anyhow this statement does not return the wanted
fields.
I would like to return the website and the signature of the
user, but only
if those values are available. There might be none of them available, but
maybe 1 or even both.
this query:
SELECT
w.website,
s.signature
FROM
user_websites
LEFT JOIN user_signature AS s ON s.user_id = '3'
LEFT JOIN user_websites AS w ON w.user_id = '3'
does return all records of the table website (45000!)
Yes... you should have a user table, with one row for each user, and select
FROM that table.
Your stmt does join the website table with the comment (in my case
signature) table:
SELECT w.website, c.comment
FROM user_websites AS w
LEFT JOIN user_comments AS c ON c.user_id = w.user_id
WHERE w.user_id = '10'
But thats not what I want. I do want just to know if the user has
a website,
or a signature and then pull them out if they are there.
You can not find out anything about a 'user' if you don't have a 'user' in
your system... :) You should have a user table, with user id, user name etc.
The user id should be the primary key, and your fields 'user_id' in the
tables user_signatures and user_websites would reffer to the primary key.
If you really don't have/want a user table, try something like this:
(You use quotes around your user_id constants, so I assume it is a string...
it could (should?) be an integer. If it is an integer, use 'int' instead of
'varchar(255)' in the create statement.)
create temporary table tmp_users (user_id varchar(255));
insert into tmp_users values ('3');
SELECT w.website, s.signature
FROM tmp_users
LEFT JOIN user_signature AS s ON s.user_id = tmp_users.user_id
LEFT JOIN user_websites AS w ON w.user_id = tmp_users.user_id
WHERE tmp_users.user_id = '3'
The WHERE clause is not needed here, but I include it because it _is_ needed
if you use a real user table, with multiple rows.
Your first query in this post would probably work if you select FROM
tmp_users, but proper join conditions like in this last query is cleaner,
and easier to expand.
A temporary table is automatically deleted when the connection is closed,
and if multiple users create a temporary table with the same name at the
same time, that's ok with mysql.
--
Roger
-
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: Problem with join syntax
* andy [EMAIL PROTECTED] thank you roger for your reply. I am wondering how to form the stmt if I do already know the user_id I tryed this, but it does not work though. Anyhow there has to be a from field. But on which table? SELECT w.website, c.comment LEFT JOIN user_websites AS w ON w.user_id = '10' LEFT JOIN user_comments AS c ON c.user_id = '10' That is correct, when you use LEFT JOIN (or any join, actually) you must select FROM one table, and then JOIN with the other table(s). Try this: SELECT w.website, c.comment FROM user_websites AS w LEFT JOIN user_comments AS c ON c.user_id = w.user_id WHERE w.user_id = '10' Note that the ON expression contains one field from each of the two tables. This expression is also known as the 'join condition'. It is perfectly legal to use a constant, like you did, but that will not be possible in most cases, like if you want to list more than one user. The above statement will list the website for the user with id '10', even if he does not have an associated comment, because of the LEFT JOIN. The following statement would only find website-rows _with_ comments, ie, if user_id '10' does not have a comment, you will get 0 rows: SELECT w.website, c.comment FROM user_websites AS w, user_comments AS c WHERE c.user_id = w.user_id AND w.user_id = '10' This statement does not contain the word JOIN, but it is still a joined select. The comma between the tables in the FROM caluse means 'INNER JOIN'. This is the _excact_ same statement: SELECT w.website, c.comment FROM user_websites AS w INNER JOIN user_comments AS c WHERE c.user_id = w.user_id AND w.user_id = '10' ...and this is also legal (in versions 3.23.16 and later): SELECT w.website, c.comment FROM user_websites AS w INNER JOIN user_comments AS c ON c.user_id = w.user_id WHERE w.user_id = '10' The join condition can be in the WHERE clause, but it is still a join condition. I hope this makes things clearer. :) -- Roger sql - 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
Problem with join syntax
Hi there, I have some trouble with the syntax of join query and some perfomance isues. I did split my data due to the rules of normalisation. So there is a website table and a comment table and I would like to find out if the user with the id '10215' has a stored comment or website. Here is my stmt: SELECT w.website, c.comment FROM user_websites w LEFT JOIN user_comments AS c ON c.user_id = '10215' WHERE w.user_id = '10215' So what if comments contains 1 million entries. Or what if I have 10 such tables and would have to make a left join on each of them? I fear that the performance would suffer. is there a better way to do this in one stmt? Thanx for any help, Andy - 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
nested join syntax error
Hello All, I have a LEFT JOIN nested inside an INNER JOIN that generates a mySQL syntax error. I'm new to mySQL so I wonder if anyone could help me understand the problem. The query runs fine under PostgreSQL, MS SQL, and MS Access. SELECT Recording.Entry, InstCode.InstType, Recording.DateRec FROM InstCode INNER JOIN (Recording LEFT JOIN Legal ON Recording.Entry = Legal.Entry) ON InstCode.InstCode = Recording.InstCode WHERE Legal.PIDN = '22-41-17-26-2-00-005'; Thanks, Rich - 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: FW: I need an example on DELETE /UPDATE with JOIN syntax
Vadim,
Monday, April 29, 2002, 7:10:27 PM, you wrote:
V My company migrated to MySQL a few months ago from Sybase Adaptive
V Server Anywhere. Where I'm very impressed with the performance and the
V simplicity of management I find its very difficult, however, to run
V regular updates or deletes because joins are not supported on delete and
V update. I can live without stored procedures, triggers and views
V (although, they are very useful simply can not function as a DBA /SQL
V developer without being able to perform simple delete on a table set
V based on the result of a join.
V Can someone, please, show me an example on how can I delete/update table
V based on the records in another tables. This is query that works on ASA:
V delete from t1
V from t1, t2
V where t1.col1 = t2.col1
V and t2.col2 is NULL ;
V update t1, t2
V set t1.col=''
V where t1.col1 = t2.col1
V and t2.col2 is NULL
Create script with SQL statement:
SELECT CONCAT('UPDATE t1 SET t1.col=\'\' WHERE t1.col1 = ', t1.col1, ';')
FROM t1, t2
WHERE t1.col1= t2.col1
AND t2.col2 IS NULL;
and then run the script as described in the manual:
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
V This syntax does not work in MySQL.
V Thank you very much for you time.
V Vadim Kulikov
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [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
FW: I need an example on DELETE /UPDATE with JOIN syntax
Greetings: My company migrated to MySQL a few months ago from Sybase Adaptive Server Anywhere. Where I'm very impressed with the performance and the simplicity of management I find its very difficult, however, to run regular updates or deletes because joins are not supported on delete and update. I can live without stored procedures, triggers and views (although, they are very useful simply can not function as a DBA /SQL developer without being able to perform simple delete on a table set based on the result of a join. Can someone, please, show me an example on how can I delete/update table based on the records in another tables. This is query that works on ASA: delete from t1 from t1, t2 where t1.col1 = t2.col1 and t2.col2 is NULL ; update t1, t2 set t1.col='' where t1.col1 = t2.col1 and t2.col2 is NULL This syntax does not work in MySQL. # I need help! # Thank you very much for you time. Vadim Kulikov 877-428-3279 [EMAIL PROTECTED] P.S. Someone left a comment with the example on how you can do delete with join under MYSQL help site. But its not working. These are the comments I copied from the http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html (MySQL documentation site): (the problem is that the following example with a flag doesn't work due to inability to do update with a join) Comments: - John Gwilliam: Deletes and sub-selects. I have found a convenient way of avoiding sub- selects for deleting as follows - 1/ Add a DELETE_FLAG column to the table involved. 2/ Set the DELETE_FLAG using UPDATE, where joins can be used. 3/ DELETE from the table using a simple WHERE clause to select rows where the DELETE_FLAG is set. [EMAIL PROTECTED]: I'm having trouble with your delete suggestion, can you give an example of the update query you use for your sub-selects for deleting purposes? While SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; works fine, I can't get UPDATE table1 set deleteflag = 1 where table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL to work. Does anyone know how to use LEFT JOINs in an update statement? - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
I need an example on DELETE /UPDATE with JOIN syntax
Greetings: My company migrated to MySQL a few months ago from Sybase Adaptive Server Anywhere. Where I'm very impressed with the performance and the simplicity of management I find its very difficult, however, to run regular updates or deletes because joins are not supported on delete and update. I can live without stored procedures, triggers and views (although, they are very useful simply can not function as a DBA /SQL developer without being able to perform simple delete on a table set based on the result of a join. Can someone, please, show me an example on how can I delete/update table based on the records in another tables. This is query that works on ASA: delete from t1 from t1, t2 where t1.col1 = t2.col1 and t2.col2 is NULL ; update t1, t2 set t1.col='' where t1.col1 = t2.col1 and t2.col2 is NULL This syntax does not work in MySQL. # I need help! # Thank you very much for you time. Vadim Kulikov 877-428-3279 [EMAIL PROTECTED] P.S. Someone left a comment with the example on how you can do delete with join under MYSQL help site. But its not working. These are the comments I copied from the http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html (MySQL documentation site): (the problem is that the following example with a flag doesn't work due to inability to do update with a join) Comments: - John Gwilliam: Deletes and sub-selects. I have found a convenient way of avoiding sub- selects for deleting as follows - 1/ Add a DELETE_FLAG column to the table involved. 2/ Set the DELETE_FLAG using UPDATE, where joins can be used. 3/ DELETE from the table using a simple WHERE clause to select rows where the DELETE_FLAG is set. [EMAIL PROTECTED]: I'm having trouble with your delete suggestion, can you give an example of the update query you use for your sub-selects for deleting purposes? While SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; works fine, I can't get UPDATE table1 set deleteflag = 1 where table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL to work. Does anyone know how to use LEFT JOINs in an update statement? - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: I need an example on DELETE /UPDATE with JOIN syntax
-Original Message- From: Vadim [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 27, 2002 4:49 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: I need an example on DELETE /UPDATE with JOIN syntax Greetings: My company migrated to MySQL a few months ago from Sybase Adaptive Server Anywhere. Where I'm very impressed with the performance and the simplicity of management I find its very difficult, however, to run regular updates or deletes because joins are not supported on delete and update. I can live without stored procedures, triggers and views (although, they are very useful simply can not function as a DBA /SQL developer without being able to perform simple delete on a table set based on the result of a join. Can someone, please, show me an example on how can I delete/update table based on the records in another tables. This is query that works on ASA: delete from t1 from t1, t2 where t1.col1 = t2.col1 and t2.col2 is NULL ; update t1, t2 set t1.col='' where t1.col1 = t2.col1 and t2.col2 is NULL This syntax does not work in MySQL. # I need help! # Thank you very much for you time. Vadim Kulikov 877-428-3279 [EMAIL PROTECTED] P.S. Someone left a comment with the example on how you can do delete with join under MYSQL help site. But its not working. These are the comments I copied from the http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html (MySQL documentation site): (the problem is that the following example with a flag doesn't work due to inability to do update with a join) Comments: - John Gwilliam: Deletes and sub-selects. I have found a convenient way of avoiding sub- selects for deleting as follows - 1/ Add a DELETE_FLAG column to the table involved. 2/ Set the DELETE_FLAG using UPDATE, where joins can be used. 3/ DELETE from the table using a simple WHERE clause to select rows where the DELETE_FLAG is set. [EMAIL PROTECTED]: I'm having trouble with your delete suggestion, can you give an example of the update query you use for your sub-selects for deleting purposes? While SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; works fine, I can't get UPDATE table1 set deleteflag = 1 where table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL to work. Does anyone know how to use LEFT JOINs in an update statement? - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help w/ join syntax
When I call the following PHP script, $qry_1 doesn't work (No query # 1!).
?php
set_time_limit(300);
$connection=mysql_connect(localhost,wagner,???) or die (No
connection!);
$db=mysql_select_db(sbwresearch,$connection) or die (No database!);
$qry_1=select
first_name.con_inf,
...
zip.con_inf,
label.svy_149
from con_inf, svy_149 where con_inf.username=svy_149.username;
$result_1=mysql_query($qry_1,$connection) or die (No query # 1!);
while ($row_1=mysql_fetch_array($result_1, MYSQL_ASSOC))
{
$first_name=$row_1[first_name];
...
$zip=$row_1[zip];
$label=$row_1[label];
if ($label==n)
{
$qry_2=insert into address_149
(
first_name,
...,
zip)
values (
\$first_name\,
...,
\$zip\
);
$result_2=mysql_query($qry_2,$connection);
};
};
mysql_free_result($result_1);
mysql_close($connection);
echo bDONE/b;
flush();
exit;
?
Thank you!
Anthony F. Rodriguez
([EMAIL PROTECTED])
-
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: Help w/ join syntax
Hi. On Mon, Mar 11, 2002 at 09:42:01AM -0500, [EMAIL PROTECTED] wrote: When I call the following PHP script, $qry_1 doesn't work (No query # 1!). ?php [...] $result_1=mysql_query($qry_1,$connection) or die (No query # 1!); At first glance, I don't see any error. But change the previous line to something like $result_1=mysql_query($qry_1,$connection) or die(query #1: . mysql_errno() . : . mysql_error() . br); And you will get a more reasonable error message. Bye, Benjamin. -- [EMAIL PROTECTED] - 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: MySQL JOIN syntax question
On Wednesday 21 November 2001 03:01, James O'Brien wrote: I have been trying to perform a join with several tables and I am having no joy. error messages would increase the understanding of exactly what joy you aren't having ;-) Can some one send me a query (just a select * is fine) for these tables the ID's i'm using below aren't the real column names but they will do for the purpose of this email. select * from table1 left join table2 on table1.id=table2.id table2 left join table3 on table2.id1=table3.id2 and table2.id2=table3id2 table3 left join table4 on table3.id3=table4.id3 table3 left join table5 on table3.id4=table5.id4 table3 left join table6 on table3.id5=table6.id5 table3 left join table7 on table3.id6=table7.id6 Have you tried something like this?: select * from table1 left join table2 on table1.id=table2.id left join table3 on table2.id1=table3.id2 and table2.id2=table3id2 left join table4 on table3.id3=table4.id3 left join table5 on table3.id4=table5.id4 left join table6 on table3.id5=table6.id5 left join table7 on table3.id6=table7.id6 HTH -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
MySQL JOIN syntax question
I have been trying to perform a join with several tables and I am having no joy. Can some one send me a query (just a select * is fine) for these tables the ID's i'm using below aren't the real column names but they will do for the purpose of this email. select * from table1 left join table2 on table1.id=table2.id table2 left join table3 on table2.id1=table3.id2 and table2.id2=table3id2 table3 left join table4 on table3.id3=table4.id3 table3 left join table5 on table3.id4=table5.id4 table3 left join table6 on table3.id5=table6.id5 table3 left join table7 on table3.id6=table7.id6 If someone can show me how write this type of query that would work on mysql, it would be greatly appreciated. We are moving our Access 97 DB's to Mysql (probably Mysql 4 with builtin support for InnoDB tables for row-level locking). SQL Server is too pricey for our needs. cheers, James O'Brien Senior Technical Analyst PowerConnex Pty Ltd - 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
Outer join syntax..
Hi all, MySQL doesn't support nested subqueries, I know, and I also understand that the same results can be achieved through outer joins (Left join, Right join and WHERE IS [NOT] NULL). Also, inner joins can be expressed either in the FROM clause or the WHERE clause. My question is: Can outer joins be expressed in the FROM clause as they can in Oracle (using (+)) and MSSQL (using '*=' or '=*')? What is the syntax? I think I have looked through the documentation thoroughly enough, that this question is not a waste of anyone's time. If this is dealt with in the manual, I apologise humbly, but where is it?!? Thanks, Bruce. - 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
AW: Outer join syntax..
MySQL uses the LEFT JOIN, RIGHT JOIN Syntax ... RTFM: http://www.mysql.com/doc/J/O/JOIN.html -Ursprüngliche Nachricht- Von: Bruce Stewart [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 16. Juli 2001 11:54 An: Mysql-Help (E-mail) Betreff: Outer join syntax.. Hi all, MySQL doesn't support nested subqueries, I know, and I also understand that the same results can be achieved through outer joins (Left join, Right join and WHERE IS [NOT] NULL). Also, inner joins can be expressed either in the FROM clause or the WHERE clause. My question is: Can outer joins be expressed in the FROM clause as they can in Oracle (using (+)) and MSSQL (using '*=' or '=*')? What is the syntax? I think I have looked through the documentation thoroughly enough, that this question is not a waste of anyone's time. If this is dealt with in the manual, I apologise humbly, but where is it?!? Thanks, Bruce. - 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: Join syntax
Rodney - You can't execute a query across two connections simultaneously. If you must split the tables between two hosts, network then together at the file system level so that the database directories are accessible by one instance of MySQL. Then connect and query that instance... \\|// (@ @) ---oOO---(_)---OOo || | Fred Dinkler | | SVP Technology | | DFII Atlanta | | Office: 01.770.596.1443| || -- |__|__| || || ooO Ooo -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 10, 2001 12:58 PM To: Werner Stuerenburg Cc: [EMAIL PROTECTED] Subject: Re: Join syntax I have two databases (db1 e db2) on different hosts (host1 e host2). I put them on different hosts to split the charge (they are very big databases). However, I have to make some selects on tables of db2 filtering with information that are on db1. If db1 and db2 were at the same machine, I wouldn't have problem, because the table_reference would be db1.table1 AS t1, db2.table2 AS t2. But as they are on different machines, I didn't know the syntax of the table_reference or even if it was possible. Thanks, Rodney Werner Stuerenburg [EMAIL PROTECTED] em 10/07/2001 10:51:39 Favor responder a Werner Stuerenburg [EMAIL PROTECTED] Para:RODNEY ANTONIO RAMOS RODNEYR/Embratel@Embratel cc: [EMAIL PROTECTED] Assunto: Re: Join syntax - 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
Join syntax
Is it possible to join two tables from different databases that are located on different hosts? Thanks, Rodney - 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: Join syntax
As it seems, it is not possible. You would have to have a connection which talks to two databases at once, which may not be possible at all, at least create numerous problems. But I think it should be possible to create a temporary table in db 2 and copy the table from db1 into it, then do a join with that temporary table. Question is: what kind of an application do you have that you need such a thing? Is it possible to join two tables from different databases that are located on different hosts? -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: Fwd: Join syntax
I think the solution would be to do the join at the application level rather than in the database. We frequently had to do this in msql because it did joins so badly. Basically you do the select in the first table including the linking field and then do selects against the second table (this is actually relatively fast using prepared queries if the linking field(s) in the second database is an index. On Mon, 9 Jul 2001, Rodney A. Ramos wrote: Date: Mon, 9 Jul 2001 21:38:44 -0300 From: Rodney A. Ramos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Fwd: Join syntax Is it possible to join two tables from different databases that are located on different hosts? Thanks, Rodney - 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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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: Join syntax
I have two databases (db1 e db2) on different hosts (host1 e host2). I put them on different hosts to split the charge (they are very big databases). However, I have to make some selects on tables of db2 filtering with information that are on db1. If db1 and db2 were at the same machine, I wouldn't have problem, because the table_reference would be db1.table1 AS t1, db2.table2 AS t2. But as they are on different machines, I didn't know the syntax of the table_reference or even if it was possible. Thanks, Rodney Werner Stuerenburg [EMAIL PROTECTED] em 10/07/2001 10:51:39 Favor responder a Werner Stuerenburg [EMAIL PROTECTED] Para:RODNEY ANTONIO RAMOS RODNEYR/Embratel@Embratel cc: [EMAIL PROTECTED] Assunto: Re: Join syntax As it seems, it is not possible. You would have to have a connection which talks to two databases at once, which may not be possible at all, at least create numerous problems. But I think it should be possible to create a temporary table in db 2 and copy the table from db1 into it, then do a join with that temporary table. Question is: what kind of an application do you have that you need such a thing? Is it possible to join two tables from different databases that are located on different hosts? -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: Join syntax
This topic really interested me. Can an expert of the list explain a good approach handle distributed system with distributed databases? thanks siomara From: [EMAIL PROTECTED] To: Werner Stuerenburg [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Join syntax Date: Tue, 10 Jul 2001 13:57:37 -0300 I have two databases (db1 e db2) on different hosts (host1 e host2). I put them on different hosts to split the charge (they are very big databases). However, I have to make some selects on tables of db2 filtering with information that are on db1. If db1 and db2 were at the same machine, I wouldn't have problem, because the table_reference would be db1.table1 AS t1, db2.table2 AS t2. But as they are on different machines, I didn't know the syntax of the table_reference or even if it was possible. Thanks, Rodney Werner Stuerenburg [EMAIL PROTECTED] em 10/07/2001 10:51:39 Favor responder a Werner Stuerenburg [EMAIL PROTECTED] Para:RODNEY ANTONIO RAMOS RODNEYR/Embratel@Embratel cc: [EMAIL PROTECTED] Assunto: Re: Join syntax As it seems, it is not possible. You would have to have a connection which talks to two databases at once, which may not be possible at all, at least create numerous problems. But I think it should be possible to create a temporary table in db 2 and copy the table from db1 into it, then do a join with that temporary table. Question is: what kind of an application do you have that you need such a thing? Is it possible to join two tables from different databases that are located on different hosts? -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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 _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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
Join syntax
Is it possible to join two columns from different databases located on differents hosts? Thanks, Rodney - 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: Join syntax
Is it possible to join two columns from different databases Yes. located on differents hosts? No. Why do you want to do this? - 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
Join syntax
Is it possible to join two tables from different databases that are located on different hosts? Thanks, Rodney - 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
Join syntax
Is it possible to join two tables from different databases that are located on different hosts? Thanks, Rodney - 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: Join syntax
On Mon, Jul 09, 2001 at 09:29:00PM -0300, Rodney A. Ramos wrote: Is it possible to join two tables from different databases that are located on different hosts? Nope. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 23 days, processed 178,432,519 queries (87/sec. avg) - 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
Fwd: Join syntax
Is it possible to join two tables from different databases that are located on different hosts? Thanks, Rodney - 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: Conversion from Access JOIN syntax to MySQL JOIN syntax...
As it is such a big query, I don't have time to look at it for you but it may be easier if you convert the RIGHT JOINS to LEFT JOINS for starters. The manual does recommend LEFT JOINS, mainly for portability. First part would be something like (leg_activity LEFT JOIN leg_comm_hist ON (leg_comm_hist.leg_activity_id = leg_activity.id) LEFT JOIN leg_version_hist ON leg_version_hist.leg_activity_id = leg_activity.id) - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Wednesday, June 27, 2001 2:47 Subject: Conversion from Access JOIN syntax to MySQL JOIN syntax... Yes, I've read the docs on JOIN syntax, and search the list archives for similar information. I have this query: SELECT leg_activity.activity_date, leg_transitions.trans_desc, leg_text.leg_version, body_list.body_name FROM (((leg_version_hist RIGHT JOIN (leg_comm_hist RIGHT JOIN leg_activity ON leg_comm_hist.leg_activity_id = leg_activity.id) ON leg_version_hist.leg_activity_id = leg_activity.id) LEFT JOIN leg_text ON leg_version_hist.leg_text_id = leg_text.id) INNER JOIN leg_transitions ON leg_activity.transition_id = leg_transitions.id) LEFT JOIN body_list ON leg_comm_hist.body_list_id = body_list.id WHERE leg_activity.leg_header_id = 8 That was generated by Access's query designer. It works under Access, but passing to MySQL through ODBC, Access generates four or five queries to do the job. This make it a very long query (1 or 2 seconds), not appropriate for browsing through records. Trying to give this directly to MySQL generates an error: You have an error in your SQL syntax near '(leg_comm_hist RIGHT JOIN leg_activity ON leg_comm_hist.leg_ac' at line 2. I understand JOIN's, at least in concept, but not well enough to construct manually. Is there a tool out there similar to Access's query designer that will design MySQL compatible queries? Are there rules for convert Access queries to MySQL that I should be aware of? Thanks for any help you can give. -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - 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: Conversion from Access JOIN syntax to MySQL JOIN syntax...
Thanks! I was able to work on the query today, and manually assemble a query that did the same thing. And thus, was a lot faster. The main contention MySQL seems to have is the nested joins, such as when Access does this: FROM (((leg_version_hist RIGHT JOIN (leg_comm_hist RIGHT JOIN leg_activity ON leg_comm_hist.leg_activity_id = leg_activity.id)... For some reason MySQL doesn't like that. :) It's expecting a table name, not another JOIN. At least that what it seems to me, but I could be wrong, not being very experienced with joins. My query in MySQL ended being a lot clean looking, at least to me: SELECT leg_activity.activity_date, leg_transitions.trans_desc, leg_text.leg_version, body_list.body_name FROM leg_activity LEFT JOIN leg_comm_hist ON leg_activity.id = leg_comm_hist.leg_activity_id LEFT JOIN body_list ON leg_comm_hist.body_list_id = body_list.id LEFT JOIN leg_version_hist ON leg_activity.id = leg_version_hist.leg_activity_id LEFT JOIN leg_text ON leg_version_hist.leg_text_id = leg_text.id LEFT JOIN leg_transitions ON leg_activity.transition_id = leg_transitions.id WHERE leg_activity.leg_header_id = 8 And it works very well. I'm happy. :) Thanks for the pointers! j- k- On Tuesday 26 June 2001 17:57, Rolf Hopkins wrote: As it is such a big query, I don't have time to look at it for you but it may be easier if you convert the RIGHT JOINS to LEFT JOINS for starters. The manual does recommend LEFT JOINS, mainly for portability. First part would be something like (leg_activity LEFT JOIN leg_comm_hist ON (leg_comm_hist.leg_activity_id = leg_activity.id) LEFT JOIN leg_version_hist ON leg_version_hist.leg_activity_id = leg_activity.id) - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Wednesday, June 27, 2001 2:47 Subject: Conversion from Access JOIN syntax to MySQL JOIN syntax... Yes, I've read the docs on JOIN syntax, and search the list archives for similar information. I have this query: SELECT leg_activity.activity_date, leg_transitions.trans_desc, leg_text.leg_version, body_list.body_name FROM (((leg_version_hist RIGHT JOIN (leg_comm_hist RIGHT JOIN leg_activity ON leg_comm_hist.leg_activity_id = leg_activity.id) ON leg_version_hist.leg_activity_id = leg_activity.id) LEFT JOIN leg_text ON leg_version_hist.leg_text_id = leg_text.id) INNER JOIN leg_transitions ON leg_activity.transition_id = leg_transitions.id) LEFT JOIN body_list ON leg_comm_hist.body_list_id = body_list.id WHERE leg_activity.leg_header_id = 8 That was generated by Access's query designer. It works under Access, but passing to MySQL through ODBC, Access generates four or five queries to do the job. This make it a very long query (1 or 2 seconds), not appropriate for browsing through records. Trying to give this directly to MySQL generates an error: You have an error in your SQL syntax near '(leg_comm_hist RIGHT JOIN leg_activity ON leg_comm_hist.leg_ac' at line 2. I understand JOIN's, at least in concept, but not well enough to construct manually. Is there a tool out there similar to Access's query designer that will design MySQL compatible queries? Are there rules for convert Access queries to MySQL that I should be aware of? Thanks for any help you can give. -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - 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 -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - 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
Left Join Syntax
Here it goes. This is a small schema of the two tables. Table Employees i_recid = auto increment UNIQUE KEY First Name Last Name ETC. Table Contracts sales_rep = e.i_recid Contract Date = date of contract Office = office So here is what i am trying to do. Run a report that calulates Who had a sale between adate (2001-04-05) and bdate (2001-05-05) AND that report can be in any number range specified = 8 (in other words less than 8 contracts written) in order of count (7,6,5,4,3,2,1,0). I need it to show 0, that is the main problem. It isn't showing zeros becuase before i was doing a SELECT directly from the contracts table. The client is insisting on having the ZERO though and it has to stay in order. Below is a sample SQL query, i have tried a few others but i am stuck. ilist search=SELECT count(c.sales_rep) as cnt, concat(e.last_name,', ',e.first_name) as full_name, c.office, c.sales_rep, e.i_recid FROM employee e LEFT JOIN contracts c on e.i_recid=c.sales_rep WHERE c.install_date='@date(-NM-ND)' ieval @input(office,0) ne 0and c.office=@input(office)/ieval GROUP by c.office, c.sales_rep having count(c.sales_rep) @input(operation) @input(count,0) ORDER BY cnt desc Ignore the ilist (sql) and ieval (perl's IF THEN) they are a dynamic way of embedding sql and perl code into the html. The big problem is that i have been unable to join the tables because of the WHERE conditions. I am not sure if the subqueries are even supported by mysql. Will creating a temporary table using a LEFT JOIN to collect the information work? Or is there an easier way that i have overlooked. Dan
Re: Join syntax
I don't know what you are trying to accomplish but here is the way I would build a table using ALL the data field from two tables. first is a straight join which will only build the records that have an equal in both tables. insert into tbla select distinct tblb.*, tblc.* from tblb, tblc where tblb.flda = tblc.flda the second will build records for every record in tblb and will include data from tblc when there is a corresponding record in tblc. When there is no corresponding record in tblc the tblc data fields will be set to null. insert into tbla select distinct tblb.*, tblc.* from tblb left join tblc on tblb.flda = tblc.flda hope this helps. Jack :-)= - Original Message - From: "Willie Klein" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 06, 2001 2:20 PM Subject: Join syntax Hi all: I think what I want to do is join tables in a select statement but I'm not having any luck. I'm using MySQL 3.22 PHP and apache on RH6.2 I have an application that has multiple tables that customers upload order data to. People who look at this data can see their orders from each table individually. Now they want to download their data from each of these tables into their own database. When I do a select on my test table like: Select * into outfile /home/temp/test.txt from apple where RepID = NYS I get a file that is 150K. When I do a select on 2 of my test tables(which are identical)like: Select * into outfile /home/temp/test.txt from apple as t1, bass as t2 where t1.RepID = 'NYS' and t2.RepID='NYS' I get a file that is 116MB with each record being repeated 866 times. When I do a left join it does about the same thing. I'm going to want to do this with more than 2 tables so is my approach to this wrong or just my syntax. I've read the manual page on join and (I admit it) I don't understand it. Thanks for your help willie - 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
