Re: Query Question
Bill, if you use an order by clause in your query, the limit will pick the first 100K rows in that order. That way you can ensure that all rows will be processed in (wait for it...) order :) Cheers, Walter On Tue, Aug 18, 2009 at 18:44, Bill Arbuckle b...@arbucklellc.com wrote: I am in need of some help for the following: Say I have a table with 1M rows. Users are being added constantly (not deleted) during the queries that I am about to explain. The pk is uid and appid. I need to run queries in increments of 100K rows until reaching the end without duplicating rows in the queries. I am using a select statement with a limit of row_index and row_count. This start row is where my question arises. If I make a query with limit 0,10 then 2 minutes later 10,10 then 2minutes later 30,10 and so on. My question is are new rows added to the end of the table or will they randomly appear in my queries? If they are added to the end of the table, that is fine because I will pick them up in my final pass. I hope this is clear enough. If not, let me know and I will provide more information. Thanks! -- Walter Heck, Engineer @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org
RE: Query Question
To further emphasize this point: A table has no order by itself, and you should make no assumptions about the order of rows you will get back in a select statement, unless you use an ORDER BY clause. Regards, Gavin Towey -Original Message- From: walterh...@gmail.com [mailto:walterh...@gmail.com] On Behalf Of Walter Heck - OlinData.com Sent: Tuesday, August 18, 2009 9:51 AM To: b...@arbucklellc.com Cc: mysql@lists.mysql.com Subject: Re: Query Question Bill, if you use an order by clause in your query, the limit will pick the first 100K rows in that order. That way you can ensure that all rows will be processed in (wait for it...) order :) Cheers, Walter On Tue, Aug 18, 2009 at 18:44, Bill Arbuckle b...@arbucklellc.com wrote: I am in need of some help for the following: Say I have a table with 1M rows. Users are being added constantly (not deleted) during the queries that I am about to explain. The pk is uid and appid. I need to run queries in increments of 100K rows until reaching the end without duplicating rows in the queries. I am using a select statement with a limit of row_index and row_count. This start row is where my question arises. If I make a query with limit 0,10 then 2 minutes later 10,10 then 2minutes later 30,10 and so on. My question is are new rows added to the end of the table or will they randomly appear in my queries? If they are added to the end of the table, that is fine because I will pick them up in my final pass. I hope this is clear enough. If not, let me know and I will provide more information. Thanks! -- Walter Heck, Engineer @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Query Question
To further emphasize this point: A table has no order by itself, That's not entirely true ;-) Records are stored in some kind of physical order, some DBMSses implement clustered keys, meaning that the records are stored ascending order on disk. However... and you should make no assumptions about the order of rows you will get back in a select statement, unless you use an ORDER BY clause. This is correct in that a -result set- does not have an order defined unless you specify an ORDER BY clause. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
Re: Query Question
It may be true that some DBMSs physically store rows in whatever order you speicfy; however, this is a MySQL list, and MySQL does not do this (InnoDB anyway). For example, take a table with 10,000,000 rows and run a simple select on it: Database changed mysql SELECT id FROM trans_item LIMIT 1\G *** 1. row *** id: 8919552 1 row in set (0.08 sec) mysql SELECT id FROM trans_item ORDER BY id ASC LIMIT 1\G *** 1. row *** id: 8441275 1 row in set (0.08 sec) Sure, the first query may always return that ID number; however, it may not. On Tue, Aug 18, 2009 at 2:31 PM, Martijn Tonies m.ton...@upscene.comwrote: To further emphasize this point: A table has no order by itself, That's not entirely true ;-) Records are stored in some kind of physical order, some DBMSses implement clustered keys, meaning that the records are stored ascending order on disk. However... and you should make no assumptions about the order of rows you will get back in a select statement, unless you use an ORDER BY clause. This is correct in that a -result set- does not have an order defined unless you specify an ORDER BY clause. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Query Question
It may be true that some DBMSs physically store rows in whatever order you speicfy; That's not what I said. however, this is a MySQL list, and MySQL does not do this (InnoDB anyway). For example, take a table with 10,000,000 rows and run a simple select on it: Database changed mysql SELECT id FROM trans_item LIMIT 1\G *** 1. row *** id: 8919552 1 row in set (0.08 sec) mysql SELECT id FROM trans_item ORDER BY id ASC LIMIT 1\G *** 1. row *** id: 8441275 1 row in set (0.08 sec) Sure, the first query may always return that ID number; however, it may not. And you're confusing -physical order- (table order) with -result set order- ... With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
Re: query question...
It sounds to me like you want to join the two tables? http://dev.mysql.com/doc/refman/5.1/en/join.html On Mon, Jun 15, 2009 at 03:56, brucebedoug...@earthlink.net wrote: hi. i've got a situation, where i'm trying to figure out how to select an item from tblA that may/maynot be in tblB. if the item is only in tblA, i can easilty get a list of the items select * from tblA if the item is in tblA but not linked to tblB, i can get the items as well select * from tblA where id not in (select id from tblB); but i have no idea how to combine the two selects.. i need to combine them, as the app can create tblA for a given item, and then later on create the data in tblB, with thblA.id = tblB.aid. thoughts/pointers on this would be appreciated. thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- 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 question...
Hi Bruce, bruce wrote: hi. i've got a situation, where i'm trying to figure out how to select an item from tblA that may/maynot be in tblB. if the item is only in tblA, i can easilty get a list of the items select * from tblA if the item is in tblA but not linked to tblB, i can get the items as well select * from tblA where id not in (select id from tblB); but i have no idea how to combine the two selects.. i need to combine them, as the app can create tblA for a given item, and then later on create the data in tblB, with thblA.id = tblB.aid. thoughts/pointers on this would be appreciated. Just as the other reply said, you need a JOIN. Specifically you need one of the OUTER JOIN such as LEFT JOIN or RIGHT JOIN. Here's the pattern for exists in A and is/is not in B (using the fields you specified in your sample) SELECT ... FROM a LEFT JOIN b ON a.id = b.aid Any row where b.id is null would indicate a mismatch (the row exists in A but not in B). So you can test for things like all rows in A that have no match in B SELECT ... FROM a LEFT JOIN b ON a.id = b.aid WHERE b.id IS NULL all rows in B that have no match in A SELECT ... FROM b LEFT JOIN a ON a.id = b.aid WHERE a.id IS NULL - or - SELECT ... FROM a RIGHT JOIN b ON a.id = b.aid WHERE a.id is null only rows from A or B that have a matching row in in the other table SELECT ... FROM a INNER JOIN b ON a.id = b.id All Rows from A and only those matching rows from B where b.datecol '2009-06-13' SELECT ... FROM a LEFT JOIN b ON a.id = b.id AND b.datecol '2009-06-13' - this will NOT work - SELECT ... FROM a LEFT JOIN b ON a.id = b.id WHERE b.datecol '2009-06-13' It will not work because the WHERE clause will filter from the results any rows from A where b.datecol is not '2009-06-13' which would include those rows from A that had no matching row from B (effectively turning the LEFT join into an INNER join). Try it both ways and see. Yours, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- 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 question.
Hi Richard, Richard Reina wrote: I have a database table paycheck like this. empno, date, gross, fed_with 1234 2007-09-01 1153.85 108.26 1323 2007-09-01 461.54 83.08 1289 2007-09-01 1153.85 94.41 1234 2007-09-15 1153.85 108.26 1323 2007-09-15 491.94 87.18 1289 2007-09-15 1153.8594.41 I can easily do a query like this select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE=2007-09-01; But then I have to do a query for each pay date in the pay period. Accordingly, what would be really useful on a day like today would be to be able to do a query like the following: select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE IS distinct; Does anyone know how to do this? it seems you want to use group by: SELECT (SUM(gross) * .153) + SUM(fed_with) FROM paycheck GROUP BY date; See here: http://dev.mysql.com/doc/refman/5.0/en/select.html http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question.
you need to group the result sets by date, look at the manual link below: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html Richard Reina wrote: I have a database table paycheck like this. empno, date, gross, fed_with 1234 2007-09-01 1153.85 108.26 1323 2007-09-01 461.54 83.08 1289 2007-09-01 1153.85 94.41 1234 2007-09-15 1153.85 108.26 1323 2007-09-15 491.94 87.18 1289 2007-09-15 1153.8594.41 I can easily do a query like this select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE=2007-09-01; But then I have to do a query for each pay date in the pay period. Accordingly, what would be really useful on a day like today would be to be able to do a query like the following: select (SUM(gross) * .153) + SUM(fed_with) FROM paycheck where DATE IS distinct; Does anyone know how to do this? Thanks for the help. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question
there should be no space between function name and () i.e. it should be group_concat(hosts.name) (unless you have the sql mode IGNORE_SPACE set) Andrey Dmitriev wrote: I knew I’ve seen this error before ☺ Thanks a lot. -andrey From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 1:55 AM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 Try lose the space after group_concat. PB Andrey Dmitriev wrote: Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 mysql select service_names.name as 'Service', - group_concat (hosts.name) - from monarch.hosts as hosts, monarch.services as services, monarch.service_names as service_names - where - hosts.host_id=services.host_id - and service_names.servicename_id=services.servicename_id - group by service_name.name - - - ; ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 4:00 PM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Hi, Andrey Dmitriev wrote: This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', hosts.name as 'Host' from hosts, services, service_names where hosts.host_id=services.host_id and service_names.servicename_id=services.servicename_id order by service_names.name Which outputs something like | SSH | mt-ns4 | | SSH | tsn-adm-core | | SSH | tsn-juno | | SSH | tsn-tsn2 However, the desired output is one line per service name, so something like | SSH | mt-ns4, tsn-adm-core, tsn-juno, tsn-tsn2 | Can this be done w/o writing procedural code in mysql? Yes. Have a look at GROUP_CONCAT(). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question
I knew I’ve seen this error before ☺ Thanks a lot. -andrey From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 1:55 AM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 Try lose the space after group_concat. PB Andrey Dmitriev wrote: Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 mysql select service_names.name as 'Service', - group_concat (hosts.name) - from monarch.hosts as hosts, monarch.services as services, monarch.service_names as service_names - where - hosts.host_id=services.host_id - and service_names.servicename_id=services.servicename_id - group by service_name.name - - - ; ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 4:00 PM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Hi, Andrey Dmitriev wrote: This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', hosts.name as 'Host' from hosts, services, service_names where hosts.host_id=services.host_id and service_names.servicename_id=services.servicename_id order by service_names.name Which outputs something like | SSH | mt-ns4 | | SSH | tsn-adm-core | | SSH | tsn-juno | | SSH | tsn-tsn2 However, the desired output is one line per service name, so something like | SSH | mt-ns4, tsn-adm-core, tsn-juno, tsn-tsn2 | Can this be done w/o writing procedural code in mysql? Yes. Have a look at GROUP_CONCAT(). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question
Hi, Andrey Dmitriev wrote: This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', hosts.name as 'Host' from hosts, services, service_names where hosts.host_id=services.host_id and service_names.servicename_id=services.servicename_id order by service_names.name Which outputs something like | SSH | mt-ns4 | | SSH | tsn-adm-core | | SSH | tsn-juno | | SSH | tsn-tsn2 However, the desired output is one line per service name, so something like | SSH | mt-ns4, tsn-adm-core, tsn-juno, tsn-tsn2 | Can this be done w/o writing procedural code in mysql? Yes. Have a look at GROUP_CONCAT(). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question
Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 mysql select service_names.name as 'Service', - group_concat (hosts.name) - from monarch.hosts as hosts, monarch.services as services, monarch.service_names as service_names - where - hosts.host_id=services.host_id - and service_names.servicename_id=services.servicename_id - group by service_name.name - - - ; ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 4:00 PM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Hi, Andrey Dmitriev wrote: This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', hosts.name as 'Host' from hosts, services, service_names where hosts.host_id=services.host_id and service_names.servicename_id=services.servicename_id order by service_names.name Which outputs something like | SSH | mt-ns4 | | SSH | tsn-adm-core | | SSH | tsn-juno | | SSH | tsn-tsn2 However, the desired output is one line per service name, so something like | SSH | mt-ns4, tsn-adm-core, tsn-juno, tsn-tsn2 | Can this be done w/o writing procedural code in mysql? Yes. Have a look at GROUP_CONCAT(). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question
Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 Try lose the space after group_concat. PB Andrey Dmitriev wrote: Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 mysql select service_names.name as 'Service', - group_concat (hosts.name) - from monarch.hosts as hosts, monarch.services as services, monarch.service_names as service_names - where - hosts.host_id=services.host_id - and service_names.servicename_id=services.servicename_id - group by service_name.name - - - ; ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 4:00 PM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Hi, Andrey Dmitriev wrote: This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', hosts.name as 'Host' from hosts, services, service_names where hosts.host_id=services.host_id and service_names.servicename_id=services.servicename_id order by service_names.name Which outputs something like | SSH | mt-ns4 | | SSH | tsn-adm-core | | SSH | tsn-juno | | SSH | tsn-tsn2 However, the desired output is one line per service name, so something like | SSH | mt-ns4, tsn-adm-core, tsn-juno, tsn-tsn2 | Can this be done w/o writing procedural code in mysql? Yes. Have a look at GROUP_CONCAT(). Baron
Re: Query question
Can you post your table definitions and some sample data. Also what is the end requirement - how should the end result look like? Anoop On 4/23/07, Clyde Lewis [EMAIL PROTECTED] wrote: Guys, I have the following table that contains some information about a cars. I'm trying to write a query to determine: the number of make(name of car), number of models per make(name of car) and the average number of models/make(name of car) sold in a particular period. The two queries below can provide me with what I need, but am stumped on how to combine the results. Any help would be greatly appreciated. table name cars: columns: make model sold_date select make,count(make) from cars where make in(Nissan, Toyota, Honda) group by make select model,count(model) from cars where make in(Nissan, Toyota, Honda) group by model -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Hi Aaron, Aaron Clausen wrote: I have a couple of very simple tables to handle a client signin site: The client table has the following fields: client_id int(11) primary key auto_increment first_name char(90) last_name char(90) The signin table has the following fields record_id int primary key auto_increment client_id int date datetime Essentially, the client enters his id and it creates a record in the signin table. I need a query that can identify all the clients who signed in for the first time during a specific month. I have fought this one for a couple of days now and just can't seem to get it. I think you can break the problem into a couple of steps: 1) find the first login for each client. 2) eliminate all but the ones in the month. You can't do step 2 first because that would destroy your knowledge of whether a signin record is a client's first. select client_id, min(date) from signin group by client_id; Now you know the first time each client signed in. From here you can go several ways. One is to just add a HAVING clause. select client_id, min(date) from signin group by client_id having min(date) between ? and ?; I hope that helps, Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Behrang Saeedzadeh wrote: Hi, Suppose that there are two tables book and author: book id title author_id author - od title I want a query that returns all the books, but if there are more than 3 books with the same author_id, only 3 should be returned. For example if this is the contents of the book table: (1, Book 1, 10) (2, Book 2, 10) (3, Book 3, 10) (4, Book 4, 10) (5, Book 1, 11) these rows should be returned: (1, Book 1, 10) (2, Book 2, 10) (3, Book 3, 10) (5, Book 1, 11) Try this: select id, title, author_id from books where ( select count(*) from books as b where b.author_id = books.author_id and b.id books.id ) = 3; In English, select books where there aren't more than three lower-numbered books by the same author. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Hi Behrang, Behrang Saeedzadeh wrote: Hi Baron, Thanks. That that worked great. Is it possible to insert an empty row after the books by the same author? -Behi On 4/12/07, Baron Schwartz [EMAIL PROTECTED] wrote: Behrang Saeedzadeh wrote: Hi, Suppose that there are two tables book and author: book id title author_id author - od title I want a query that returns all the books, but if there are more than 3 books with the same author_id, only 3 should be returned. For example if this is the contents of the book table: (1, Book 1, 10) (2, Book 2, 10) (3, Book 3, 10) (4, Book 4, 10) (5, Book 1, 11) these rows should be returned: (1, Book 1, 10) (2, Book 2, 10) (3, Book 3, 10) (5, Book 1, 11) Try this: select id, title, author_id from books where ( select count(*) from books as b where b.author_id = books.author_id and b.id books.id ) = 3; In English, select books where there aren't more than three lower-numbered books by the same author. Please remember to reply to the list so others can read and benefit from answers to your questions. Also, though I don't care tremendously one way or another, many people think it's good form to place your response after the message instead of before (I tend to follow the pattern set by the first response -- I don't care either way as long as it's not back-and-forth in different styles, which makes it impossible to figure out the sequence of replies). To answer your second question, I can't think of a way to do this. It sounds like you're getting into the realm of formatting something for visual display, which is not what SQL is best used for in my opinion. Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Hi Baron, Please remember to reply to the list so others can read and benefit from answers to your questions. Also, though I don't care tremendously one way or another, many people think it's good form to place your response after the message instead of before (I tend to follow the pattern set by the first response -- I don't care either way as long as it's not back-and-forth in different styles, which makes it impossible to figure out the sequence of replies). Actually me preference is to break the incoming mail and at relevant points give my response. But I don't care that much as well. And in most of the mailing lists that I am a member of, the reply-to address is set to the address of the mailing list itself, so I usually hit reply and go on! But, the problem is that I accidentally press the reply button for the mailing lists that don't work like that... Anyway... To answer your second question, I can't think of a way to do this. It sounds like you're getting into the realm of formatting something for visual display, which is not what SQL is best used for in my opinion. Yeah, you're right. The problem is that I need to know for which authors the number of the books are more than, say 3, so I can display an appropriate message to the user. I can do this in two queries but I though there should be a way to do it in one query. Regards, Behrang -- Behrang Saeedzadeh http://www.jroller.com/page/behrangsa http://my.opera.com/behrangsa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question...
Try this on for size: SELECT DISTINCT id FROM tbl WHERE id NOT IN (SELECT id FROM tbl WHERE action = 1) The subselect will only work in 4.1 and later I think. Dan On 12/8/06, bruce [EMAIL PROTECTED] wrote: hi... i'm looking at what is probably a basic question. i have a tbl with -id -action -status -date ie: id action statusdate 1 0 1 1 1 2 1 2 3 - 2 0 4 2 2 5 i need a query to somehow get all id's that don't have an 'action=1', in this case, id '2' would be the id that should be returned from the query... however, i can't quite figure out how to create a query to select the items that are the ones i'm looking for. any help/thoughts would be 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]
Re: query question...
bruce wrote: hi... i'm looking at what is probably a basic question. i have a tbl with -id -action -status -date ie: id action statusdate 1 0 1 1 1 2 1 2 3 - 2 0 4 2 2 5 i need a query to somehow get all id's that don't have an 'action=1', in this case, id '2' would be the id that should be returned from the query... however, i can't quite figure out how to create a query to select the items that are the ones i'm looking for. any help/thoughts would be appreciated! select t1.* from tbl t1 left join tbl t2 on t1.id = t2.id where t2.id is NULL t2.action = 1 I *think*. -- John C. Nichel IV Programmer/System Admin Dot Com Holdings of Buffalo 716.856.9675 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question...
Ysgrifennodd bruce: hi... i'm looking at what is probably a basic question. i have a tbl with -id -action -status -date ie: id action statusdate 1 0 1 1 1 2 1 2 3 - 2 0 4 2 2 5 i need a query to somehow get all id's that don't have an 'action=1', in this case, id '2' would be the id that should be returned from the query... however, i can't quite figure out how to create a query to select the items that are the ones i'm looking for. any help/thoughts would be appreciated! thanks -bruce Hi Bruce, Does this do it for you? SELECT t1.id FROM tbl t1 WHERE t1.id NOT IN (SELECT t2.id FROM tbl t2 WHERE t2.id = 1) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question...
hi peter. thanks, the solution you gave me is close...!! my actual data is: mysql select * from SvnTBL; +--+--+--+-++ | universityID | actionID | statusID | _date | ID | +--+--+--+-++ |1 |1 |0 | 2006-12-08 13:12:15 | 1 | |1 |2 |0 | 2006-12-08 13:12:15 | 2 | |1 |3 |0 | 2006-12-08 13:12:15 | 3 | |2 |1 |0 | 2006-12-08 13:12:15 | 4 | |2 |3 |0 | 2006-12-08 13:12:15 | 5 | |3 |1 |0 | 2006-12-08 13:12:15 | 6 | |3 |6 |0 | 2006-12-08 13:12:15 | 7 | |3 |3 |0 | 2006-12-08 13:12:15 | 8 | |3 |4 |0 | 2006-12-08 13:12:15 | 9 | +--+--+--+-++ if i do: mysql SELECT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2); i get, +--+ | universityID | +--+ |2 | |2 | |3 | |3 | |3 | |3 | +--+ 6 rows in set (0.00 sec) what i really want to get is: +--+ | universityID | +--+ |2 | |3 | +--+ which would be the unique 'id's. i've tried to do a 'limit' and group, but i'm missing some thing... thanks -bruce -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Friday, December 08, 2006 12:26 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: query question... Ysgrifennodd bruce: hi... i'm looking at what is probably a basic question. i have a tbl with -id -action -status -date ie: id action statusdate 1 0 1 1 1 2 1 2 3 - 2 0 4 2 2 5 i need a query to somehow get all id's that don't have an 'action=1', in this case, id '2' would be the id that should be returned from the query... however, i can't quite figure out how to create a query to select the items that are the ones i'm looking for. any help/thoughts would be appreciated! thanks -bruce Hi Bruce, Does this do it for you? SELECT t1.id FROM tbl t1 WHERE t1.id NOT IN (SELECT t2.id FROM tbl t2 WHERE t2.id = 1) Peter -- 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: RE: query question...
Just add a DISTINCT: SELECT DISTINCT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2); Dan On 12/8/06, bruce [EMAIL PROTECTED] wrote: hi peter. thanks, the solution you gave me is close...!! my actual data is: mysql select * from SvnTBL; +--+--+--+-++ | universityID | actionID | statusID | _date | ID | +--+--+--+-++ |1 |1 |0 | 2006-12-08 13:12:15 | 1 | |1 |2 |0 | 2006-12-08 13:12:15 | 2 | |1 |3 |0 | 2006-12-08 13:12:15 | 3 | |2 |1 |0 | 2006-12-08 13:12:15 | 4 | |2 |3 |0 | 2006-12-08 13:12:15 | 5 | |3 |1 |0 | 2006-12-08 13:12:15 | 6 | |3 |6 |0 | 2006-12-08 13:12:15 | 7 | |3 |3 |0 | 2006-12-08 13:12:15 | 8 | |3 |4 |0 | 2006-12-08 13:12:15 | 9 | +--+--+--+-++ if i do: mysql SELECT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2); i get, +--+ | universityID | +--+ |2 | |2 | |3 | |3 | |3 | |3 | +--+ 6 rows in set (0.00 sec) what i really want to get is: +--+ | universityID | +--+ |2 | |3 | +--+ which would be the unique 'id's. i've tried to do a 'limit' and group, but i'm missing some thing... thanks -bruce -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Friday, December 08, 2006 12:26 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: query question... Ysgrifennodd bruce: hi... i'm looking at what is probably a basic question. i have a tbl with -id -action -status -date ie: id action statusdate 1 0 1 1 1 2 1 2 3 - 2 0 4 2 2 5 i need a query to somehow get all id's that don't have an 'action=1', in this case, id '2' would be the id that should be returned from the query... however, i can't quite figure out how to create a query to select the items that are the ones i'm looking for. any help/thoughts would be appreciated! thanks -bruce Hi Bruce, Does this do it for you? SELECT t1.id FROM tbl t1 WHERE t1.id NOT IN (SELECT t2.id FROM tbl t2 WHERE t2.id = 1) Peter -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question...
Ysgrifennodd bruce: hi peter. thanks, the solution you gave me is close...!! snip what i really want to get is: +--+ | universityID | +--+ |2 | |3 | +--+ which would be the unique 'id's. i've tried to do a 'limit' and group, but i'm missing some thing... thanks -bruce SELECT DISTINCT ... I didn't realise you only wanted the distinct values. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: query question...
dan... thanks!!! like a charm.. now for the other 200 queries i'm dealing with!! -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, December 08, 2006 1:40 PM To: [EMAIL PROTECTED] Cc: Peter Bradley; mysql@lists.mysql.com Subject: Re: RE: query question... Just add a DISTINCT: SELECT DISTINCT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2); Dan On 12/8/06, bruce [EMAIL PROTECTED] wrote: hi peter. thanks, the solution you gave me is close...!! my actual data is: mysql select * from SvnTBL; +--+--+--+-++ | universityID | actionID | statusID | _date | ID | +--+--+--+-++ |1 |1 |0 | 2006-12-08 13:12:15 | 1 | |1 |2 |0 | 2006-12-08 13:12:15 | 2 | |1 |3 |0 | 2006-12-08 13:12:15 | 3 | |2 |1 |0 | 2006-12-08 13:12:15 | 4 | |2 |3 |0 | 2006-12-08 13:12:15 | 5 | |3 |1 |0 | 2006-12-08 13:12:15 | 6 | |3 |6 |0 | 2006-12-08 13:12:15 | 7 | |3 |3 |0 | 2006-12-08 13:12:15 | 8 | |3 |4 |0 | 2006-12-08 13:12:15 | 9 | +--+--+--+-++ if i do: mysql SELECT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2); i get, +--+ | universityID | +--+ |2 | |2 | |3 | |3 | |3 | |3 | +--+ 6 rows in set (0.00 sec) what i really want to get is: +--+ | universityID | +--+ |2 | |3 | +--+ which would be the unique 'id's. i've tried to do a 'limit' and group, but i'm missing some thing... thanks -bruce -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Friday, December 08, 2006 12:26 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: query question... Ysgrifennodd bruce: hi... i'm looking at what is probably a basic question. i have a tbl with -id -action -status -date ie: id action statusdate 1 0 1 1 1 2 1 2 3 - 2 0 4 2 2 5 i need a query to somehow get all id's that don't have an 'action=1', in this case, id '2' would be the id that should be returned from the query... however, i can't quite figure out how to create a query to select the items that are the ones i'm looking for. any help/thoughts would be appreciated! thanks -bruce Hi Bruce, Does this do it for you? SELECT t1.id FROM tbl t1 WHERE t1.id NOT IN (SELECT t2.id FROM tbl t2 WHERE t2.id = 1) Peter -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Query question
Dan, your suggestion is *exactly* what I needed! Furthermore, because of the use of the subquery, there is no need to join to table to itself, so the query may be simplified to: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - WHERE loc1.locLevel5Id = 2356 - ANDloc1.imageid NOT IN -(SELECT imageid FROM locBridgeImageLocLevel5 WHERE locLevel5Id = 13128); +-+ | imageId | +-+ | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | ... | 255 | | 258 | | 259 | | 260 | | 261 | +-+ Thank you very much for sharing your expertise. Erick At 3:56 PM -0500 10/17/06, Dan Buettner wrote: I see what's happening, Erick. It's matching all the rows in loc1 and loc2 with the same image id. It *is* excluding 13128, but image id 1 is still appearing because of the rows where they match *besides* 13128. For example, 18302 and actually also 2356 since you're joining a table on itself. Sounds like what you want is to exclude all the image ids for locLevel5Id = 13128 ? Rewrite like so, assuming you have subqueries: SELECT distinct loc1.imageId FROM locBridgeImageLocLevel5 as loc1 INNER JOIN locBridgeImageLocLevel5 as loc2 USING (imageId) WHERE loc1.locLevel5Id = 2356 AND loc2.imageid NOT IN (SELECT imageid from locBridgeImageLocLevel5 WHERE locLevel5Id = 13128); I think I'm understanding your goal!! Dan On 10/17/06, Erick Carballo [EMAIL PROTECTED] wrote: Dan, thanks for your prompt response. You are correct: I mistyped. However, if I ran the query as you suggest, I obtain the same results: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 13128); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | ... | 257 | | 258 | | 259 | | 260 | | 261 | +-+ 251 rows in set (0.04 sec) It stills returns imageId 1, eventhough there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ Erick At 2:47 PM -0500 10/17/06, Dan Buettner wrote: Erick, maybe I'm missing something or you mistyped, but you appear to be saying this: you want 2356 and not 13128 but your last SQL query is excluding only 18302. 13128 is not mentioned in the query. Try re-running the query with 13128 instead of 18302 ? Dan On 10/17/06, Erick Carballo [EMAIL PROTECTED] wrote: Hello, I would really appreciate your help regarding a query. First, some background: The query is being executed on the following table: mysql describe locBridgeImageLocLevel5; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables (image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |2356 | |
Re: Query question
Erick, maybe I'm missing something or you mistyped, but you appear to be saying this: you want 2356 and not 13128 but your last SQL query is excluding only 18302. 13128 is not mentioned in the query. Try re-running the query with 13128 instead of 18302 ? Dan On 10/17/06, Erick Carballo [EMAIL PROTECTED] wrote: Hello, I would really appreciate your help regarding a query. First, some background: The query is being executed on the following table: mysql describe locBridgeImageLocLevel5; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables (image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |2356 | | 4 | 2 | 18302 | +---+-+-+ So far I have been successful in obtaining data from the tables asking for two discrete criteria using the AND logical operator: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id = 18302); +-+ | imageId | +-+ | 1 | | 2 | +-+ The problem comes when try to find a record in which a condition is positive, and the other negative. Using NOT gave me a syntax error: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 NOT loc2.locLevel5Id = 18302); ERROR 1064 (42000): 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 'loc2.locLevel5Id = 18302)' at line 5 And using in combination with AND, gave me a result that is not correct. mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 18302); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | ... | 259 | | 260 | | 261 | +-+ I should rephrase my last sentence, I am sure the result *IS* correct, but it is not what I am looking for. I am looking for those images that are associated with locLevel5Id 2356, but NOT associated with locLevel5Id 13128. As you can see from the query result above, imageId 1 is selected, however there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128. As I mentioned before, your help will be greatly appreciated. If you need further details, please let me know. Erick Teaching Learning Technologies Center Ballantine Hall 307 http://www.indiana.edu/~tltc/ -- 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: Query question
Dan, thanks for your prompt response. You are correct: I mistyped. However, if I ran the query as you suggest, I obtain the same results: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 13128); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | ... | 257 | | 258 | | 259 | | 260 | | 261 | +-+ 251 rows in set (0.04 sec) It stills returns imageId 1, eventhough there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ Erick At 2:47 PM -0500 10/17/06, Dan Buettner wrote: Erick, maybe I'm missing something or you mistyped, but you appear to be saying this: you want 2356 and not 13128 but your last SQL query is excluding only 18302. 13128 is not mentioned in the query. Try re-running the query with 13128 instead of 18302 ? Dan On 10/17/06, Erick Carballo [EMAIL PROTECTED] wrote: Hello, I would really appreciate your help regarding a query. First, some background: The query is being executed on the following table: mysql describe locBridgeImageLocLevel5; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables (image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |2356 | | 4 | 2 | 18302 | +---+-+-+ So far I have been successful in obtaining data from the tables asking for two discrete criteria using the AND logical operator: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id = 18302); +-+ | imageId | +-+ | 1 | | 2 | +-+ The problem comes when try to find a record in which a condition is positive, and the other negative. Using NOT gave me a syntax error: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 NOT loc2.locLevel5Id = 18302); ERROR 1064 (42000): 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 'loc2.locLevel5Id = 18302)' at line 5 And using in combination with AND, gave me a result that is not correct. mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 18302); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 |
Re: Re: Query question
I see what's happening, Erick. It's matching all the rows in loc1 and loc2 with the same image id. It *is* excluding 13128, but image id 1 is still appearing because of the rows where they match *besides* 13128. For example, 18302 and actually also 2356 since you're joining a table on itself. Sounds like what you want is to exclude all the image ids for locLevel5Id = 13128 ? Rewrite like so, assuming you have subqueries: SELECT distinct loc1.imageId FROM locBridgeImageLocLevel5 as loc1 INNER JOIN locBridgeImageLocLevel5 as loc2 USING (imageId) WHERE loc1.locLevel5Id = 2356 AND loc2.imageid NOT IN (SELECT imageid from locBridgeImageLocLevel5 WHERE locLevel5Id = 13128); I think I'm understanding your goal!! Dan On 10/17/06, Erick Carballo [EMAIL PROTECTED] wrote: Dan, thanks for your prompt response. You are correct: I mistyped. However, if I ran the query as you suggest, I obtain the same results: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id 13128); +-+ | imageId | +-+ | 1 | | 2 | | 280 | | 4 | | 5 | | 6 | | 7 | ... | 257 | | 258 | | 259 | | 260 | | 261 | +-+ 251 rows in set (0.04 sec) It stills returns imageId 1, eventhough there is a record (locBridgeImageLocLevel5Id 541) in which imagedId = 1 and locLevel5Id = 13128: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ Erick At 2:47 PM -0500 10/17/06, Dan Buettner wrote: Erick, maybe I'm missing something or you mistyped, but you appear to be saying this: you want 2356 and not 13128 but your last SQL query is excluding only 18302. 13128 is not mentioned in the query. Try re-running the query with 13128 instead of 18302 ? Dan On 10/17/06, Erick Carballo [EMAIL PROTECTED] wrote: Hello, I would really appreciate your help regarding a query. First, some background: The query is being executed on the following table: mysql describe locBridgeImageLocLevel5; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | locBridgeImageLocLevel5Id | int(10) unsigned | NO | PRI | NULL | auto_increment | | imageId | int(10) unsigned | NO | | || | locLevel5Id | int(10) unsigned | NO | | || +---+--+--+-+-++ As you may see, this table gathers data from two separate tables (image and and locLevel5). The locLevel5 table contains data from a US official list of places which--unfortunately--mixes location names of counties, cities, and certain geographical places (such as lakes, national parks, mountains, etc.). This creates a many-to-many relationship between imageId and locLevel5Id, as the following queries show: mysql select * from locBridgeImageLocLevel5 where imageId = 1; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 1 | 1 |2356 | | 2 | 1 | 18302 | | 541 | 1 | 13128 | +---+-+-+ mysql select * from locBridgeImageLocLevel5 where imageId = 2; +---+-+-+ | locBridgeImageLocLevel5Id | imageId | locLevel5Id | +---+-+-+ | 3 | 2 |2356 | | 4 | 2 | 18302 | +---+-+-+ So far I have been successful in obtaining data from the tables asking for two discrete criteria using the AND logical operator: mysql SELECT distinct loc1.imageId - FROM locBridgeImageLocLevel5 as loc1 - INNER JOIN - locBridgeImageLocLevel5 as loc2 USING (imageId) - WHERE (loc1.locLevel5Id = 2356 AND loc2.locLevel5Id = 18302); +-+ | imageId | +-+ | 1 | | 2 | +-+ The problem comes when try to find a record in which a condition is positive, and the other negative. Using NOT gave me a syntax error: mysql SELECT distinct loc1.imageId
Re: query question: updating between 2 tables
2006/9/19, Peter Van Dijck [EMAIL PROTECTED]: Hi all, trying to figure out if there is a query I can use for this, or if I have to write a php script to loop tru each row... table1: entryid int(11) itemid int(11) table2: object_id int(11) The situation is: table2.objectid is populated with the values of table1.itemid, but they have to be replaced with the corresponding table1.entryid. I could do in PHP: - select * from table2 - for each row, update table2 with select from table1 but I am wondering if there is 1 query that could take care of this? Peter something like : update table2,table1 set table2.objectid=table1.itemid where table1.entryid=table2.objectid you need 4.0 at least IIRC. -- http://www.myspace.com/sakuradrop : credit runs faster http://www.w-fenec.org/ Rock Webzine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
Peter Van Dijck wrote: I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter SELECT Count(*) as Count, UserID FROM table GROUP BY UserID ORDER BY Count DESC LIMIT 5 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
brilliant, that works! Thanks! On 8/20/06, Chris W [EMAIL PROTECTED] wrote: Peter Van Dijck wrote: I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter SELECT Count(*) as Count, UserID FROM table GROUP BY UserID ORDER BY Count DESC LIMIT 5 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
select userid,count(text) from blah group by userid; --On August 20, 2006 7:22:59 PM +0100 Peter Van Dijck [EMAIL PROTECTED] wrote: I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
The story so far, with comments: Michael DePhillips wrote: Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Michael DePhillips also wrote: snip ...the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. nigel wood wrote: What you want to do isn't possible in the form you want without subqueries. Which would you prefer: Yes, I believe it is. See below. a) A single query returning one row of three columns containing nulls b) A single query using sub queries (MySQL 4.1+ only) returning upto three rows with one column c) multiple queries Nigel Michael DePhillips wrote: Hi Nigel, A and B...please. Douglas Sims wrote: I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t1 WHERE id(SELECT MAX(id) FROM t1 WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t1 WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t1 WHERE id@id) ORDER BY id ASC LIMIT 1; I think you're on the right track, but this is unnecessarily complicated, and, unfortunately, wrong. Given rows with the ids 3,4,5,6,9, it selects 3, 5, and 9 rather than 4, 5, and 6. Look at the first query: SELECT * FROM t1 WHERE id (SELECT MAX(id) FROM t1 WHERE id@id) ORDER BY id DESC LIMIT 1; The subquery finds the previous id (isn't that what we want?), then the outer query selects all the ids that are *less than* the id we want and limits the output to the largest one. In other words, we get the row prior to the row that is prior to the row with the chosen id. You need an = in there somewhere. To get the previous id: SELECT MAX(id) FROM t1 WHERE id @id; To get the entire previous row: SELECT * FROM t1 WHERE id @id ORDER BY id DESC LIMIT 1; or SELECT * FROM t1 WHERE id = (SELECT MAX(id) FROM t1 WHERE id @id); Your last query is similar -- it selects the 2nd row after the middle one rather than the next one. But as to putting that in one statement... it might be better just to do it as three. There's always UNION. See below. [EMAIL PROTECTED] wrote: Here's a single query version of Douglas's solution: select @id:=6; select distinct t.testid, (select max(testid) from t where testid @id) as previousId, (select min(testid) from t where testid @id) as nextId from t where [EMAIL PROTECTED] Donna Well, this requires subqueries (mysql 4.1+), and only returns the ids (not the other columns in those rows), but probably neither is a big deal. There is one big problem, however: It returns nothing if the chosen id doesn't exist. Michael specified that he wanted the rows before and after the chosen id, even if no row has the chosen id. Try this: SET @id = 13; To get just the ids, even when no row has id = @id: (SELECT MAX(id) FROM t1 WHERE id @id) UNION DISTINCT (SELECT id FROM t1 WHERE id = @id) UNION DISTINCT (SELECT MIN(id) FROM t1 WHERE id @id) ORDER BY 1; To get the row contents, as well: (SELECT * FROM t1 WHERE id @id ORDER BY id DESC LIMIT 1) UNION DISTINCT (SELECT * FROM t1 WHERE id = @id) UNION DISTINCT (SELECT * FROM t1 WHERE id @id ORDER BY id ASC LIMIT 1) ORDER BY id; (Whether those are each one query or three depends on how you feel about UNION, I suppose.) Now, that last query isn't very efficient. The first and last parts may not use the index on id very well. You could improve on this significantly if you knew the size of the largest gap in the sequence of ids (or at least an upper bound). For example, if you were sure no 2 ids were farther apart than 12, you could take that into account, like so: SET @id = 2345; SET @maxgap = 12; (SELECT * FROM inits WHERE id @id AND id @id - @maxgap ORDER BY id DESC LIMIT 1) UNION DISTINCT (SELECT * FROM inits WHERE id = @id) UNION DISTINCT (SELECT * FROM inits WHERE id @id AND id @id + @maxgap ORDER BY id ASC LIMIT 1)
Re: Query Question
Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Have a great day, Dan Julson Application Software Engineer API Outsourcing 1355 Mendota Heights Rd. Mendota Heights, MN 55120 651-675-2628 (work) 952-456-2067 (mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Michael DePhillips wrote: Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 Assuming the id's are consecutive. You want surounding rows returned if the target Id is missing: - select id from T1 as first where T1.id between (1237 -1) and (1237 +1); You want no rows returned if the target Id is missing: - select id from T1 as first , inner join T2 as second on second.id between (first.id -1) and (first.id +1) where first.id = 1237; If you Id's aren't always consecutive, or you need something more smart post again. Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. What you want to do isn't possible in the form you want without subqueries. Which would you prefer: a) A single query returning one row of three columns containing nulls b) A single query using sub queries (MySQL 4.1+ only) returning upto three rows with one column c) multiple queries Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id(SELECT MAX(id) FROM t WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t WHERE id@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- 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: Query Question
Hi Nigel, A and B...please. Perhaps a UDF could achieve my initial request...any ideas. Thanks, Michael nigel wood wrote: Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. What you want to do isn't possible in the form you want without subqueries. Which would you prefer: a) A single query returning one row of three columns containing nulls b) A single query using sub queries (MySQL 4.1+ only) returning upto three rows with one column c) multiple queries Nigel -- Michael DePhillips www.star.bnl.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Here's a single query version of Douglas's solution: select @id:=6; select distinct t.testid, (select max(testid) from t where testid @id) as previousId, (select min(testid) from t where testid @id) as nextId from t where [EMAIL PROTECTED] Donna Douglas Sims [EMAIL PROTECTED] 08/14/2006 10:47 AM To Michael DePhillips [EMAIL PROTECTED] cc Dan Julson [EMAIL PROTECTED], mysql@lists.mysql.com Subject Re: Query Question I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id(SELECT MAX(id) FROM t WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t WHERE id@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Query Question
D'oh. Very good. I wish I'd thought of that. In response to Michael DePhillips' point about the UDF - I believe that in MySQL 5.x UDFs can't query tables. In Oracle, SQL Server, etc. they can and I'm sure they will in the future. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 10:17 AM, [EMAIL PROTECTED] wrote: Here's a single query version of Douglas's solution: select @id:=6; select distinct t.testid, (select max(testid) from t where testid @id) as previousId, (select min(testid) from t where testid @id) as nextId from t where [EMAIL PROTECTED] Donna Douglas Sims [EMAIL PROTECTED] 08/14/2006 10:47 AM To Michael DePhillips [EMAIL PROTECTED] cc Dan Julson [EMAIL PROTECTED], mysql@lists.mysql.com Subject Re: Query Question I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id(SELECT MAX(id) FROM t WHERE id@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id(SELECT MIN(id) FROM t WHERE id@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: Hi Dan, Thanks for the prompt reply, As I described it yes, you are correct, however, the id may not always be one(1) value away. So the number one needs, somehow, to be replaced with a way to get the next largest value and the previous less than value. Sorry for the lack of precision in my prior post. Regards, Michael Dan Julson wrote: Michael, I would think this is what you want. Select ID from T1 where ID BETWEEN (id in question - 1) and (id in question + 1) If you want distinct values, place the distinct keyword in front of ID (i.e. Select DISTINCT ID... This should do it for you. -Dan Hi, Does anyone have a clever way of returning; a requested value with one value less than that value, and one value greater than that value with one query. For example T1 contains ID 1234 1235 1236 1238 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 or; select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I would want 1236 1238 I would be surprised if there was no way of doing this.but then again, I'm often surprised Thanks Michael -- Michael DePhillips www.star.bnl.gov -- Michael DePhillips www.star.bnl.gov -- 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
On Monday 14 August 2006 07:08 am, Michael DePhillips wrote: select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3 (obviously this doesn't work) I would want to return 1234 1235 1236 mysql select int_value, (int_value + 1) as value2, (int_value - 1) as value3 FROM data_values; +---+++ | int_value | value2 | value3 | +---+++ |11 | 12 | 10 | +---+++ 1 row in set (0.00 sec) You can do like that. -- Chris White PHP Programmer/DBrother Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question: select * from table where id in (1,2,3) order by date uses FILESORT
MySQL is doing a file sort on the query result. It's not sorting the entire table and it's not sorting the 40 record limit you specified. It's sorting the WHERE id IN... result. After the sort, then it will return just the first 40 records. You can throw and EXPLAIN in front of the query to see what mysql is going to do. - Original Message - From: Peter Van Dijck [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 20, 2006 3:08 AM Subject: Query question: select * from table where id in (1,2,3) order by date uses FILESORT Hi all, this is a problem I'm running into: A table has like 400,000 rows, with a primary key index id. I use this query: SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date DESC LIMIT 0, 40 The problem is, it uses the index id and then a FILESORT. Questions: 1) Am I correct in thinking that a filesort on this large table is bad? Or is mysql filesorting only the 40-row result set, and itt's not so bad? 2) Any solutions? Is this a common problem? Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- 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: Query question: select * from table where id in (1,2,3) order by date uses FILESORT
I agree with Brent on what MySQL is doing ... are you seeing poor performance with this query? If so, you might evaluate whether adding an index on your 'post_date' column improves things, as MySQL may be able to sort and thus LIMIT more quickly (using index in RAM rather than reading off disk). It may not make a difference unless your result set from the WHERE ID IN () portion is reasonably large, though. Dan Brent Baisley wrote: MySQL is doing a file sort on the query result. It's not sorting the entire table and it's not sorting the 40 record limit you specified. It's sorting the WHERE id IN... result. After the sort, then it will return just the first 40 records. You can throw and EXPLAIN in front of the query to see what mysql is going to do. - Original Message - From: Peter Van Dijck [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 20, 2006 3:08 AM Subject: Query question: select * from table where id in (1,2,3) order by date uses FILESORT Hi all, this is a problem I'm running into: A table has like 400,000 rows, with a primary key index id. I use this query: SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date DESC LIMIT 0, 40 The problem is, it uses the index id and then a FILESORT. Questions: 1) Am I correct in thinking that a filesort on this large table is bad? Or is mysql filesorting only the 40-row result set, and itt's not so bad? 2) Any solutions? Is this a common problem? Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Douglas S. Davis wrote: Hi, If the following isn't appropriate, please feel free to ignore. The program I'm referring to is written in Perl and uses a MySQL database, so I thought perhaps it would be appropriate for this list. I have a webpage that displays a user's profile by selecting the data in the profiles database that corresponds to the user's ID number (in the profiles database that number is stored as the id_num). I have another table that stores comments that are posted to each user's profile page. What I need to do is: #1) Display a user's profile by looking up the data in the profiles database. #2) Look in the comments database for any comments that have the user's id_num associated with it, and display the comments and the comment posters' names as found by querying the profile database on the comment posters' ID numbers. Can this be accomplished with a join. Basically two tables with data similar to this: Profiles Table | id | name | | 1 | Bill Clinton| --- | 2 | George Bush | --- | 3 | Gerald Ford | --- Comments Table -- | user_id | poster_id | comment | -- | 1 | 2 | What a guy. | -- | 2 | 3 | What a jerk. | -- | 1 | 3 | Cigar lover.| -- When I display the profile for Bill Clinton (id #1 in the Profiles table), it needs to pull the comments made to Bill's profile (the ones in the Comments table with the user_id matching Bill's id from the Profiles table: What a guy., and Cigar lover) and also look up the name of the person who made those comments by taking the poster_id from the Comments table and matching them with a name from the first table (for the two comments on Bill's profile, that would be George Bush and Gerald Ford). Can this be done in one SQL query? If so, what would it look like? Thanks! Douglas Douglas S. Davis Programmer/Analyst Haverford College Administrative Computing 370 Lancaster Ave. Haverford, PA 19041 610-896-4206 This is a How do I write this query? question, rather than a Perl DBI question, so it would be better on the general list. I've copied the general list so any further discussion can take place there. Yes, you can do this with a join. You end up joining to the Profiles table twice, once for the name of the subjec of the quote and once for the source of the quote. Something like this: SELECT p1.name AS Subject, c.comment AS Quote, p2.name AS Source FROM Profiles p1 LEFT JOIN Comments c ON p1.id = c.user_id LEFT JOIN Profiles p2 ON c.poster_id = p2.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Suppose you subscribe to a public email list that offers support on a free open source database, and you see an email where someone doesn't really provide nearly enough information to answer, what would you do? What is the algorithm you are trying to implement to get the query-output? Roy Harrell wrote: Suppose I have a simple table as follows: PartNameTolerance Cycles A 1 10 A 2 11 A 3 13 A 4 15 A 5 18 B 1 12 B 2 14 B 3 16 B 4 16 B 5 17 C 1 6 C 2 7 C 3 7 C 4 8 C 5 10 How do I set up a query whose output would look like this: Tolerance PartA PartB PartC 1 10 12 6 2 11 14 7 3 13 16 7 4 15 16 8 5 18 17 10 Thanks, Roy Harrell Adaptive Equipment 352.372.7821 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Question
Hi, One way of doing it would be: select a.tolerance, a.Cycles as PartA, b.Cycles as PartB, c.Cycles as PartC from t as a, t as b, t as c where a.tolerance=b.tolerance and a.tolerance=c.tolerance and a.PartName='A' and b.PartName='B' and c.PartName='C'; Jacek -Original Message- From: Roy Harrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 12:16 PM To: mysql@lists.mysql.com Subject: Query Question Suppose I have a simple table as follows: PartName Tolerance Cycles A 1 10 A 2 11 A 3 13 A 4 15 A 5 18 B 1 12 B 2 14 B 3 16 B 4 16 B 5 17 C 1 6 C 2 7 C 3 7 C 4 8 C 5 10 How do I set up a query whose output would look like this: Tolerance PartA PartB PartC 1 10 12 6 2 11 14 7 3 13 16 7 4 15 16 8 5 18 17 10 Thanks, Roy Harrell Adaptive Equipment 352.372.7821 -- 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: Query Question
Roy Harrell [EMAIL PROTECTED] wrote on 10/04/2005 03:15:33 PM: Suppose I have a simple table as follows: PartName Tolerance Cycles A 1 10 A 2 11 A 3 13 A 4 15 A 5 18 B 1 12 B 2 14 B 3 16 B 4 16 B 5 17 C 1 6 C 2 7 C 3 7 C 4 8 C 5 10 How do I set up a query whose output would look like this: Tolerance PartA PartB PartC 1 10 12 6 2 11 14 7 3 13 16 7 4 15 16 8 5 18 17 10 Thanks, Roy Harrell Adaptive Equipment 352.372.7821 What you are doing is called a Pivot Table or a Crosstab Query. In order to do one with MySQL, you need to decide what kind of information you want in your middle cells. That's because you need to pick an aggregate function (sum, min, max, avg, etc.) to wrap around your middle cell data. I will break it down by min cycles, max cycles, and avg cycles for each of the parts A, B, and C. SELECT Tolerance , MIN(if(PartName='A', cycles, NULL)) as A_min , MAX(if(PartName='A', cycles, NULL)) as A_max , AVG(if(PartName='A', cycles, NULL)) as A_avg , MIN(if(PartName='B', cycles, NULL)) as B_min , MAX(if(PartName='B', cycles, NULL)) as B_max , AVG(if(PartName='B', cycles, NULL)) as B_avg , MIN(if(PartName='C', cycles, NULL)) as C_min , MAX(if(PartName='C', cycles, NULL)) as C_max , AVG(if(PartName='C', cycles, NULL)) as C_avg FROM simpletable GROUP BY Tolerance; There is no automatic solution for this with MySQL. You have to know your what your analysis columns will be and how to identify them before you can write this query. Nothing says you can't use code to write the query (notice the repetitive pattern?) but there is no native function to do that for you. Notes: When computing MIN(), MAX(), or AVG(), all NULL values should be ignored. That's what the IF() clause is for, to pick out just the values you want to analyze. Does that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query Question
Jacek, Your method would only work so long as each PartA, PartB, and PartC all have the same tolerance numbers. if PartA and PartB had a tolerance of 20 but PartC didn't, your query would not show just the A and B tolerances. In fact, it wouldn't show a line for Tolerance 20 at all. The only way to do this in the pattern you describe is with the FULL OUTER JOIN predicate. MySql currently supports the INNER, LEFT, RIGHT, and NATURAL joins but not the FULL OUTER JOIN. There is a workaround for FULL OUTER JOIN if you need it but you have to be on a version that supports UNION queries to make it work without a temp table. The workaround also becomes very cumbersome if you are joining more than two tables. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Becla, Jacek [EMAIL PROTECTED] wrote on 10/04/2005 03:33:04 PM: Hi, One way of doing it would be: select a.tolerance, a.Cycles as PartA, b.Cycles as PartB, c.Cycles as PartC from t as a, t as b, t as c where a.tolerance=b.tolerance and a.tolerance=c.tolerance and a.PartName='A' and b.PartName='B' and c.PartName='C'; Jacek -Original Message- From: Roy Harrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 12:16 PM To: mysql@lists.mysql.com Subject: Query Question Suppose I have a simple table as follows: PartName Tolerance Cycles A 1 10 A 2 11 A 3 13 A 4 15 A 5 18 B 1 12 B 2 14 B 3 16 B 4 16 B 5 17 C 1 6 C 2 7 C 3 7 C 4 8 C 5 10 How do I set up a query whose output would look like this: Tolerance PartA PartB PartC 1 10 12 6 2 11 14 7 3 13 16 7 4 15 16 8 5 18 17 10 Thanks, Roy Harrell Adaptive Equipment 352.372.7821 -- 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: Query Question
Yes, agreed. The case you mentioned about missing tolerance is not in his example though - we clearly need more input from Roy. Jacek -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 12:56 PM To: Becla, Jacek Cc: Roy Harrell; mysql@lists.mysql.com Subject: RE: Query Question Jacek, Your method would only work so long as each PartA, PartB, and PartC all have the same tolerance numbers. if PartA and PartB had a tolerance of 20 but PartC didn't, your query would not show just the A and B tolerances. In fact, it wouldn't show a line for Tolerance 20 at all. The only way to do this in the pattern you describe is with the FULL OUTER JOIN predicate. MySql currently supports the INNER, LEFT, RIGHT, and NATURAL joins but not the FULL OUTER JOIN. There is a workaround for FULL OUTER JOIN if you need it but you have to be on a version that supports UNION queries to make it work without a temp table. The workaround also becomes very cumbersome if you are joining more than two tables. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Becla, Jacek [EMAIL PROTECTED] wrote on 10/04/2005 03:33:04 PM: Hi, One way of doing it would be: select a.tolerance, a.Cycles as PartA, b.Cycles as PartB, c.Cycles as PartC from t as a, t as b, t as c where a.tolerance=b.tolerance and a.tolerance=c.tolerance and a.PartName='A' and b.PartName='B' and c.PartName='C'; Jacek -Original Message- From: Roy Harrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 12:16 PM To: mysql@lists.mysql.com Subject: Query Question Suppose I have a simple table as follows: PartName Tolerance Cycles A 1 10 A 2 11 A 3 13 A 4 15 A 5 18 B 1 12 B 2 14 B 3 16 B 4 16 B 5 17 C 1 6 C 2 7 C 3 7 C 4 8 C 5 10 How do I set up a query whose output would look like this: Tolerance PartA PartB PartC 1 10 12 6 2 11 14 7 3 13 16 7 4 15 16 8 5 18 17 10 Thanks, Roy Harrell Adaptive Equipment 352.372.7821 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Question
Should have specified - all parts will have the same tolerance numbers. Thanks for the quick, informative, responses. I'll give them a whirl. Roy On Tue, 2005-10-04 at 15:59, Becla, Jacek wrote: Yes, agreed. The case you mentioned about missing tolerance is not in his example though - we clearly need more input from Roy. Jacek -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 12:56 PM To: Becla, Jacek Cc: Roy Harrell; mysql@lists.mysql.com Subject: RE: Query Question Jacek, Your method would only work so long as each PartA, PartB, and PartC all have the same tolerance numbers. if PartA and PartB had a tolerance of 20 but PartC didn't, your query would not show just the A and B tolerances. In fact, it wouldn't show a line for Tolerance 20 at all. The only way to do this in the pattern you describe is with the FULL OUTER JOIN predicate. MySql currently supports the INNER, LEFT, RIGHT, and NATURAL joins but not the FULL OUTER JOIN. There is a workaround for FULL OUTER JOIN if you need it but you have to be on a version that supports UNION queries to make it work without a temp table. The workaround also becomes very cumbersome if you are joining more than two tables. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Becla, Jacek [EMAIL PROTECTED] wrote on 10/04/2005 03:33:04 PM: Hi, One way of doing it would be: select a.tolerance, a.Cycles as PartA, b.Cycles as PartB, c.Cycles as PartC from t as a, t as b, t as c where a.tolerance=b.tolerance and a.tolerance=c.tolerance and a.PartName='A' and b.PartName='B' and c.PartName='C'; Jacek -Original Message- From: Roy Harrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 12:16 PM To: mysql@lists.mysql.com Subject: Query Question Suppose I have a simple table as follows: PartName Tolerance Cycles A 1 10 A 2 11 A 3 13 A 4 15 A 5 18 B 1 12 B 2 14 B 3 16 B 4 16 B 5 17 C 1 6 C 2 7 C 3 7 C 4 8 C 5 10 How do I set up a query whose output would look like this: Tolerance PartA PartB PartC 1 10 12 6 2 11 14 7 3 13 16 7 4 15 16 8 5 18 17 10 Thanks, Roy Harrell Adaptive Equipment 352.372.7821 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Roy, How do I set up a query whose output would look like this: Tolerance PartA PartB PartC 1 10 12 6 2 11 14 7 3 13 16 7 4 15 16 8 5 18 17 10 One way is... SELECT tolerance, MAX(CONCAT(IF(Partname='A', Cycles, '' ))) AS PartA, MAX(CONCAT(IF(Partname='B', Cycles, '' ))) AS PartB, MAX(CONCAT(IF(Partname='C', Cycles, '' ))) AS PartC FROM table GROUP BY tolerance; PB Roy Harrell wrote: Suppose I have a simple table as follows: PartNameTolerance Cycles A 1 10 A 2 11 A 3 13 A 4 15 A 5 18 B 1 12 B 2 14 B 3 16 B 4 16 B 5 17 C 1 6 C 2 7 C 3 7 C 4 8 C 5 10 How do I set up a query whose output would look like this: Tolerance PartA PartB PartC 1 10 12 6 2 11 14 7 3 13 16 7 4 15 16 8 5 18 17 10 Thanks, Roy Harrell Adaptive Equipment 352.372.7821 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.9/118 - Release Date: 10/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query - question
Eddie wrote: How can I join two tables looking like this? Table 1: ++---+---+ | Id | Name | Score | ++---+---+ Table 2: ++---+---+---+ | Id | Name | Score | Info | ++---+---+---+ To get output table like this: Table 2: ++---+---+-+ | Id | Name | Score | Info| ++---+---+-+ | ... table 1 and table 2 rows | | ... | ++---+---+-+ You can use UNION: (SELECT Id, Name, Score, NULL FROM Table1) UNION (SELECT Id, Name, Score, Info FROM Table2) Refer to http://dev.mysql.com/doc/mysql/en/union.html for more details. Regards, Eugene Kosov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Jack Lauman wrote: I have the following query which display every Cuisine in the database sorted by the WebsiteName. How can I modify this to get a COUNT of the number of records in each Cuisine in each WebsiteName? SELECT DISTINCT Restaurant.Cuisine, RestaurantWebsites.WebsiteName FROM Restaurant INNER JOIN RestaurantWebsites ON ( Restaurant.RestaurantID = RestaurantWebsites.RestaurantID ) WHERE RestaurantWebsites.WebsiteName = 'TOS' ORDER BY Cuisine ASC LIMIT 0 , 300 Thanks Jack Something like SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOS' GROUP BY w.WebsiteName, r.Cuisine; should do, if I understand the question. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question...
Jack Lauman wrote: Given the following query, how can it be modified to return 'Cuisine'(s) that have no rows as having a count of zero and also return a SUM of COUNT(*)? SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOW' GROUP BY w.WebsiteName, r.Cuisine Thanks. Jack After the where clause tack on: HAVING COUNT(*)0 This will eliminate the zero rows, but won't get you the sum. I think the only way to get the sum is to either run a second query, or to sum up the count column in your code as you're looping through the results. stipe42 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
count(*)? was: Re: Query Question...
Hi , This is a little off topic but I have seen count(*) on this list afew times and it got me wondering... Is there a reason to use SELECT COUNT(*) as opposed to SELECT COUNT(column)? I have noticed that selecting count(*) versus specifying the column name executes much more slowly. I've been around, on relative periphery, SQL and SQL DBMS' for a long time but never seen count(*) used. These days I am more involved with SQL (using MySQL) and want to learn more nuances as I go along. Cheers and thanks in advance, Nic On 7/16/05, stipe42 [EMAIL PROTECTED] wrote: Jack Lauman wrote: Given the following query, how can it be modified to return 'Cuisine'(s) that have no rows as having a count of zero and also return a SUM of COUNT(*)? SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOW' GROUP BY w.WebsiteName, r.Cuisine Thanks. Jack After the where clause tack on: HAVING COUNT(*)0 This will eliminate the zero rows, but won't get you the sum. I think the only way to get the sum is to either run a second query, or to sum up the count column in your code as you're looping through the results. stipe42 -- 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: count(*)? was: Re: Query Question...
I believe the difference is that count(*) includes nulls (because it is counting the number of records), whereas count(column) only counts the records where the column being counted is not null, regardless of the total number of rows. Hmm, on a related question then if I am correct above, does count(distinct column) count NULL as a distinct value or not? I.e. if I've got four records in a table with one column: (null, a, a, b), will count(distinct column) return 3 or 2? stipe42 Nic Stevens wrote: Hi , This is a little off topic but I have seen count(*) on this list afew times and it got me wondering... Is there a reason to use SELECT COUNT(*) as opposed to SELECT COUNT(column)? I have noticed that selecting count(*) versus specifying the column name executes much more slowly. I've been around, on relative periphery, SQL and SQL DBMS' for a long time but never seen count(*) used. These days I am more involved with SQL (using MySQL) and want to learn more nuances as I go along. Cheers and thanks in advance, Nic On 7/16/05, stipe42 [EMAIL PROTECTED] wrote: Jack Lauman wrote: Given the following query, how can it be modified to return 'Cuisine'(s) that have no rows as having a count of zero and also return a SUM of COUNT(*)? SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOW' GROUP BY w.WebsiteName, r.Cuisine Thanks. Jack After the where clause tack on: HAVING COUNT(*)0 This will eliminate the zero rows, but won't get you the sum. I think the only way to get the sum is to either run a second query, or to sum up the count column in your code as you're looping through the results. stipe42 -- 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: count(*)? was: Re: Query Question...
stipe42 wrote: I believe the difference is that count(*) includes nulls (because it is counting the number of records), whereas count(column) only counts the records where the column being counted is not null, regardless of the total number of rows. Right. COUNT(*) counts rows, COUNT(col) counts non-null values in col. Hmm, on a related question then if I am correct above, does count(distinct column) count NULL as a distinct value or not? I.e. if I've got four records in a table with one column: (null, a, a, b), will count(distinct column) return 3 or 2? COUNT() doesn't count NULLS. SELECT DISTINCT col FROM yourtable will return NULL, 'a', and 'b', but SELECT COUNT(DISTINCT col) FROM yourtable will return 2, because there are 2 non-NULL values to count. This is easily verified: mysql SELECT * FROM news; ++-+ | id | subject | ++-+ | 1 | cars| | 2 | toys| | 3 | books | | 4 | NULL| | 5 | toys| ++-+ 5 rows in set (0.00 sec) mysql SELECT COUNT(DISTINCT subject) FROM news; +-+ | COUNT(DISTINCT subject) | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql SELECT subject, COUNT(*), COUNT(subject) FROM news GROUP BY subject; +-+--++ | subject | COUNT(*) | COUNT(subject) | +-+--++ | NULL|1 | 0 | | books |1 | 1 | | cars|1 | 1 | | toys|2 | 2 | +-+--++ 4 rows in set (0.00 sec) stipe42 Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question...
stipe42 wrote: Jack Lauman wrote: Given the following query, how can it be modified to return 'Cuisine'(s) that have no rows as having a count of zero and also return a SUM of COUNT(*)? I'm sorry, but I'm having trouble picturing what you are doing. What is a Cuisine with no rows? I see a Restaurant table and a RestaurantWebsites table. I don't see a Cuisines table. Is there one? I would have expected one row per restaurant in a table named Restaurant, but it appears that a restaurant can have multiple Cuisines, with one row in Restaurant for each restaurant-cuisine combination. Is that right? Then how does RestaurantWebsites fit in? From your first query, it appears to link to certain cuisines of certain restaurants? In other words, I think we need more information to answer this. Carefully describe your tables (SHOW CREATE tablename would do) and how they relate to each other. SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOW' GROUP BY w.WebsiteName, r.Cuisine Thanks. Jack After the where clause tack on: HAVING COUNT(*)0 This will eliminate the zero rows, but won't get you the sum. You've got it backwards. There aren't any zero-count rows to eliminate from the given query, but he wants to change the query to *get* zero-count rows. I think the only way to get the sum is to either run a second query, or to sum up the count column in your code as you're looping through the results. You can get subtotals and the total using WITH ROLLUP (added in 4.1.1). See the manual for details http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html. stipe42 Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question...
Sorry... I was having a brain fart. (I use entries in the web.xml file to generate a dropdown list of cuisines). The field cuisine is part of the restaurant table. And it does not accept a null value. It should be split out into it's own table. I do need to get a SUM of all the values returned by COUNT(*) though. Here's an example of the results for the query below: WebsiteName Cuisine COUNT(*) TOS Afghan 1 TOS African 1 TOS American64 TOS Argentinean 1 TOS Asian9 TOS BBQ 7 The SUM of COUNT(*) in this case would be '83' SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOW' GROUP BY w.WebsiteName, r.Cuisine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question...
Jack Lauman wrote: Sorry... I was having a brain fart. (I use entries in the web.xml file to generate a dropdown list of cuisines). The field cuisine is part of the restaurant table. And it does not accept a null value. It should be split out into it's own table. I do need to get a SUM of all the values returned by COUNT(*) though. Here's an example of the results for the query below: WebsiteName Cuisine COUNT(*) TOS Afghan 1 TOS African 1 TOS American64 TOS Argentinean 1 TOS Asian9 TOS BBQ 7 The SUM of COUNT(*) in this case would be '83' SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOW' GROUP BY w.WebsiteName, r.Cuisine I don't believe you've specified which version of mysql you have. (Sorry if I missed it.) If you are using 4.1, you could add WITH ROLLUP to the query to get the total(s). SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID GROUP BY w.WebsiteName, r.Cuisine WITH ROLLUP; WebsiteName Cuisine COUNT(*) TOS Afghan1 TOS African 1 TOS American 64 TOS Argentinean 1 TOS Asian 9 TOS BBQ 7 TOS NULL 83 TOW Afghan3 TOW African 2 TOW American 37 TOW Argentinean 2 TOW Asian13 TOW BBQ 5 TOW NULL 62 NULL NULL145 At the end of each WebsiteName, you get an extra row with NULL in the Cuisine column and the subtotal for that group in the COUNT(*) column. At the end, you get another extra line with 2 NULLs and the grand total in the COUNT(*) column. See the manual for more http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html. (I left out your WHERE clause, WHERE w.WebsiteName = 'TOS', for my example. If I'd left it in, the output would be missing the TOW rows, and the grand total would have been the same as the TOS subtotal.) If you still have 4.0 (or earlier), you can either save the result of your query in a temporary table and add the counts with a second query, or simply add the counts in your application. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question
Hi, If i understand : select month(entryDate) as monthPart, if (amount is nul,'',day(entryDate) ) as dayPart, amount from raindata order by dayPart, monthPart Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: mercredi 27 avril 2005 22:24 To: mysql@lists.mysql.com Subject: query question I have a table that the important parts look something like: keynum int, entryDate datetime, amount varchar(10) What I want to do is a query that gets me every day of the year and just has null values for the days that don't have anything in the amount column. Is something like that possible with sql? In fact, what I would really like is: select month(entryDate) as monthPart, day(entryDate) as dayPart, amount from raindata order by dayPart, monthPart just with the whole year filled in. it will make my later code simplier if I can not have to test for values as much. --ja -- -- 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: query question
JA, To have a SELECT statement generate a row for every day in the year, either your raindata table needs a row for every day in the year, or you need another table which has a row for every day of the year. Supposing you have such a table, call it 'calendar' with a date column named 'yearday', then you could retrieve daily rainfall including NULLs with SELECT calendar.yearday, rainfall.amount FROM calendar LEFT JOIN rainfall ON calendar.yearday = raindata.entrydate ORDER BY calendar.yearday; or if there can be multiple raindata rows for a date then SELECT calendar.yearday, SUM( rainfall.amount ) FROM calendar LEFT JOIN rainfall ON calendar.yearday = raindata.entrydate GROUP BY calendar.yearday; but your rainfall column ought to be numeric. Peter Brawley http://www.artfulsoftware.com - [EMAIL PROTECTED] wrote: I have a table that the important parts look something like: keynum int, entryDate datetime, amount varchar(10) What I want to do is a query that gets me every day of the year and just has null values for the days that don't have anything in the amount column. Is something like that possible with sql? In fact, what I would really like is: select month(entryDate) as monthPart, day(entryDate) as dayPart, amount from raindata order by dayPart, monthPart just with the whole year filled in. it will make my later code simplier if I can not have to test for values as much. --ja -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
If my englsih is so bad, i'll try to explain and stop this thread now. That's not what was being said. I'm not teaching, i'm answering questions. If someone wants to read docs, he (she) doesn't ask a question on the list. So if i answer, i answer the question, just the question. You want to know my level of knowledgne, 10 years, oracle, sybase, sqlserver, db2. I can help for migration from or to... I said don't use joins for the query given in the example or queries using just the joining columns from the first table. Normal forms is bla bla here ... 10 yrs? Time to read a book then. -- Martijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Yes ten years and forgot mysql certified. I can offer i-am-a-dummy to you if you lack. I've never imagined find so bad people on the list. But i'll write to the moderator to see who is on. But i'm pleased to help people wihout naz mentality than yours. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 12:29 To: mysql@lists.mysql.com Subject: Re: Query question If my englsih is so bad, i'll try to explain and stop this thread now. That's not what was being said. I'm not teaching, i'm answering questions. If someone wants to read docs, he (she) doesn't ask a question on the list. So if i answer, i answer the question, just the question. You want to know my level of knowledgne, 10 years, oracle, sybase, sqlserver, db2. I can help for migration from or to... I said don't use joins for the query given in the example or queries using just the joining columns from the first table. Normal forms is bla bla here ... 10 yrs? Time to read a book then. -- Martijn -- 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: Query question
Mathias There are no *bad* people on this list - different point of view, yes. Participating on this and other lists requires give AND take - taking advice as well as giving it... Participating is always going to be a two way process so just accept it, and if you can't - unsubscribe. Hope this helps Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
sorry Chris again, i mean in what they speak about. i try help if i can, just that. :o) Mathias Selon Chris Ramsay [EMAIL PROTECTED]: Mathias There are no *bad* people on this list - different point of view, yes. Participating on this and other lists requires give AND take - taking advice as well as giving it... Participating is always going to be a two way process so just accept it, and if you can't - unsubscribe. Hope this helps Chris -- 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: Query question
Jeff McKeon [EMAIL PROTECTED] wrote on 04/25/2005 03:00:55 PM: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff This is a FAQ. What you have is the groupwise maximum problem described here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Except in your case you are not looking for max price for a dealer, you are looking for the latest date for a given parentid. Let us know if you need more details and someone on the list will be happy to help!! :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query question
Hi, You can do something like that : mysql select * from son; +--+ | a| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.02 sec) mysql select * from mother; +--+--+ | a| b| +--+--+ |1 | a| |1 | b| |2 | a| |2 | c| |3 | a| |3 | b| |3 | c| |3 | d| +--+--+ 8 rows in set (0.00 sec) mysql select a,max(b) from mother - group by a; +--++ | a| max(b) | +--++ |1 | b | |2 | c | |3 | d | +--++ 3 rows in set (0.00 sec) The max will be used with your datetime column. The son table can not be used, or joined to the mother. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:01 To: mysql@lists.mysql.com Subject: Query question I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- 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: Query question
mathias fatene [EMAIL PROTECTED] wrote on 04/25/2005 03:19:33 PM: Hi, You can do something like that : mysql select * from son; +--+ | a| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.02 sec) mysql select * from mother; +--+--+ | a| b| +--+--+ |1 | a| |1 | b| |2 | a| |2 | c| |3 | a| |3 | b| |3 | c| |3 | d| +--+--+ 8 rows in set (0.00 sec) mysql select a,max(b) from mother - group by a; +--++ | a| max(b) | +--++ |1 | b | |2 | c | |3 | d | +--++ 3 rows in set (0.00 sec) The max will be used with your datetime column. The son table can not be used, or joined to the mother. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:01 To: mysql@lists.mysql.com Subject: Query question I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff I think I am decent at what I do and that confused even me. I am totally baffled at what SQL concept you were trying to illustrate. How did you _help_ the OP? The question that started this thread is an example of a common class of SQL problems and several solutions exist. Your solution neither answered his query nor was it explained to the point that made it comprehendable. Please, please try to be less confusing (especially when responding to newbies). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query question
Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Hi, Why my answer doesn't answer his question. Did you heared about his comment. Let him do it. If you're confused, i can explain more one-to-many relashionships. If you think about joins and want absolutely add them, this is the error generating performance problems asked along all RDMBS, especially with mysql (DBMS till now). Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:44 To: mathias fatene Cc: 'Jeff McKeon'; mysql@lists.mysql.com Subject: RE: Query question mathias fatene [EMAIL PROTECTED] wrote on 04/25/2005 03:19:33 PM: Hi, You can do something like that : mysql select * from son; +--+ | a| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.02 sec) mysql select * from mother; +--+--+ | a| b| +--+--+ |1 | a| |1 | b| |2 | a| |2 | c| |3 | a| |3 | b| |3 | c| |3 | d| +--+--+ 8 rows in set (0.00 sec) mysql select a,max(b) from mother - group by a; +--++ | a| max(b) | +--++ |1 | b | |2 | c | |3 | d | +--++ 3 rows in set (0.00 sec) The max will be used with your datetime column. The son table can not be used, or joined to the mother. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 21:01 To: mysql@lists.mysql.com Subject: Query question I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff I think I am decent at what I do and that confused even me. I am totally baffled at what SQL concept you were trying to illustrate. How did you _help_ the OP? The question that started this thread is an example of a common class of SQL problems and several solutions exist. Your solution neither answered his query nor was it explained to the point that made it comprehendable. Please, please try to be less confusing (especially when responding to newbies). Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Here we are Shawn, With empty tables : +++---+--+---+--+--- --+--+--+-+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+---+--+--- --+--+--+-+ | 1 | PRIMARY| a | ALL | NULL | NULL | NULL | NULL |0 | Using where | | 2 | DEPENDENT SUBQUERY | b | ALL | NULL | NULL | NULL | NULL |0 | Using where | +++---+--+---+--+--- --+--+--+-+ 2 rows in set (0.00 sec) mysql mysql explain select parentid,max(datestamp) from table2 - group by parentid; ++-++--+---+--+-+--- ---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+-+--- ---+--+-+ | 1 | SIMPLE | table2 | ALL | NULL | NULL |NULL | NULL |0 | Using temporary; Using filesort | ++-++--+---+--+-+--- ---+--+-+ One or two table scans ? Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 22:01 To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- 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: Query question
Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Hi, Im sorry to disappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 22:17 To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
RE: Query question
Peter, I'm unfamiliar with the @d := section you describe. Is this psudo code or real syntax? thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:17 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
Re: Query question
Title: Message Mathias, Im sorry todisappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). This 2-query solution uses neither a join nor a subquery. What do you mean? PB - mathias fatene wrote: Hi, Im sorry todisappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: lundi 25 avril 2005 22:17 To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Jeff McKeon [EMAIL PROTECTED] wrote on 04/25/2005 04:08:29 PM: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 OK, then you need to collect your child-table maximums in one pass and build your actual query in the second (the non-subquery version of the example I sent). Let's find all of the child records where user not like 'john'. ( I will exclude all users whose name starts with 'john') CREATE TEMPORARY TABLE lastRecords SELECT parentID, max(datetime_field_name_here) as latest FROM table2 WHERE user NOT LIKE 'john%' GROUP BY parentID; You had to exclude 'john' at this stage because you want the latest child record that isn't 'john'. Make sense? Of course, you will need to adjust this to meet whatever conditions you really want. SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN lastRecords r ON r.parentID = t1.id LEFT JOIN table2 t2 ON t2.parentID = r.parentID AND t2.datetime_field_name_here = r.latest; That will give you all of the records from table1 and only the most recent record from table2 (if it even exists). I used the LEFT JOIN (not an INNER JOIN) so that you can see all of the records from table1. If I had used INNER JOINs you would have only seen those records that matched up with the conditions you placed on table2. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query question
Title: Message
That's real syntax for inline assignment of a column value to a user
variable. What MySQL version are you using?
PB
Jeff McKeon wrote:
Peter,
I'm unfamiliar with the "@d := " section
you describe. Is this psudo code or real syntax?
thanks,
Jeff
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 25, 2005 4:17 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question
Jeff,
Then do it with 2 queries,
SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;
SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];
PB
-
Jeff McKeon wrote:
Thanks all but I don't have a mysql version high enough for subqueries.
Thanks,
Jeff
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question
Jeff,
Something like ...
SELECT *
FROM table2 AS a
WHERE datestamp = (
SELECT MAX( b.datestamp )
FROM table2 AS b
WHERE a.parentID = b.parentID
);
PB
-
Jeff McKeon wrote:
I have a table that contains records that link back to a
main talbe in
a many to one configuration linked by table1.id = table2.parentid
Table1 (one)
Table2 (many)
I want to pull the latest records from table2 for each
record in table1
where certain criteria applie.
So, if record 100 in table1 links to 5 corresponding records
in table2,
I want to pull the latest record from table2 where table2.parentid =
100 and table2.user not like 'john'
There is a datestamp field in table2.
I just can't figure out how to do this.
Thanks,
Jeff
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
3.23. no control over this right now or i'd upgrade, believe me! jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:43 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question That's real syntax for inline assignment of a column value to a user variable. What MySQL version are you using? PB Jeff McKeon wrote: Peter, I'm unfamiliar with the @d := section you describe. Is this psudo code or real syntax? thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:17 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Then do it with 2 queries, SELECT @d := MAX( datestamp ) FROM table2 WHERE parentID = X; SELECT * FROM table2 WHERE parentID = X AND [EMAIL PROTECTED]; PB - Jeff McKeon wrote: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff
Re: Query question
Title: Message
Jeff,
3.23.
no control over this right now or i'd upgrade, believe me!
Yep, I maintain websites with the same
problem. Shawn Green just posted a solution that doesn't need inline
user variable assignment.
PB
-
Jeff McKeon wrote:
3.23.
no control over this right now or i'd upgrade,
believe me!
jeff
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 25, 2005 4:43 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question
That's real syntax for inline assignment of a column value to a user
variable. What MySQL version are you using?
PB
Jeff McKeon wrote:
Peter,
I'm unfamiliar with the "@d := " section
you describe. Is this psudo code or real syntax?
thanks,
Jeff
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 25, 2005 4:17 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question
Jeff,
Then do it with 2 queries,
SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;
SELECT *
FROM table2
WHERE parentID = X AND [EMAIL PROTECTED];
PB
-
Jeff McKeon wrote:
Thanks all but I don't have a mysql version high enough for subqueries.
Thanks,
Jeff
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@lists.mysql.com
Subject: Re: Query question
Jeff,
Something like ...
SELECT *
FROM table2 AS a
WHERE datestamp = (
SELECT MAX( b.datestamp )
FROM table2 AS b
WHERE a.parentID = b.parentID
);
PB
-
Jeff McKeon wrote:
I have a table that contains records that link back to a
main talbe in
a many to one configuration linked by table1.id = table2.parentid
Table1 (one)
Table2 (many)
I want to pull the latest records from table2 for each
record in table1
where certain criteria applie.
So, if record 100 in table1 links to 5 corresponding records
in table2,
I want to pull the latest record from table2 where table2.parentid =
100 and table2.user not like 'john'
There is a datestamp field in table2.
I just can't figure out how to do this.
Thanks,
Jeff
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
mathias fatene [EMAIL PROTECTED] wrote on 04/25/2005 04:24:42 PM: Hi, Im sorry to disappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer snip Mathias, I do appreciate your energy and willingness to contribute to the list. I am not affiliated with MySQL or any of its subsidiaries and I have no special privileges to police what happens on this list. I am a fellow contributor just as you. With that said, I feel that I must seriously question your level of experience and ability to form useful responses. When you say and don't use joins if you can (all data in the mother table), It seems to me that you are proposing that in order to eliminate JOINs in queries that all data should be flattened into one single table. Not only is this incorrect advice but it undermines the many reasons for using a relational database system (RDBMS) in the first place. I would love to compare the performance of a properly normalized and indexed relational data structure against a single flat table for all but the most trivial of data sets. The nomalized data will not only take up less room on the disk but it will perform extremely well (especially for larger data sets). The single-table model you proposed will not scale to more than a few hundred thousand rows before the table's size becomes a bottleneck. Some queries will take a long time to finish against 30 million row tables, even with good indexes on them. Your extreme counter example was a non-starter. The original poster acknowledges that they are new (no offence intended) and I feel that your posts were hardly helpful at best and most likely counter-productive. Please, take the time to read your ansers from the perspective of the person you are responding to. Try to keep in mind not only their language skills (as this is a multi-national list) but their experience level and even sometimes their age (we have many students looking for help on here and some of them are still teenagers). Please be more accurate, thoughtful, and descriptive the next time you post, OK? With greatest humility, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query question
thanks, I'll give that a try tomorrow. :o) Jeffrey S. McKeon Manager of Information Technology Telaurus Communications LLC [EMAIL PROTECTED] +1 (973) 889-8990 ex 209 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:36 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: RE: Query question Jeff McKeon [EMAIL PROTECTED] wrote on 04/25/2005 04:08:29 PM: Thanks all but I don't have a mysql version high enough for subqueries. Thanks, Jeff -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, April 25, 2005 4:01 PM To: Jeff McKeon Cc: mysql@lists.mysql.com Subject: Re: Query question Jeff, Something like ... SELECT * FROM table2 AS a WHERE datestamp = ( SELECT MAX( b.datestamp ) FROM table2 AS b WHERE a.parentID = b.parentID ); PB - Jeff McKeon wrote: I have a table that contains records that link back to a main talbe in a many to one configuration linked by table1.id = table2.parentid Table1 (one) Table2 (many) I want to pull the latest records from table2 for each record in table1 where certain criteria applie. So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' There is a datestamp field in table2. I just can't figure out how to do this. Thanks, Jeff -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 OK, then you need to collect your child-table maximums in one pass and build your actual query in the second (the non-subquery version of the example I sent). Let's find all of the child records where user not like 'john'. ( I will exclude all users whose name starts with 'john') CREATE TEMPORARY TABLE lastRecords SELECT parentID, max(datetime_field_name_here) as latest FROM table2 WHERE user NOT LIKE 'john%' GROUP BY parentID; You had to exclude 'john' at this stage because you want the latest child record that isn't 'john'. Make sense? Of course, you will need to adjust this to meet whatever conditions you really want. SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN lastRecords r ON r.parentID = t1.id LEFT JOIN table2 t2 ON t2.parentID = r.parentID AND t2.datetime_field_name_here = r.latest; That will give you all of the records from table1 and only the most recent record from table2 (if it even exists). I used the LEFT JOIN (not an INNER JOIN) so that you can see all of the records from table1. If I had used INNER JOINs you would have only seen those records that matched up with the conditions you placed on table2. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query question
Hi, If my englsih is so bad, i'll try to explain and stop this thread now. I'm not teaching, i'm answering questions. If someone wants to read docs, he (she) doesn't ask a question on the list. So if i answer, i answer the question, just the question. You want to know my level of knowledgne, 10 years, oracle, sybase, sqlserver, db2. I can help for migration from or to... I said don't use joins for the query given in the example or queries using just the joining columns from the first table. Normal forms is bla bla here ... See also about covering indexes. That can help. This is the query given by Jeff : So, if record 100 in table1 links to 5 corresponding records in table2, I want to pull the latest record from table2 where table2.parentid = 100 and table2.user not like 'john' The only clause is about table2.parentid = 100 or child.id = 100. that's the same. All the other clauses are on table2. This is the exampel given by Jeff. If you want absolutely LEFT outer joins for that (without other columns from table1), i say you good luck, this can (also) do the trick. That's all. - If you give me real examples, i can help you to give you to find the right (if i can) query plan. Tuning is my first target when i think a query. I never suggest nested loops, but relationnal algebra. I'm not supposed speeking to students but DBAs, for specific question. Sorry if i run up against your sensitivity, but we are not speaking about the same thing. And please if you have to criticize or complete an answer, it's your right. The list is for that. If you want to speak to me as your student, this is enough. I never did it when i was teacher 11 years ago. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 23:02 To: mathias fatene Cc: 'Jeff McKeon'; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Query question mathias fatene [EMAIL PROTECTED] wrote on 04/25/2005 04:24:42 PM: Hi, Im sorry to disappoint you but this is an anti-performance solution. Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer snip Mathias, I do appreciate your energy and willingness to contribute to the list. I am not affiliated with MySQL or any of its subsidiaries and I have no special privileges to police what happens on this list. I am a fellow contributor just as you. With that said, I feel that I must seriously question your level of experience and ability to form useful responses. When you say and don't use joins if you can (all data in the mother table), It seems to me that you are proposing that in order to eliminate JOINs in queries that all data should be flattened into one single table. Not only is this incorrect advice but it undermines the many reasons for using a relational database system (RDBMS) in the first place. I would love to compare the performance of a properly normalized and indexed relational data structure against a single flat table for all but the most trivial of data sets. The nomalized data will not only take up less room on the disk but it will perform extremely well (especially for larger data sets). The single-table model you proposed will not scale to more than a few hundred thousand rows before the table's size becomes a bottleneck. Some queries will take a long time to finish against 30 million row tables, even with good indexes on them. Your extreme counter example was a non-starter. The original poster acknowledges that they are new (no offence intended) and I feel that your posts were hardly helpful at best and most likely counter-productive. Please, take the time to read your ansers from the perspective of the person you are responding to. Try to keep in mind not only their language skills (as this is a multi-national list) but their experience level and even sometimes their age (we have many students looking for help on here and some of them are still teenagers). Please be more accurate, thoughtful, and descriptive the next time you post, OK? With greatest humility, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
SELECT product_lines.* FROM product_lines LEFT JOIN manufacturer_product_line_index ON manufacturer_product_line_index.product_line_id = product_lines.id WHERE product_lines.id IS NULL -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 11:39 AM To: mysql@lists.mysql.com Subject: Query question Three tables like this: -- product_lines -- id title -- manufacturer -- id title -- manufacturer_product_line_index -- id product_line_id manufacturer_id The index provides a one to many relationship - one product line can be associated with more than one company. How do I get a list of product lines not in the index? Thanks, Ed -- 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: Query question
Ed Lazor [EMAIL PROTECTED] wrote on 04/07/2005 12:39:01 PM: Three tables like this: -- product_lines -- id title -- manufacturer -- id title -- manufacturer_product_line_index -- id product_line_id manufacturer_id The index provides a one to many relationship - one product line can be associated with more than one company. How do I get a list of product lines not in the index? Thanks, Ed To list all of the products without any entries in your index table: SELECT p.id, p.title FROM product_lines p LEFT JOIN manufacturer_product_line_index i on i.product_line_id = p.id WHERE i.id is null; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Query question
Whew, thanks Jon =) -Original Message- SELECT product_lines.* FROM product_lines LEFT JOIN manufacturer_product_line_index ON manufacturer_product_line_index.product_line_id = product_lines.id WHERE product_lines.id IS NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Jerry Swanson [EMAIL PROTECTED] wrote on 03/29/2005 11:43:56 AM:
I want to get everything from user than if record exist in admin so
user has admin(administrator) in table user with user.id =
admin.admin_id, so I need to get 'admin' first_name and last_name
If there is no record in table admin with adin.user_id = user.id ,
than I need at least all records from user
Table: user
| id| int(10) | | PRI | NULL
| auto_increment |
| email | varchar(100)| YES | | NULL
||
| password | varchar(45) | YES | | NULL
||
| first_name| varchar(100)| YES | | NULL
||
| last_name | varchar(100)| YES | | NULL
||
| type | enum('admin','user')| YES | | NULL
| auto_increment |
Table: admin
| id| int(10) | | PRI | NULL| auto_increment |
| admin_id | int(10) | YES | | NULL||
| user_id | int(10) | YES | | NULL||
| date | datetime | YES | | NULL||
I think I understand your need: for all users, list the user's name and
the name of their administrator, if an administrator exists
SELECT u.type
, u.first_name
, u.last_name
, au.first_name as admin_first
, au.last_name as admin_last
FROM user u
LEFT JOIN admin a
on a.user_id = u.id
LEFT JOIN user au
on au.id = a.admin_ID;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Query question
This will return the top 50 urls in descending order of popularity. SELECT URL, count(1) as popularity FROM yourtablename GROUP BY URL ORDER BY popularity DESC LIMIT 50; Feel free to adjust as needed. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Lazor [EMAIL PROTECTED] wrote on 12/23/2004 01:45:30 PM: I use a table to log what pages on the website are getting visits with a table structure like this: ID DateAdded URL Now I'm trying to query the database to see which URLs are most popular, but I'm not sure how to go about doing this. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Ed, Try the following query select ID, DateAdded, URL, count(*) as 'cnt' from mytable group by URL order by cnt desc It should display the most numerous URLs in the table. dimitar -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, December 23, 2004 1:46 PM To: 'mysql' Subject: Query question I use a table to log what pages on the website are getting visits with a table structure like this: ID DateAdded URL Now I'm trying to query the database to see which URLs are most popular, but I'm not sure how to go about doing this. Any ideas? Thanks, Ed -- 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: Query question
Thanks, Shawn. I didn't think count would just limit to the items being grouped - very handy =) -Ed SELECT URL, count(1) as popularity FROM yourtablename GROUP BY URL ORDER BY popularity DESC LIMIT 50; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Quite possibly since 0 could also mean false depending on your comparison operator. For instance, using a generic if statement, these two would both evaluate to false: if(0) if(null) You should be very specific when checking for NULL. WHERE field IS NOT NULL or WHERE field IS NULL Also, you may want to look into the NULL safe comparison operator: http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html On Nov 16, 2004, at 7:17 AM, Stuart Felenstein wrote: I have a query statement set up for record returns based on various where statements. The select statement consists of a number of joins. One of those joins includes a field that is marked no null. Recently I did a mass insertion into the table. Into this particular no null field were place 0's (zeroes). Now the queries are not running correctly. I'm wondering if it's possible that these 0's could be effecting the query ? Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
I think I'm on the right track but still in question After all the joins I added a and LocationState = x. I'm not totally sure, because I want to search for records based on (for now)3 conditions (state, city, industry). Two things I should mention , the somewhat strange notation is becaue I'm using one of dem fancy visual query editors. Secondly, I'm using this query in a web page that receives the codes via url / variables. So, and I hope I don't get slammed for talking web dev here. But the problem is someone may choose one field and not another , so I want a return on whether they chose 1 or all 3 variables. Hope that makes sense. i.e. They choose the state, but leave the city and industy blank. The way it's set up now with and ..., and ..., and it will only return a record if I put in all 3. If I do an OR, then a second on any additonal OR's would get bypassed if I understand correctly. Sorry, I might just be thinking outloud. Please do not boot. Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: -- SELECT `VendorJobs`.`JobID`, `VendorJobs`.`Entered`, `VendorSignUp`.`CompanyName`, `StaIndTypes`.`CareerCategories`, `StaUSCities`.`City`, `USStates`.`States`, `VendorJobs`.`AreaCode`, `staTaxTerm`.`TaxTerm`, `VendorJobs`.`PayRate`, `staTravelReq`.`TravelReq`, `VendorJobDetails`.`JobTitle`, `VendorJobDetails`.`Details`, `VendorJobs`.`PostStart` FROM `VendorJobs` INNER JOIN `VendorSignUp` ON (`VendorJobs`.`VendorID` = `VendorSignUp`.`VendorID`) INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` = `StaIndTypes`.`CareerIDs`) LEFT OUTER JOIN `StaUSCities` ON (`VendorJobs`.`LocationCity` = `StaUSCities`.`CityID`) LEFT OUTER JOIN `USStates` ON (`VendorJobs`.`LocationState` = `USStates`.`StateID`) LEFT OUTER JOIN `staTaxTerm` ON (`VendorJobs`.`TaxTerm` = `staTaxTerm`.`TaxTermID`) INNER JOIN `staTravelReq` ON (`VendorJobs`.`TravelReq` = `staTravelReq`.`TravelReqID`) INNER JOIN `VendorJobDetails` ON (`VendorJobs`.`JobID` = `VendorJobDetails`.`JobID`) -- The where condition is going to have multiple AND's (I've considered UNION but don't think they are appropriate here) First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the where's but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. What's confusing me is the join, and how the join maintains the integrity of the record. If that makes sense, cause what I just said, doesn't to me. Yet, that's the only way I could say it for now. Alrighty, flame away Stuart -- 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: Query Question
Stuart Felenstein wrote: I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: query reformatted so I could read it -- SELECT VJ.JobID, VJ.Entered, VSU.CompanyName, StaIndTypes.CareerCategories, StaUSCities.City, USStates.States, VJ.AreaCode, staTaxTerm.TaxTerm, VJ.PayRate, staTravelReq.TravelReq, VendorJobDetails.JobTitle, VendorJobDetails.Details, VJ.PostStart FROM VendorJobs VJ INNER JOIN VendorSignUp VSU ON VJ.VendorID = VSU.VendorID INNER JOIN StaIndTypes ON VJ.Industry = StaIndTypes.CareerIDs LEFT JOIN StaUSCities ON VJ.LocationCity = StaUSCities.CityID LEFT JOIN USStates ON VJ.LocationState = USStates.StateID LEFT JOIN staTaxTerm ON VJ.TaxTerm = staTaxTerm.TaxTermID INNER JOIN staTravelReq ON VJ.TravelReq = staTravelReq.TravelReqID INNER JOIN VendorJobDetails ON VJ.JobID = VendorJobDetails.JobID -- The where condition is going to have multiple ANDs (I've considered UNION but don't think they are appropriate here) AND and UNION are opposites. ANDs narrow your results, because only rows which match all AND conditions are selected. UNION, like OR, increases your result set, because rows only have to match any one of the conditions. That is, SELECT * FROM atable WHERE a = 1 OR b = 2; is equivalent to SELECT * FROM atable WHERE a = 1 UNION SELECT * FROM atable WHERE b = 2; See the manual for details http://dev.mysql.com/doc/mysql/en/UNION.html. First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the wheres but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. `VendorJobs`.`CareerCategories` = Finance gives you an *error* (as opposed to no match), because you have no column named Finance. You (mysql) can tell Finance is the name of a column because it has no quotes. Of course, you meant to compare VendorJobs.CareerCategories to the constant string 'Finance', so you should use WHERE `VendorJobs`.`CareerCategories` = 'Finance' ... But, your condition should be WHERE column_name = 'a value in that column' ... So, if the string 'Fin' is what is actually stored in the CareerCategories column, you need WHERE `VendorJobs`.`CareerCategories` = 'Fin' ... I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. OK, now I'm confused. Which column of which table contains the string 'Finance' or 'Fin'? Oh, is 'Fin' an ID for the row in StaIndTypes where the full string 'Finance' resides? If that's the case, since you are joining to StaIndTypes, either match (VendorJobs.CareerCategories` = 'Fin' or StaIndTypes = 'Finance') should work. What's confusing me is the join, and how the join maintains the integrity of the record. If that makes sense, cause what I just said, doesn't to me. Yet, that's the only way I could say it for now. Can you elaborate? I could answer the question I *think* you're asking, but that might be a waste. What about joins is confusing you? Alrighty, flame away No flames. I originally learned mysql by reading the manual, reading the list, working on a mysql/php/apache project, and asking questions. There's nothing wrong with that. The trick is not to let your development race too far ahead of your understanding, so as to minimize the rewriting you have to do whenever you say, Aha! Now I get it. Stuart Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Well I feel like maybe I wasted some bandwidth here. I think what I'm looking for is a square peg in a round hole. That won't work. More to the point :) , I do not having a problem with the AND / OR / IN / NOT / etc. What I think I was attempting was to come up with a SQL statement that will work with an unknown factor. I'm now thinking that this isn't the path to take. Here is where I'm at, and as this goes into web dev, I am trying to figure out if I have any choices strictly using SQL. I have 3 choices (3 seperate fields to query) a user can submit, but none are strictly required. Chances are only 1 will be used. If I do a where x = 1 or y = 2 or z = 3 then regardless of the y or z, x is coming back. It's not even looking at y or z. All AND requires all three conditions are met. Lastly I was interested in NOT, since by default there is an assigned value to the non used form field. Yet, all of my where conditions are = resset1... which is the variable that gets passed over. Not sure how to say where LocationState NOT XXX and leave the resset in place. Sorry if this is all confusing. I am taking my time and wouldn't think of just throwing something up unless it works in the 999,999,999 ways it should. Thank you, Stuart --- Michael Stassen [EMAIL PROTECTED] wrote: Stuart Felenstein wrote: I'm hoping I can present this correctly. I'm trying to determine how to set up my where condition as, 1 way has already failed me. While I continue to figure this out (i'm a noob), I hope asking for some advice here won't be too awful. There is one main table where data is inserted and that I'm querying against, but this main table is comprised of ID's from other static tables. VendorJobs is the main table, here is the select and from's: query reformatted so I could read it -- SELECT VJ.JobID, VJ.Entered, VSU.CompanyName, StaIndTypes.CareerCategories, StaUSCities.City, USStates.States, VJ.AreaCode, staTaxTerm.TaxTerm, VJ.PayRate, staTravelReq.TravelReq, VendorJobDetails.JobTitle, VendorJobDetails.Details, VJ.PostStart FROM VendorJobs VJ INNER JOIN VendorSignUp VSU ON VJ.VendorID = VSU.VendorID INNER JOIN StaIndTypes ON VJ.Industry = StaIndTypes.CareerIDs LEFT JOIN StaUSCities ON VJ.LocationCity = StaUSCities.CityID LEFT JOIN USStates ON VJ.LocationState = USStates.StateID LEFT JOIN staTaxTerm ON VJ.TaxTerm = staTaxTerm.TaxTermID INNER JOIN staTravelReq ON VJ.TravelReq = staTravelReq.TravelReqID INNER JOIN VendorJobDetails ON VJ.JobID = VendorJobDetails.JobID -- The where condition is going to have multiple ANDs (I've considered UNION but don't think they are appropriate here) AND and UNION are opposites. ANDs narrow your results, because only rows which match all AND conditions are selected. UNION, like OR, increases your result set, because rows only have to match any one of the conditions. That is, SELECT * FROM atable WHERE a = 1 OR b = 2; is equivalent to SELECT * FROM atable WHERE a = 1 UNION SELECT * FROM atable WHERE b = 2; See the manual for details http://dev.mysql.com/doc/mysql/en/UNION.html. First I should say that run as a complete dump, it returns all the records correctly , with all the id's translated into the correct lable. i.e State, CA is stored in VendorJobs as CA, but in the return (and this isn't the greatest example) it's California. So now I want to add the wheres but doing: where `VendorJobs`.`CareerCategories` = Finance is returning an error. I think because VendorJobs only knows Finance by Fin. `VendorJobs`.`CareerCategories` = Finance gives you an *error* (as opposed to no match), because you have no column named Finance. You (mysql) can tell Finance is the name of a column because it has no quotes. Of course, you meant to compare VendorJobs.CareerCategories to the constant string 'Finance', so you should use WHERE `VendorJobs`.`CareerCategories` = 'Finance' ... But, your condition should be WHERE column_name = 'a value in that column' ... So, if the string 'Fin' is what is actually stored in the CareerCategories column, you need WHERE `VendorJobs`.`CareerCategories` = 'Fin' ... I think what it should be : StaIndTypes`.`CareerCategories` = Finance I could be wrong, about to try it. OK, now I'm confused. Which column of which table contains the string 'Finance' or 'Fin'? Oh, is 'Fin' an ID for the row in StaIndTypes where the full string 'Finance' resides? If that's the case, since you are joining to StaIndTypes, either match (VendorJobs.CareerCategories` = 'Fin' or StaIndTypes = 'Finance') should work. What's confusing me is the join, and how the join
RE: Query question
Select count(distinct(field)) from table where field = 0 ? -Original Message- From: Laercio Xisto Braga Cavalcanti [mailto:[EMAIL PROTECTED] Sent: Monday, May 24, 2004 11:18 PM To: 'John Nichel'; 'MySQL List' Subject: RE: Query question You can do: Select count(distinct(field)) from table where field 0 Laercio. -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 24 de maio de 2004 14:37 To: MySQL List Subject: Query question Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. -- John C. Nichel KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
