Confused about syntax for specific join with 3 tables
I've been doing some experimenting with the data model from the MySQL
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sexENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this logically, I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as student joining to score joining to grade_event.
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to change where NULL values sort?
I think normally NULL values will sort at the end, correct? I believe there's a way to make NULL values sort at the beginning, but I can't remember how to do it. I just searched a couple of MySQL resources, but I couldn't find it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to set db property so that table name queries are case-insensitive?
Ubuntu 8.10. I was experimenting with the Spring Petclinic sample application, configured with MySQL. I found that some of the tests were failing, apparently because of table name case-sensitivity issues. I was able to fix some of the code references, but after that I hit other, probably for the same issue. The tables were created with lower case names, but generated queries are using uppercase names. I can't easily control how it generates the SQL (using Hibernate). I think I'd prefer to have table names be case-sensitive, but I guess for now I have to figure out how to disable that, at least for this database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: W2008 Server Issues?
Duncan, Kurt (MCUSA) wrote: 1. Any issues running MySQL Ver. X on Windows 2008 Server? 2. Running a query to list all records of about 2600 records is very slow and gets an error of Extremely High Work Load, Each record does contain 1 LONGBLOB containing small voice recording. We are using the SQL Query Browser and running the same query and same database on a 2000 server 1 gig less memory runs the query in about 15 seconds with no issues. Is this an issue with MySQL or the 2008 server? I'm no expert, so I'm not going to comment on the environmental issues. You'd likely get much more predictable performance if you re-engineer your tables so that the BLOB is in a separate table from the main entity, with a FK from there. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best mysql optimization tutorial and/or quick start guide?
John G. Heim wrote: - Original Message - From: Darryle Steplight dstepli...@gmail.com To: Stephen Edberg sbedb...@ucdavis.edu Cc: John G. Heim jh...@math.wisc.edu; mysql@lists.mysql.com Sent: Thursday, March 05, 2009 12:53 PM Subject: Re: best mysql optimization tutorial and/or quick start guide? High Performance MySql Optimization, Backups, Replication, and more 2nd Edition . Got a problem, pick a chapter and read the solution. This book is awesome, I'm confident you will find what you are looking for :) . Excellent tip. It's exactly what I'm looking for. Well, I guess I wouldn't exactly call it a quick start guide. But by chance, it happened to be on bookshare.org. This is a web site which, if you're blind, you can subscribe to to download e-books. I'm already subscribed so I didn't have to pay any additional price for this particular book. Must be my lucky day. This book is also available on the Safari edition associated with ACM memberships. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query from HPM book to limit rows scanned doesn't appear to work as described
Baron Schwartz wrote: Hi! On Wed, Mar 11, 2009 at 8:20 PM, David M. Karr davidmichaelk...@gmail.com wrote: Using 5.0.67-0ubuntu6 on Ubuntu 8.10. I'm going through the High Performance MySQL book. I was reading section 4.4.1.8, titled MIN() and MAX(). The point of this is that MySQL doesn't optimize MIN()/MAX() very well, but it showed a supposed workaround for this. The first sample query was: SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE'; As described, this does a table scan, looking at 200 rows. The alternative was this: SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' LIMIT 1; Which supposedly would not do a full table scan, and it seems logical. The explain output for this is the following (tabs replaced with colon): id:select_type:table:type:possible_keys:key:key_len:ref:rows:Extra 1:SIMPLE:actor:ALL:null:null:null:null:200:Using where This explain output is identical to the output for the previous query, so this workaround didn't appear to help any. But EXPLAIN is only a prediction. If you look at the changes in the Handler status variables, you'll see the second one reads fewer rows. Ok, I think I get it. I first changed both of my queries to add sql_no_cache because without that, the Handler_read_rnd_next variable was zero in both cases. Before running each query, I ran flush status, then the query, then show session status like 'Handler%'. The first one had a value of 207 for Handler_read_rnd_next and the second one had a value of 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query from HPM book to limit rows scanned doesn't appear to work as described
Using 5.0.67-0ubuntu6 on Ubuntu 8.10. I'm going through the High Performance MySQL book. I was reading section 4.4.1.8, titled MIN() and MAX(). The point of this is that MySQL doesn't optimize MIN()/MAX() very well, but it showed a supposed workaround for this. The first sample query was: SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE'; As described, this does a table scan, looking at 200 rows. The alternative was this: SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' LIMIT 1; Which supposedly would not do a full table scan, and it seems logical. The explain output for this is the following (tabs replaced with colon): id:select_type:table:type:possible_keys:key:key_len:ref:rows:Extra 1:SIMPLE:actor:ALL:null:null:null:null:200:Using where This explain output is identical to the output for the previous query, so this workaround didn't appear to help any. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: high level: enabling transactions in mysql-max with WebLogic TX datasource?
Bert == Bert VdB [EMAIL PROTECTED] writes: Bert Hi, Bert all you have to do use a transaction enabled data-source and use the Bert Mm-MysqlDriver Bert for your JDBC connections. (http://mmmysql.sourceforge.net/) Bert Best is to read the InnoDB documentation carefully (http://www.innodb.com), Bert as you cannot choose from a wide Bert variety of transaction-levels ... in my experience, most of the detailed Bert transaction-handling Bert can only be done at mysql-query level. Bert To test wether your datasource supports the transactions, do a test and look Bert at the InnoDB-logfiles, it should Bert indicate some thing like SET AUTOCOMMIT=0 and COMMIT. Ok, I determined that innodb wasn't being initialized because I didn't have innodb_data_file_path set in my.ini. I added the following line to my.ini in mysqladmin: innodb_data_file_path = ibdata1:30M:autoextend After I restarted the service and created some tables (not adding any rows yet), I noticed the following files in the data subdirectory: --- total 41004 drwxr-xr-x2 dmkarr None 8192 Jul 1 15:01 mysql/ -rw-r--r--1 dmkarr None 2461 Jul 1 14:59 mysql.err -rw-r--r--1 dmkarr None 5242880 Jul 1 14:59 ib_logfile0 -rw-r--r--1 dmkarr None 31457280 Jul 1 14:59 ibdata1 drwxr-xr-x4 dmkarr None 4096 Jul 1 14:59 ./ -rw-r--r--1 dmkarr None25088 Jul 1 14:59 ib_arch_log_00 -rw-r--r--1 dmkarr None 5242880 Jul 1 14:59 ib_logfile1 drwxr-xr-x 11 dmkarr None 4096 Jun 29 11:30 ../ drwxr-xr-x2 dmkarr None0 Jun 29 11:07 test/ --- However, I don't know how to check the log files for those strings, as they appear to be binary. A simple grep didn't find anything. Is there a special viewer for mysql log files? -- === David M. Karr ; Java/J2EE/XML/Unix/C++ [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
URL for detailed description of SQL left/right inner/outer join concepts?
Is there a good URL that explains in detail exactly the concepts of SQL left/right inner/outer join? I've found several that give very high-level hints, but nothing that really summarizes exactly what these do. -- === David M. Karr ; Java/J2EE/XML/Unix/C++ [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
high level: enabling transactions in mysql-max with WebLogic TX datasource?
One thing that I find a little confusing is the notion of transactional behavior in mySQL. I read that it's possible to enable transactions in mySQL-max, however, it's never really been clear to me exactly what I'm supposed to do, or whether I have to do anything. If I'm using a transactional datasource in WebLogic, on top of mySQL, I need to make sure mySQL supports that transactional behavior. Is there a section of the mySQL documentation, or some other documentation, which explains these issues clearly? -- === David M. Karr ; Java/J2EE/XML/Unix/C++ [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
