To join or not to join?
I need a single row from 2 different tables. It is rather trivial to create a join that will join these two tables and give me all the information I want in one query. It is also fairly easy to just execute two different queries with out any join and get the data I need. Since the both single table queries or the join query will always only return a single row, I was wondering if there was a performance hit doing it one way or the other. On one table the where clause is on the primary key on the other table there where clause is on a single column that is indexed but not unique. However in this situation it will be unique. I can't put a unique key on this field in the second table because there are other applications of the second table where I do need it to non unique. Hope that makes sense. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: to join or not to join, that is the query
Miguel Vaz wrote: I have three tables: TABLE Person - id_person, name, id_levelA, id_sizeA, id_levelB, id_sizeB TABLE Levels - id, desc TABLE Sizes - id, desc Hi! You can always join a table twice :) SELECT p.id_person , lA.desc as levelA , sA.desc as sizeA , lB.desc as levelB , sB.desc as sizeB FROM Person p INNER JOIN Levels lA ON p.id_levelA = lA.id INNER JOIN Levels lB ON p.id_levelB = lB.id INNER JOIN Sizes sA ON p.id_sizeA = sA.id INNER JOIN Sizes sB ON p.id_sizeB = sB.id; Of course, if id_levelA field is NULLable, you would use a LEFT JOIN instead of an INNER JOIN. Here is an example output: mysql CREATE TABLE Person ( - id_person INT UNSIGNED NOT NULL - , name VARCHAR(20) NOT NULL - , id_levelA TINYINT UNSIGNED NOT NULL - , id_sizeA TINYINT UNSIGNED NOT NULL - , id_levelB TINYINT UNSIGNED NOT NULL - , id_sizeB TINYINT UNSIGNED NOT NULL - , PRIMARY KEY (id_person) - ); Query OK, 0 rows affected (0.07 sec) mysql INSERT INTO Person VALUES (1, 'Miguel', 1, 1, 2, 2); Query OK, 1 row affected (0.04 sec) mysql CREATE TABLE Levels ( id TINYINT UNSIGNED NOT NULL , `desc` VARCHAR(20) NOT NULL , PRIMARY KEY (id) ); Query OK, 0 rows affected (0.06 sec) mysql CREATE TABLE Sizes ( id TINYINT UNSIGNED NOT NULL , `desc` VARCHAR(20) NOT NULL , PRIMARY KEY (id) ); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO Levels VALUES (1, 'Level One'),(2, 'Level Two'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql INSERT INTO Sizes VALUES (1, 'Size One'),(2, 'Size Two'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT - p.id_person - , lA.desc as levelA - , sA.desc as sizeA - , lB.desc as levelB - , sB.desc as sizeB - FROM Person p - INNER JOIN Levels lA - ON p.id_levelA = lA.id - INNER JOIN Levels lB - ON p.id_levelB = lB.id - INNER JOIN Sizes sA - ON p.id_sizeA = sA.id - INNER JOIN Sizes sB - ON p.id_sizeB = sB.id; +---+---+--+---+--+ | id_person | levelA| sizeA| levelB| sizeB| +---+---+--+---+--+ | 1 | Level One | Size One | Level Two | Size Two | +---+---+--+---+--+ 1 row in set (0.00 sec) A couple notes for you: 1) desc is a keyword, so I would not recommend using it as a field name. Use something like description instead to make your life easier 2) This kind of table structure is typically an indication of a poorly designed schema because it is not normalized. You should instead have a table, PersonLevels and PersonSizes, which can store any number of a person's levels and sizes... read up on normalization about this concept. Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
to join or not to join, that is the query
Hi, i am having some difficulty to write a query for the following problem: I have three tables: TABLE Person - id_person, name, id_levelA, id_sizeA, id_levelB, id_sizeB TABLE Levels - id, desc TABLE Sizes - id, desc I need a query that returns everything from the Person table, replacing the id_levelA... with the desc from the tables Levels and Sizes. I can get a result with one of them replaced using JOIN, but not several replacements using the same reference tables (levels and sizes). :-P Heres what i need as a result: - id_person, name, descA, sizeA, descB, sizeB descA, etc, being the id_levelA, etc replaced, and i assume i need to give it a new name to fetch the results, right? Can you guys point me in the right direction? Thanks! Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
On 3/21/06, Robert DiFalco wrote: I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If your benchmark shows it it must be true :) But is it a significant difference? Over how many test runs? And is it worth the risk that for some join it is actually much slower. If so, it this peculiar to MySQL or would this be true with almost all database servers? Hard to tell. If you want a generic answer the way to go wouls be to start digging in the MySQL sourcecode to find out why it is faster. Once you know that, you can extrapolate to other databases. The only mechanism I can imagine is that you are constraining the planner so for a join between N tables you go from N! plans to (N-1)! plans which will save you a miniscule amount on the planning time for a small join, and somwhat more on a big join. (Does the MySQL planner do an exhaustive search?) The downside is that the plan might be significantly worse so you can loose big time in the executor. Especially considering the semantic difference between both syntaxes I would never use this as a way to constrain the planner. Jochem
Re: RIGHT JOIN better than INNER JOIN?
I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. Use the join that is appropriate for your query, do not use a different one. If speed isn't OK, then bug the guys that do the implementation, but don't start changing the query that _might_ return a different result if, for example, someone else starts working at the application and figures hey, this is a right join, so it's optional etc ... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
- Original Message - From: Jochem van Dieten [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 21, 2006 3:53 AM Subject: Re: RIGHT JOIN better than INNER JOIN? On 3/21/06, Robert DiFalco wrote: I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If your benchmark shows it it must be true :) But is it a significant difference? Over how many test runs? And is it worth the risk that for some join it is actually much slower. If so, it this peculiar to MySQL or would this be true with almost all database servers? Hard to tell. If you want a generic answer the way to go wouls be to start digging in the MySQL sourcecode to find out why it is faster. Once you know that, you can extrapolate to other databases. No, you can't. Other databases use different optimizers, i.e. different algorithms to choose the access path. Even if you inspected 100 or 1000 or 1,000,000 Fords and found every one of them to have rear-wheel drive, it wouldn't guarantee that BMWs or Hondas had rear-wheel drive, they might have front-wheel drive or all-wheel drive. The specific things you learn about MySQL's optimization technique cannot be extrapolated to other databases unless they are using the same optimization techniques. The only mechanism I can imagine is that you are constraining the planner so for a join between N tables you go from N! plans to (N-1)! plans which will save you a miniscule amount on the planning time for a small join, and somwhat more on a big join. (Does the MySQL planner do an exhaustive search?) The downside is that the plan might be significantly worse so you can loose big time in the executor. Especially considering the semantic difference between both syntaxes I would never use this as a way to constrain the planner. Jochem -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RIGHT JOIN better than INNER JOIN?
Robert, A RIGHT JOIN would potentially return more results than an INNER JOIN. I do not see how it could be, in the absolute, be faster. How large were the tables and how did you do your testing? What platform did you use and were you InnoDB or MyISAM? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Monday, March 20, 2006 5:12 PM To: mysql@lists.mysql.com Subject: RIGHT JOIN better than INNER JOIN? I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- 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: RIGHT JOIN better than INNER JOIN?
Martjin, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 1:01 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. Use the join that is appropriate for your query, do not use a different one. If speed isn't OK, then bug the guys that do the implementation, but don't start changing the query that _might_ return a different result if, for example, someone else starts working at the application and figures hey, this is a right join, so it's optional etc ... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- 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: RIGHT JOIN better than INNER JOIN?
Robert, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. Gee, I wonder why ... This happens to be the query that returns the rows as it should. What happens, if two years from now you didn't document WHY you wrote a right join query instead of an inner join and someone figures out that this could return nulls for a result column? If you start using the wrong joins, you will make things harder on yourself and others. As I said: if performance isn't satisfactory (which sounds a bit strange for this situation), then try to solve that. Either by using different index/buffer/caching strategies or by complaining to the people who created the database system in the first place. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RIGHT JOIN better than INNER JOIN?
For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an inappropriate join or that I am asking the database server developers to improve their query optimizer. I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 9:43 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? Robert, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. Gee, I wonder why ... This happens to be the query that returns the rows as it should. What happens, if two years from now you didn't document WHY you wrote a right join query instead of an inner join and someone figures out that this could return nulls for a result column? If you start using the wrong joins, you will make things harder on yourself and others. As I said: if performance isn't satisfactory (which sounds a bit strange for this situation), then try to solve that. Either by using different index/buffer/caching strategies or by complaining to the people who created the database system in the first place. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- 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: RIGHT JOIN better than INNER JOIN?
Robert DiFalco wrote: For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an inappropriate join or that I am asking the database server developers to improve their query optimizer. I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. You have not given enough information to even make a guess. Show the create tables for each table, and the output of explain for each query to see what keys are being used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
Robert, Your restatement of your original question uses FULL JOIN as if it means the same things as INNER JOIN: that's simply not correct. A full join contains the results of an inner join PLUS the orphan rows from the right-hand table in the join PLUS the orphan rows from the left-hand table in the join. Furthermore, the last time I checked, which was probably at least a year ago now, MySQL didn't support a full join. -- Rhino - Original Message - From: Robert DiFalco [EMAIL PROTECTED] To: Martijn Tonies [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, March 21, 2006 2:04 PM Subject: RE: RIGHT JOIN better than INNER JOIN? For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an inappropriate join or that I am asking the database server developers to improve their query optimizer. I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 9:43 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? Robert, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. Gee, I wonder why ... This happens to be the query that returns the rows as it should. What happens, if two years from now you didn't document WHY you wrote a right join query instead of an inner join and someone figures out that this could return nulls for a result column? If you start using the wrong joins, you will make things harder on yourself and others. As I said: if performance isn't satisfactory (which sounds a bit strange for this situation), then try to solve that. Either by using different index/buffer/caching strategies or by complaining to the people who created the database system in the first place. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- 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] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RIGHT JOIN better than INNER JOIN?
I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
The only relational databases I've ever used to any significant extent are MySQL and DB2. I've used DB2 for a lot longer than MySQL and on most of the platforms on which it runs over various versions. As far as I'm concerned, the answer to your questions, at least as far as DB2 goes, is: it depends. It depends on a host of factors. In no particular order, these factors include: - which version of DB2 you are using - what hardware you are running on - how you write your SQL - whether the data is properly clustered - whether the tables and indexes have been reorganized in a timely fashion - etc. etc. You simply can't make a categorical statement that a right join will perform better than an inner join - or vice versa - in every case in DB2. All versions of DB2 use a cost-based optimizer that makes great efforts to give the optimum access path (and therefore optimum performance) for each query. A lot of very smart people have worked on the design of that optimizer over the years - I've met some of them - but, as good as the DB2 optimizer is, it can still make inappropriate decisions. This happens when you don't do routine maintenance like reorganizing tables and the RUNSTATS utility but the way you write (or mis-write) your SQL can also affect your access path and therefore your performance. This unpredictability may sound like a bad thing but it is often a very good thing since the optimizer has many tricks and shortcuts. It will often rewrite a poorly-written query to improve its performance. You may be able to find more categorical answers for the other major databases, like Oracle, since they tend to use different optimizer designs. The only way to be really sure though is to do a proper benchmark for all the platforms and configurations that interest you. -- Rhino - Original Message - From: Robert DiFalco [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 20, 2006 7:11 PM Subject: RIGHT JOIN better than INNER JOIN? I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to reference right join table in left join statement under windows...but works under linux
I am using MySQL 5.0.15 on windows and cannot run this query: SELECT a.*,b.name, c.fullname,d.fullname FROM access_authorization a, building b LEFT JOIN users c ON a.createdby=c.id LEFT JOIN users d ON a.modifiedby=d.id WHERE a.sortcode=b.sortcode AND a.sortcode like '1,2,1,6%' LIMIT 0, 25 I receive: ERROR 1054 (42S22): Unknown column 'a.createdby' in 'on clause' But this query DOES work under my linux mysql 5.0.0-alpha! I can modify the SQL Statement to the following and it works fine: SELECT a.*,b.name, c.fullname,d.fullname FROM access_authorization a LEFT JOIN users c ON a.createdby=c.id LEFT JOIN users d ON a.modifiedby=d.id RIGHT JOIN building b ON a.sortcode=b.sortcode WHERE a.sortcode like '1,2,1,6%' LIMIT 0, 25 But I have a lot of SQL statements like this and I do not want to have to modify them all. Does anyone have any ideas on what is wrong? I've been able to reproduce the problem with some generic tables, so I wont include my table definitions here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to reference right join table in left join statement under windows...but works under linux
Ryan Allbaugh [EMAIL PROTECTED] wrote on 12/19/2005 04:41:39 PM: I am using MySQL 5.0.15 on windows and cannot run this query: SELECT a.*,b.name, c.fullname,d.fullname FROM access_authorization a, building b LEFT JOIN users c ON a.createdby=c.id LEFT JOIN users d ON a.modifiedby=d.id WHERE a.sortcode=b.sortcode AND a.sortcode like '1,2,1,6%' LIMIT 0, 25 I receive: ERROR 1054 (42S22): Unknown column 'a.createdby' in 'on clause' But this query DOES work under my linux mysql 5.0.0-alpha! I can modify the SQL Statement to the following and it works fine: SELECT a.*,b.name, c.fullname,d.fullname FROM access_authorization a LEFT JOIN users c ON a.createdby=c.id LEFT JOIN users d ON a.modifiedby=d.id RIGHT JOIN building b ON a.sortcode=b.sortcode WHERE a.sortcode like '1,2,1,6%' LIMIT 0, 25 But I have a lot of SQL statements like this and I do not want to have to modify them all. Does anyone have any ideas on what is wrong? I've been able to reproduce the problem with some generic tables, so I wont include my table definitions here. What is wrong is that the all versions of MySQL before 5.0.12 were buggy in their evaluation of implicit CROSS JOINs (comma separated lists of table names) in combination with explicit JOINS (INNER JOIN...ON..., LEFT JOIN...ON..., RIGHT JOIN...ON..., etc). In order to get MySQL to operate as specified by the SQL:2003 specification, the comma operator (what you are using to create your implicit CROSS JOIN) was demoted in evaluation precedent. This change happened with 5.0.12 and is thoroughly documented here: http://dev.mysql.com/doc/refman/5.0/en/join.html Unfortunately you are going to need to edit your queries to bring them in line with the SQL standard in order to achieve their previous functionality. May I suggest to move to explicit JOIN statements and stop using the commas to create lists of tables? RANT HEY Documentation team!! This is about the 500th example this year of someone using that dang-blasted comma-separated list format to make JOINS who is having problems with their query. Can you PLEASE fix *all* the examples in the manual to NOT use this form except towards the bottom of certain pages where you could describe it as an option along with all of the baggage it now carries. The implicit CROSS JOIN catches another one! /RANT Shawn Green Database Administrator Unimin Corporation - Spruce Pine
another left join question - multiple left join statements
my test tbls cattbl dogtbl birdtbl namename name id --- catid --- dogid id id so dogtbl.catid = cattbl.id birdtbl.dogid = dogtbl.id my question is how i can use left joins to produce the results set with the names of all three cat/dog/bird... i've tried various derivatives of the following... mysql select cattbl.name as cat, - dogtbl.name as dog, birdtbl.name as bird - from dogtbl - left join cattbl on cattbl.id=dogtbl.catid - from birdtbl - left join dogtbl on birdtbl.dogid=dogtbl.id; i keep getting an error complaining about the 2nd from/left join... i know how to get the results using where/and logic... but i'm trying to get a better feel of the left join process... after looking at mysql/google, i'm still missing something... any comments/criticisms appreciated.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another left join question - multiple left join statements
On Monday 05 July 2004 12:28 pm, bruce wrote: my test tbls cattbl dogtbl birdtbl namename name id --- catid --- dogid id id so dogtbl.catid = cattbl.id birdtbl.dogid = dogtbl.id my question is how i can use left joins to produce the results set with the names of all three cat/dog/bird... i've tried various derivatives of the following... mysql select cattbl.name as cat, - dogtbl.name as dog, birdtbl.name as bird - from dogtbl - left join cattbl on cattbl.id=dogtbl.catid - from birdtbl - left join dogtbl on birdtbl.dogid=dogtbl.id; i keep getting an error complaining about the 2nd from/left join... ... -bruce It's not really clear from the manual, but if you check the basic syntax of the SELECT statement: http://dev.mysql.com/doc/mysql/en/SELECT.html and the JOIN: http://dev.mysql.com/doc/mysql/en/JOIN.html you'll see that all the tables references (the FROM and the JOINS) go in a single spot in the SELECT statement: SELECT column list FROM table references WHERE conditions. So there should only be a single FROM followed first by the tables in the basic select (including any inner join) and then by any LEFT or RIGHT JOINs you wish to add. Generally, you only reference each table one time. select cattbl.name, dogtbl.name, birdtbl.name from dogtbl left join cattbl on cattbl.id=dogtbl.catid this is wrong (see comments above) - from birdtbl left join anothertablenametoaddtoyourquery on birdtbl.dogid=dogtbl.id; Good luck, Joihn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another left join question - multiple left join statements
You only need to specify from tabel on the first table. Like this. select cattbl.name as cat, dogtbl.name as dog, birdtbl.name as bird from dogtbl left join cattbl on cattbl.id=dogtbl.catid left join birdtbl on birdtbl.dogid=dogtbl.id; -Eric On Mon, 5 Jul 2004 09:28:02 -0700, bruce [EMAIL PROTECTED] wrote: my test tbls cattbl dogtbl birdtbl namename name id --- catid --- dogid id id so dogtbl.catid = cattbl.id birdtbl.dogid = dogtbl.id my question is how i can use left joins to produce the results set with the names of all three cat/dog/bird... i've tried various derivatives of the following... mysql select cattbl.name as cat, - dogtbl.name as dog, birdtbl.name as bird - from dogtbl - left join cattbl on cattbl.id=dogtbl.catid - from birdtbl - left join dogtbl on birdtbl.dogid=dogtbl.id; i keep getting an error complaining about the 2nd from/left join... i know how to get the results using where/and logic... but i'm trying to get a better feel of the left join process... after looking at mysql/google, i'm still missing something... any comments/criticisms appreciated.. thanks -bruce -- 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]
Left outer join combined with inner join
Hi, with the tables eg: Product: id, product, cost ProductTag: productId, TagId Tag: id, tag I have created the ability to selectively assign tags to products if required via a many-to-many relationship. Now I want to join the tables in MySQL so that I can see all products and in the tag column they should have a tag if there is one. I can do the left outer join to join the product and many-to-many table ProductTag: select Product.id, ProductTag.TagId from Product left join ProductTag on Product.id = ProductTag.productId but how do I then, in the same, select statement describe the join between ProductTag and Tag to produce the columns: Product.product, Product.cost, Tag.tag where most Tag.tag fields will not have values. I would hugely appreciate any assistance in this regard. Matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left outer join combined with inner join
Matthew Shalorne wrote: Hi, with the tables eg: Product: id, product, cost ProductTag: productId, TagId Tag: id, tag I have created the ability to selectively assign tags to products if required via a many-to-many relationship. Now I want to join the tables in MySQL so that I can see all products and in the tag column they should have a tag if there is one. I can do the left outer join to join the product and many-to-many table ProductTag: select Product.id, ProductTag.TagId from Product left join ProductTag on Product.id = ProductTag.productId select Product.id,Product.cost,Tag.tag from product left join ProductTag on Product.id=ProductTag.productid left join Tag on ProductTag.TagId=Tag.id but how do I then, in the same, select statement describe the join between ProductTag and Tag to produce the columns: Product.product, Product.cost, Tag.tag where most Tag.tag fields will not have values. I would hugely appreciate any assistance in this regard. Matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left outer join combined with inner join
Hi Matthew, There are several combination of things you can query for with the data you have: Only Products with Tags, Only Tags with products, all Products with or without Tags, all Tags with or without Products However you do not have the ability (yet) to query, in a single statement, for all Products and all Tags (having data all across where they match-up and having nulls in the columns where they don't until the query engine supports the FULL OUTER JOIN clause. Now, there IS a work-around using a LEFT JOIN UNIONed to a RIGHT JOIN but I don't know what version of MySQL you have. You may have to use a different workaround that requires a temporary table. Can you give us an example of the columns you want to have in your report? Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matthew Shalorne [EMAIL PROTECTED]To: [EMAIL PROTECTED] co.uk cc: Fax to: 06/01/2004 04:33 Subject: Left outer join combined with inner join PM Hi, with the tables eg: Product: id, product, cost ProductTag: productId, TagId Tag: id, tag I have created the ability to selectively assign tags to products if required via a many-to-many relationship. Now I want to join the tables in MySQL so that I can see all products and in the tag column they should have a tag if there is one. I can do the left outer join to join the product and many-to-many table ProductTag: select Product.id, ProductTag.TagId from Product left join ProductTag on Product.id = ProductTag.productId but how do I then, in the same, select statement describe the join between ProductTag and Tag to produce the columns: Product.product, Product.cost, Tag.tag where most Tag.tag fields will not have values. I would hugely appreciate any assistance in this regard. Matthew -- 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]
To join or not to join
I am looking for an expert opinion on the speed difference between fetching related data from 2 tables with a join and fetching them in to single selects. The scenario is kind of the following: SELECT a , b, c FROM table1 WHERE a='x'; # gets b='y' SELECT b, d , e, f FROM table2 WHERE b='y'; instead SELECT a , b, c, d , e, f FROM table1, table2 WHERE a='x' AND table1.b = table2.b; Background: I wrote a little Perl module that automatically instantiates a object for each table in the database connected to and each table object allows you to access any record in that table or create a new one. So the above SQL looks like: my $DB = DB-new($config); my $record_a_b_c = $DB-table1-new(primary_key_value); my $field_b_value = $record_a_b_c-fieldname; my $record_b_d_e_f = $DB-table2-new($field_b_value); In this scenario it very easy to retrieve related from several tables without doing a join, but I am not sure how hard the performance hit actually is, since MySQL would have to look up the first select before it can do the join on the second table. Thanks for your input. /h mysql, query, table - 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: To join, or not to join?
On Fri, Nov 22, 2002 at 08:13:46PM -0500, Philip Mak wrote:
SELECT *
FROM boards
LEFT JOIN boardAdmins
ON boardAdmins.userId = #{userId}
AND boardAdmins.boardId = boards.id
LEFT JOIN boardMembers
ON boardMembers.userId = #{userId}
AND boardMembers.boardId = boards.id
AND boards.id = #{boardId}
Part of your problem is that you're not quite normalized; if you had
a table boardUsers with a SET field of admins|members then it would
be easier to show ... however, I'd do:
SELECT *
FROM boards
LEFT JOIN boardAdmins
ON boardAdmins.boardId = boards.id
LEFT JOIN boardMembers
ON boardMembers.boardId = boards.id
WHERE boards.id = #{boardId}
AND boardAdmins.userId = #{userId}
AND boardMembers.userId = #{userId}
See how the ON clauses in the QUERY all refer the current table back
to the previous table? I'm not sure about parentheses, but this should
work (untested).
--
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc)
http://www.fibrespeed.net/~mbabcock/
-
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
To join, or not to join?
sql,query Which way is faster? Way 1: SELECT * FROM users LEFT JOIN boardAdmins ON boardAdmins.userId = users.id LEFT JOIN boardMembers ON boardMembers.userId = users.id WHERE id = 5; Way 2: SELECT * FROM users WHERE id = 5; SELECT * FROM boardAdmins WHERE userId = 5; SELECT * FROM boardMembers WHERE userId = 5; (Note that all of these SELECT statements only retrieve a single row, since the primary keys are users.id, boardAdmins.userId and boardMembers.userId.) The second way probably is going to have more latency between the client and the database server. - 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: To join, or not to join?
On Fri, Nov 22, 2002 at 06:20:14PM -0500, Philip Mak wrote: sql,query Why not just: SELECT * FROM users, boardAdmins, boardMembers WHERE id = 5; You're not really 'joining', since boardAdmins and boardMembers don't have the structure JOINs are made for (it seems). -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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: To join, or not to join?
On Fri, Nov 22, 2002 at 06:56:53PM -0500, Michael T. Babcock wrote:
On Fri, Nov 22, 2002 at 06:20:14PM -0500, Philip Mak wrote:
sql,query
Why not just:
SELECT * FROM users, boardAdmins, boardMembers WHERE id = 5;
You're not really 'joining', since boardAdmins and boardMembers
don't have the structure JOINs are made for (it seems).
Oops! I was typing my example from memory, and did it wrong.
Sorry, it's supposed to be like this:
SELECT *
FROM boards
LEFT JOIN boardAdmins
ON boardAdmins.userId = #{userId}
AND boardAdmins.boardId = boards.id
LEFT JOIN boardMembers
ON boardMembers.userId = #{userId}
AND boardMembers.boardId = boards.id
AND boards.id = #{boardId}
For each entry in boards, there are zero or more corresponding
entries in boardAdmins and boardMembers.
The above could be rewritten with 3 separate SELECT statements:
SELECT *
FROM boards
WHERE id = #{boardId}
SELECT *
FROM boardMembers
WHERE userId = #{userId}
AND boardId = #{boardId}
SELECT *
FROM boardAdmins
WHERE userId = #{userId}
AND boardId = #{boardId}
So, I'm wondering which way would be faster.
-
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: LEFT JOIN fails to correctly join tables
[EMAIL PROTECTED] writes: Description: It seems that that the use of LEFT JOIN when the joined table uses multiple primary key conditions fails to include rows which are clearly matching on those conditions. How-To-Repeat: mysql create table foo (fooID smallint unsigned auto_increment, primary key (fooID)); Query OK, 0 rows affected (0.08 sec) mysql create table foobar (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID)); Query OK, 0 rows affected (0.05 sec) mysql insert into foo (fooID) values (10),(20),(30); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql insert into foobar values (10,1),(20,2),(30,3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from foobar fb left join foo f on f.fooID = fb.fooID and f.fooID = 30; +---+---+---+ | fooID | barID | fooID | +---+---+---+ |10 | 1 | NULL | |20 | 2 | NULL | |30 | 3 | NULL | +---+---+---+ 3 rows in set (0.00 sec) Thank you for the repeatable test case. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
Re: LEFT JOIN fails to correctly join tables
On 1-Dec-2001 16:49 Sinisa Milivojevic wrote: | | Thank you for the repeatable test case. I've just tested this with 3.23.46 and it still fails. regards. -- -Dale - 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
