Table/select problem...
Greetings, all... I'm having an issue with a SELECT in our system. We have an event log table, with about 9 million rows in it. Inserts happen with some pretty high frequency, and these selects happen periodically. The event_log table is MyISAM, the rest of the tables are InnoDB. What's happening is that, periodically, when this select gets run, the whole damn thing locks up, and that pretty much shuts us down (since many things insert events into the table, and the table gets locked, so all the inserts hang). The statement and the explain for it are below. the enduser table has about a million rows in it, the event_type table 35 rows. The weird part is that, if I strip down the query to use no joins, the explain wants to return about 17,000 rows, but the query itself does the table locking thing. Should we perhaps change the event log to InnoDB to avoid table locking? Might the table itself be corrupt in some way? Any thoughts? thanks, andy EXPLAIN SELECT EL.event_log_id, EL.event_time, DATE_FORMAT(EL.event_time, '%c-%d-%Y %H:%i:%s') as 'time_formatted', ET.event_type_id, ET.description, EL.csr_name, EL.enduser_acnt, EL.csr_name, EL.referer, EL.mls_id, EL.mls_no, EL.ss_id, EL.details, E.fname, E.lname, E.email, E.phone1 FROM event_log EL JOIN event_type ET ON EL.event_type_id = ET.event_type_id JOIN enduser E ON EL.enduser_acnt = E.enduser_acnt WHERE EL.acnt = 'AR238156' AND EL.enduser_acnt != '' AND EL.event_type_id = 'EndUserLogin' AND event_time BETWEEN DATE_SUB(CURDATE(), INTERVAL '7' DAY) AND NOW() ORDER BY EL.event_time DESC *** 1. row *** id: 1 select_type: SIMPLE table: ET type: const possible_keys: PRIMARY key: PRIMARY key_len: 92 ref: const rows: 1 Extra: Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: EL type: index_merge possible_keys: agent,enduser,event_log_ibfk_1 key: agent,event_log_ibfk_1 key_len: 62,92 ref: NULL rows: 1757 Extra: Using intersect(agent,event_log_ibfk_1); Using where *** 3. row *** id: 1 select_type: SIMPLE table: E type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: idx_acnt.EL.enduser_acnt rows: 1 Extra: Using where 3 rows in set (0.00 sec) -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table/select problem...
I had this same issue a while back and solved it by writing my events to a disk-based file and periodically importing them into the event log MyISAM table. This way, even if your select statements lock the table, it won't affect the performance of your application. Of course, this may require some rewriting of your application code, depending on how events are logged. You could avoid the locking with InnoDB, but I did not choose that solution because MyISAM seems like a better fit for a logging situation, and they can later be used in Merge tables. I wonder if any others have used InnoDB for large logging tables and what the performance has been? Steve Musumeche CIO, Internet Retail Connection st...@internetretailconnection.com 1-800-248-1987 ext 802 On 2/4/2011 11:29 AM, Andy Wallace wrote: Greetings, all... I'm having an issue with a SELECT in our system. We have an event log table, with about 9 million rows in it. Inserts happen with some pretty high frequency, and these selects happen periodically. The event_log table is MyISAM, the rest of the tables are InnoDB. What's happening is that, periodically, when this select gets run, the whole damn thing locks up, and that pretty much shuts us down (since many things insert events into the table, and the table gets locked, so all the inserts hang). The statement and the explain for it are below. the enduser table has about a million rows in it, the event_type table 35 rows. The weird part is that, if I strip down the query to use no joins, the explain wants to return about 17,000 rows, but the query itself does the table locking thing. Should we perhaps change the event log to InnoDB to avoid table locking? Might the table itself be corrupt in some way? Any thoughts? thanks, andy EXPLAIN SELECT EL.event_log_id, EL.event_time, DATE_FORMAT(EL.event_time, '%c-%d-%Y %H:%i:%s') as 'time_formatted', ET.event_type_id, ET.description, EL.csr_name, EL.enduser_acnt, EL.csr_name, EL.referer, EL.mls_id, EL.mls_no, EL.ss_id, EL.details, E.fname, E.lname, E.email, E.phone1 FROM event_log EL JOIN event_type ET ON EL.event_type_id = ET.event_type_id JOIN enduser E ON EL.enduser_acnt = E.enduser_acnt WHERE EL.acnt = 'AR238156' AND EL.enduser_acnt != '' AND EL.event_type_id = 'EndUserLogin' AND event_time BETWEEN DATE_SUB(CURDATE(), INTERVAL '7' DAY) AND NOW() ORDER BY EL.event_time DESC *** 1. row *** id: 1 select_type: SIMPLE table: ET type: const possible_keys: PRIMARY key: PRIMARY key_len: 92 ref: const rows: 1 Extra: Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: EL type: index_merge possible_keys: agent,enduser,event_log_ibfk_1 key: agent,event_log_ibfk_1 key_len: 62,92 ref: NULL rows: 1757 Extra: Using intersect(agent,event_log_ibfk_1); Using where *** 3. row *** id: 1 select_type: SIMPLE table: E type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: idx_acnt.EL.enduser_acnt rows: 1 Extra: Using where 3 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table/select problem...
Do you delete data from the table ? MyISAM will only grant a write lock when there are no locks on the table - including implicit read locks. That may be your problem. There is a single situation when concurrent reads and writes are possible on MyISAM, however: when your table has no holes in the data. At that point, selects happen on the existing data, while the insert queue is handled (sequentially) at the same time. If that is indeed your problem, you may fix the table using OPTIMIZE TABLE. Two other options might be: - set the variable concurrent_insert to 2 - this will allow concurrent inserts at the end of the table even when there are holes. Downside is that freed space (from deletes) is not reused. - use INSERT DELAYED. Code returns immediately, but you have no way of knowing wether or not any given insert succeeded. If you delete data, but only relatively old data, you might also benefit from partitioning the table: I'm not sure about this, but it seems reasonable that concurrent updates would be possible on partitions with no holes. Should try this sometime. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: Table/select problem...
What columns do you have indexed on your event_log table? Can you post the output from SHOW CREATE TABLE? How long does the query run for? -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Friday, February 04, 2011 10:29 AM To: mysql list Subject: Table/select problem... Greetings, all... I'm having an issue with a SELECT in our system. We have an event log table, with about 9 million rows in it. Inserts happen with some pretty high frequency, and these selects happen periodically. The event_log table is MyISAM, the rest of the tables are InnoDB. What's happening is that, periodically, when this select gets run, the whole damn thing locks up, and that pretty much shuts us down (since many things insert events into the table, and the table gets locked, so all the inserts hang). The statement and the explain for it are below. the enduser table has about a million rows in it, the event_type table 35 rows. The weird part is that, if I strip down the query to use no joins, the explain wants to return about 17,000 rows, but the query itself does the table locking thing. Should we perhaps change the event log to InnoDB to avoid table locking? Might the table itself be corrupt in some way? Any thoughts? thanks, andy EXPLAIN SELECT EL.event_log_id, EL.event_time, DATE_FORMAT(EL.event_time, '%c-%d-%Y %H:%i:%s') as 'time_formatted', ET.event_type_id, ET.description, EL.csr_name, EL.enduser_acnt, EL.csr_name, EL.referer, EL.mls_id, EL.mls_no, EL.ss_id, EL.details, E.fname, E.lname, E.email, E.phone1 FROM event_log EL JOIN event_type ET ON EL.event_type_id = ET.event_type_id JOIN enduser E ON EL.enduser_acnt = E.enduser_acnt WHERE EL.acnt = 'AR238156' AND EL.enduser_acnt != '' AND EL.event_type_id = 'EndUserLogin' AND event_time BETWEEN DATE_SUB(CURDATE(), INTERVAL '7' DAY) AND NOW() ORDER BY EL.event_time DESC *** 1. row *** id: 1 select_type: SIMPLE table: ET type: const possible_keys: PRIMARY key: PRIMARY key_len: 92 ref: const rows: 1 Extra: Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: EL type: index_merge possible_keys: agent,enduser,event_log_ibfk_1 key: agent,event_log_ibfk_1 key_len: 62,92 ref: NULL rows: 1757 Extra: Using intersect(agent,event_log_ibfk_1); Using where *** 3. row *** id: 1 select_type: SIMPLE table: E type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: idx_acnt.EL.enduser_acnt rows: 1 Extra: Using where 3 rows in set (0.00 sec) -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: Select Problem
On Sun, Dec 6, 2009 at 2:42 PM, Steve Edberg edb...@edberg-online.comwrote: At 1:26 PM -0500 12/6/09, Victor Subervi wrote: Hi; I have the following: mysql select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = prodCat2; ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause' mysql describe categoriesProducts; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql select * from categoriesProducts; ++--++ | ID | Category | Parent | ++--++ | 1 | prodCat1 | None | | 2 | prodCat2 | None | ++--++ 2 rows in set (0.00 sec) So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how I specified that in my query. Please advise. TIA, Victor You didn't quote prodCat2 in the query, so it was assuming you were referring to the column name. Try: select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = 'prodCat2'; Thanks! V
Select Problem
Hi; I have the following: mysql select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = prodCat2; ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause' mysql describe categoriesProducts; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql select * from categoriesProducts; ++--++ | ID | Category | Parent | ++--++ | 1 | prodCat1 | None | | 2 | prodCat2 | None | ++--++ 2 rows in set (0.00 sec) So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how I specified that in my query. Please advise. TIA, Victor
Re: Select Problem
At 1:26 PM -0500 12/6/09, Victor Subervi wrote: Hi; I have the following: mysql select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = prodCat2; ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause' mysql describe categoriesProducts; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 3 rows in set (0.00 sec) mysql select * from categoriesProducts; ++--++ | ID | Category | Parent | ++--++ | 1 | prodCat1 | None | | 2 | prodCat2 | None | ++--++ 2 rows in set (0.00 sec) So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how I specified that in my query. Please advise. TIA, Victor You didn't quote prodCat2 in the query, so it was assuming you were referring to the column name. Try: select * from categoriesProducts as c inner join relationshipProducts as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID where p.Category = 'prodCat2'; - s -- ++ | Steve Edberg edb...@edberg-online.com | | Programming/Database/SysAdminhttp://www.edberg-online.com/ | ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select problem
I am having a problem with SELECT. The table has 3 rows. I am using the C api. Here is my C code. count = mysql_query(my_db, SELECT * FROM accounts); er = mysql_error(my_db); res = mysql_use_result(my_db); num_row = mysql_num_rows(res); count is returned as 0 (no error) er is NULL (no error) res is erturned valid and the structure is filled in num_row is returned as 0 I have full permissions to the data base and its tables. I can retrieve data from other tables in the database. Does any one have an idea as to what is amiss here. I can add to the table and get the correct error when I try to add or update the table with duplicate key fields. -- Cheers Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange select problem
Dear all, I've encountered a very strange problem. I dumpped one table from a database and then import it into another database. I works successfully, all the data is exactly the same. But when I the below SQL on two tables: select * from foo_table ORDER BY id DESC it shows up different result. All result truely sorted by id by some of them appears different. Can someone help me on this problem? Any suggestion is extremely welcomed. Thanks in advanced. -- Cheng-Lin Yang Sun Certified Java Programmer High Speed Network Group Lab (HSNG) Institute of Computer Science Info. Engineering, National Chung Cheng Univerisity, Taiwan E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert Select problem
I get this error: Fehler in /home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php in Zeile 36 mit Error:br Query:INSERT INTO objektflyer_verknuepfung (av_o_id_haupt,av_o_id_link,av_text,av_op_id) SELECT 418,av_o_id_link,av_text,av_op_id FROM objektflyer_verknuepfung WHERE av_id IN (1) brNot unique table/alias: 'objektflyer_verknuepfung' I use a very old Mysql. 3.2 or similiar. Anyone knows that error and can give a helping hand? Thanks, Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Select problem
Hi Barry, see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html you cannot insert into a table you're doing select on (same goes for update). Regards, /Johan Barry skrev: I get this error: Fehler in /home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php in Zeile 36 mit Error:br Query:INSERT INTO objektflyer_verknuepfung (av_o_id_haupt,av_o_id_link,av_text,av_op_id) SELECT 418,av_o_id_link,av_text,av_op_id FROM objektflyer_verknuepfung WHERE av_id IN (1) brNot unique table/alias: 'objektflyer_verknuepfung' I use a very old Mysql. 3.2 or similiar. Anyone knows that error and can give a helping hand? Thanks, Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Select problem
Johan Höök schrieb: Hi Barry, see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html you cannot insert into a table you're doing select on (same goes for update). But i am doing it on a test server version 5.x and it works like a charm :) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT SELECT Problem
Hi, The following query worked fine: INSERT INTO Allocations(Project_ID, User_ID) SELECT P.Project_ID, U.User_ID FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) However I want to add a column to the INSERT part of the query and the value to be inserted must always be 'Yes'. Is there a way to do this with just with one query? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT SELECT Problem
Hi, Try setting the default value of the column to 'Yes' that should work or INSERT INTO Allocations(Project_ID, User_ID, field) SELECT P.Project_ID, U.User_ID, 'Yes' FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) Met vriendelijke groet, Almar van Pel -Oorspronkelijk bericht- Van: Shaun [mailto:[EMAIL PROTECTED] Verzonden: woensdag 23 november 2005 15:22 Aan: mysql@lists.mysql.com Onderwerp: INSERT SELECT Problem Hi, The following query worked fine: INSERT INTO Allocations(Project_ID, User_ID) SELECT P.Project_ID, U.User_ID FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) However I want to add a column to the INSERT part of the query and the value to be inserted must always be 'Yes'. Is there a way to do this with just with one query? Thanks for your help. -- 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: INSERT SELECT Problem
Hi Shaun, You could use the following statement: INSERT INTO Allocations(Project_ID, User_ID, YES_COLUMN) SELECT P.Project_ID, U.User_ID, 'Yes' FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) Please replace YES_COLUMN with the column name that stores the 'Yes' values. Best regards, Diego Wald - Original Message - From: Shaun [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 23, 2005 11:21 AM Subject: INSERT SELECT Problem Hi, The following query worked fine: INSERT INTO Allocations(Project_ID, User_ID) SELECT P.Project_ID, U.User_ID FROM Users U, Projects P, Clients C WHERE P.Client_ID = C.Client_ID AND U.Client_ID = C.Client_ID AND Project_ID =.$project_id) However I want to add a column to the INSERT part of the query and the value to be inserted must always be 'Yes'. Is there a way to do this with just with one query? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Información de NOD32 1.1298 (20051123) __ Este mensaje ha sido analizado con NOD32 antivirus system http://www.nod32.com ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MAX select problem
Hello, I'm trying to get the date and amount of the most visits to my site over a given time period using : SELECT max(visits) as maximum FROM visit WHERE (((visit_date = '$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id)) This gives me the right figure, but when I try to pull out the date that this occured on with, SELECT max(visits) as maximum, visit_date FROM visit WHERE (((visit_date = '$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id)) GROUP BY visit_date I get a completely different and wrong answer, any ideas? Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX select problem
Lee Denny wrote: Hello, I'm trying to get the date and amount of the most visits to my site over a given time period using : SELECT max(visits) as maximum FROM visit WHERE (((visit_date = '$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id)) This gives me the right figure, but when I try to pull out the date that this occured on with, SELECT * FROM visit WHERE (((visit_date = '$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id)) ORDER BY visits DESC LIMIT 1; Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX select problem
Lee Denny wrote: Hello, I'm trying to get the date and amount of the most visits to my site over a given time period using : SELECT max(visits) as maximum FROM visit WHERE (((visit_date = '$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id)) This gives me the right figure, but when I try to pull out the date that this occured on with, SELECT max(visits) as maximum, visit_date FROM visit WHERE (((visit_date = '$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id)) GROUP BY visit_date I get a completely different and wrong answer, any ideas? Cheers, Lee I think that : http://dev.mysql.com/doc/mysql/en/example-maximum-row.html would answer you question more than I ;) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT problem and QUESTION OF SPEED
Reni Fournier wrote: Thanks for the solution. It looks like it would work, but I don't have MySQL 4.1 (which I believe is required for this to work, since this is SUBSELECT, isn't it?). Assuming I have to use two selects, which would you say is faster, creating a temporary table in MySQL, or extracting the data back into PHP, and recreating a SELECT from there? by using in memory temporary table, you'll avoid the round trip between, the PHP server and the SQL server, which would be a bit faster. ..Rene On 2-Jun-05, at 2:28 PM, [EMAIL PROTECTED] wrote: Hi Reni, thsi can be a solution, many others are possible : mysql select distinct the_date, person_id, cost, name - from trips,persons - where person_id=persons.id - and the_date in(select max(the_date) from trips a - where a.person_id=person_id - group by person_id) - ; ++---+--+--+ | the_date | person_id | cost | name | ++---+--+--+ | 2005-02-08 | 1 | 580 | john | | 2005-01-25 | 2 | 200 | jane | | 2005-02-03 | 3 | 600 | mike | | 2005-02-20 | 4 | 320 | mary | ++---+--+--+ 4 rows in set (0.00 sec) Mathias Selon Reni Fournier [EMAIL PROTECTED]: I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS iddateperson_idcost --- 12005-01-012500 22005-01-051400 32005-01-124350 42005-01-153175 52005-01-172385 62005-01-252200 72005-02-033600 82005-02-081580 92005-02-204320 PERSONS idname - 1john 2jane 3mike 4mary 5henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-204320mary 2005-02-081580john 2005-02-033600mike 2005-01-252200jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene -- 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] -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT problem
I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS id dateperson_id cost --- 1 2005-01-01 2 500 2 2005-01-05 1 400 3 2005-01-12 4 350 4 2005-01-15 3 175 5 2005-01-17 2 385 6 2005-01-25 2 200 7 2005-02-03 3 600 8 2005-02-08 1 580 9 2005-02-20 4 320 PERSONS id name - 1 john 2 jane 3 mike 4 mary 5 henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-20 4 320 mary 2005-02-08 1 580 john 2005-02-03 3 600 mike 2005-01-25 2 200 jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT problem
René Fournier [EMAIL PROTECTED] wrote on 06/02/2005 02:53:51 PM: I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS id date person_id cost --- 1 2005-01-01 2 500 2 2005-01-05 1 400 3 2005-01-12 4 350 4 2005-01-15 3 175 5 2005-01-17 2 385 6 2005-01-25 2 200 7 2005-02-03 3 600 8 2005-02-08 1 580 9 2005-02-20 4 320 PERSONS id name - 1 john 2 jane 3 mike 4 mary 5 henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-20 4 320 mary 2005-02-08 1 580 john 2005-02-03 3 600 mike 2005-01-25 2 200 jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene This is a VERY FAQ. It is a variant on the Groupwize maximum problem well documented here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Basically you need to determine the max(trips.date) for each person_id then use that list (in combination with the person table) to create the report you wanted in the first place. The article shows 3 ways to make it happen: save your list into a temporar table, generate your list as the result of a subquery, or use the max-concat hack. If you prefer, the same article is also available in French, German, Japanese, Portuguese, and Russian. Just click on the appropriate link to the side. If you have tried this and still can't make it work, please come back with your query and I am sure someone will be very happy to help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: SELECT problem
Hi René, thsi can be a solution, many others are possible : mysql select distinct the_date, person_id, cost, name - from trips,persons - where person_id=persons.id - and the_date in(select max(the_date) from trips a - where a.person_id=person_id - group by person_id) - ; ++---+--+--+ | the_date | person_id | cost | name | ++---+--+--+ | 2005-02-08 | 1 | 580 | john | | 2005-01-25 | 2 | 200 | jane | | 2005-02-03 | 3 | 600 | mike | | 2005-02-20 | 4 | 320 | mary | ++---+--+--+ 4 rows in set (0.00 sec) Mathias Selon René Fournier [EMAIL PROTECTED]: I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS iddateperson_id cost --- 1 2005-01-01 2 500 2 2005-01-05 1 400 3 2005-01-12 4 350 4 2005-01-15 3 175 5 2005-01-17 2 385 6 2005-01-25 2 200 7 2005-02-03 3 600 8 2005-02-08 1 580 9 2005-02-20 4 320 PERSONS idname - 1 john 2 jane 3 mike 4 mary 5 henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-20 4 320 mary 2005-02-08 1 580 john 2005-02-03 3 600 mike 2005-01-25 2 200 jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene -- 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: SELECT problem and QUESTION OF SPEED
Thanks for the solution. It looks like it would work, but I don't have MySQL 4.1 (which I believe is required for this to work, since this is SUBSELECT, isn't it?). Assuming I have to use two selects, which would you say is faster, creating a temporary table in MySQL, or extracting the data back into PHP, and recreating a SELECT from there? ...Rene On 2-Jun-05, at 2:28 PM, [EMAIL PROTECTED] wrote: Hi René, thsi can be a solution, many others are possible : mysql select distinct the_date, person_id, cost, name - from trips,persons - where person_id=persons.id - and the_date in(select max(the_date) from trips a - where a.person_id=person_id - group by person_id) - ; ++---+--+--+ | the_date | person_id | cost | name | ++---+--+--+ | 2005-02-08 | 1 | 580 | john | | 2005-01-25 | 2 | 200 | jane | | 2005-02-03 | 3 | 600 | mike | | 2005-02-20 | 4 | 320 | mary | ++---+--+--+ 4 rows in set (0.00 sec) Mathias Selon René Fournier [EMAIL PROTECTED]: I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS id dateperson_id cost --- 1 2005-01-01 2 500 2 2005-01-05 1 400 3 2005-01-12 4 350 4 2005-01-15 3 175 5 2005-01-17 2 385 6 2005-01-25 2 200 7 2005-02-03 3 600 8 2005-02-08 1 580 9 2005-02-20 4 320 PERSONS id name - 1 john 2 jane 3 mike 4 mary 5 henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-20 4 320 mary 2005-02-08 1 580 john 2005-02-03 3 600 mike 2005-01-25 2 200 jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene -- 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]
Select problem for column with Binary attribute
Hi, I am using MySQL 4.0.20. For a table of INNODB type, same query return different results when different query plan is used. select * from project_team where project_id = 'FMS '; -- 2 rows. (primary key used) select * from project_team ignore index (PRIMARY) where project_id = 'FMS '; -- 0 rows. (sequential table scan used) project_id is of type char(5) binary and is the leading part of the primary key. Since a trailing space is included in the where clause, first result should be incorrect according to MySQL documentation (which mentioned that trailing space is NOT ignored for binary comparison). Is this a bug? Also, is there a simple way to achieve : case sensitive search with trailing space ignored. (I am trying to migrate an application from SYBASE to MYSQL where the behaviour for string comparison is case sensitive and trailing space ignored). Regards, Michael [EMAIL PROTECTED] http://mobile.yahoo.com.hk/
Select Problem
Has anyone seen a post on this issue? If not, can anyone offer any advice? I have a TBL of users and I have created a search screen where you can type in first or last name and it will retrieve the appropriate records. Here is the statement: Select * from STUDENTS WHERE FName LIKE '% .$_REQUEST['searchit']. %' OR LName LIKE '% .$_REQUEST['searchit']. %' OR idStudent LIKE '% .$_REQUEST['searchit']. %' The statement works great for the most part. However, it is a bit sporadic. For example, I type in my name (because I know I am in the DB and it will NOT pull back any results. I even ran this command from the UNIX box directly and it will not work. So I have another page which pulls ALL records from another TBL and joins the USER TBL and I AM LISTED!! Here is the statement for that page: Select * FROM CAMPREG INNER JOIN STUDENTS on STUDENTS.idStudent = CAMPREG.idStudent ORDER BY LName; Again, the first one will NOT retrieve my name..the second one will list me in the master list. Help :-)
Re Select problem
Are you spitting out an output of the query string to verify that the data from the form is making it to the query correctly? GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert .. select problem.
Hello , I'm working on a project with MySql 4.0.12-log. I have a problem with insert .. select: To describe the program of some touristic tours I create two tables: TOUR that contains the data TAPPE that contains the program of the tour. (relation 1:n). To keep track of each tour i create two tables (TOUR_A and TAPPE_A) very closed to the previous two but in tese tables there are also real dates. Now I generate, with PHP, for each tour a set of records in table TOUR_A (1 record a week for a year) and copy the records from TAPPE to TAPPE_A adding dates and the ID of the record of TOUR_A. Test case: 1 record in TOUR 2 records in TAPPE. Generated 141 records in TOUR_A. If I use insert into TAPPE_A select . from TAPPE where TA_ID_TOUR=xx it takes about 25 seconds for 141+141x2 records. If I use select from TAPPE and than INSERT into TAPPE_A in php with a loop it takes less than a second. Does someone have had this problem? It is a bug? The tables have indexes on search fields. Santino Cusimano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select problem with BigInt
I am new to mysql and am converting an existing program. I have encountered what appears to be a problem with bigints I have a large integer number (milliseconds since 1970) which is 13 digits. So I tried to store it in a table as a bigint type. Storing works fine. When I try to retrieve it, I don't get the records I expect. If I try to order my records by the bigint column I get completely unexpected orderings. My table definition is: CREATE TABLE `FREEWAYDATA` ( `CLIENTCLOCK` bigint default NULL, `CLOCKOFFSET` decimal(10,0) default NULL, `CORRECTEDCLOCK` bigint NOT NULL default '0', `DETECTORDATA` varchar(255) default NULL, `STATIONID` decimal(10,0) NOT NULL default '0', `THEDAY` date default NULL, PRIMARY KEY (`CORRECTEDCLOCK`,`STATIONID`) ) TYPE=MyISAM; The problem field is correctedclock. I insert records with a JDBC preparedStatement: insert into FREEWAYSERVER.FREEWAYDATA (STATIONID, CORRECTEDCLOCK, CLIENTCLOCK, DETECTORDATA, clockOffset) values (?, ?, ?, ?, ?); where the correctedclock is set using setLong(); Inserts appear to work fine. I retrieve records using selects of the form: SELECT CORRECTEDCLOCK FROM FREEWAYSERVER.FREEWAYDATA where correctedclock = ? and correctedclock ? order by correctedClock ASC where the start and end times are large integer values. What I get back varies by which values I use but is generally wrong. If I do select correctedclock from freewaydata I can see the values I want but they don't get pulled if I use the where clause. If I do select correctedclock from freewaydata order by correctedclock asc I get some order which is not numeric nor string ordering nor the insert order. If I change correctedclock to be an integer field, everything works as expected. While this is a possible workaround it messes up my data accesses. Is there something special about bigint that prevents range based selects from working they way I expect them to? This is using mysql 4.0.16 on Mac OS X 10.3 server. Randall Cayford Institute of Transportation Studies UC Berkeley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL SELECT problem
Hi, I have a problem about writing a proper SELECT query for the following goal: (I only have basic knowledge of SQL) Table name: peoplelist column 1: id (not NULL, auto_incremental) column 2: name column 3: country now, there are about 7,000 rows in this table. I want to select out: first (in terms of id) 10 or less people of each country. There are more than 100 countries. One solution is to run one query for each country, then combine the results. But how can I do this in a better way, e.g. by one SELECT sql query ? ps. I am using MySQL 4.0 that doesn't support subselect. Thanks. Han = Email: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Search - Find what youre looking for faster http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL SELECT problem
Try something like this: SELECT ID, Name, Country FROM peoplelist GROUP BY Country HAVING count(Country)10; That might work. Also you can have subselects in 4.0.
Re: MySQL SELECT problem
sub selects are only in 4.1 I thought? On Mar 3, 2004, at 5:08 PM, Jacque Scott wrote: Also you can have subselects in 4.0. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL SELECT problem
You are correct. Sorry about that. charles kline [EMAIL PROTECTED] 3/3/2004 2:46:51 PM sub selects are only in 4.1 I thought? On Mar 3, 2004, at 5:08 PM, Jacque Scott wrote: Also you can have subselects in 4.0.
Re: SELECT problem
Hi Andy What you can do is make a copy of your genre_titles table through aliasing, this will allow you to only return the results that have both genres and should reduce the need for programmatical sorting - aliasing also makes for less typing :). SELECT a.name, b.titleid FROM titles a, genre_titles b, genre_titles c WHERE b.genid = 4 AND (b.titleid = c.titleid AND c.genid = 5) AND a.titleid = b.titleid If the number of genres that have to be matched vary, you can always generate your code through a script that loops through and builds the additional parts of the predicate. HTH Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Andrew Barnes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 2:46 AM Subject: SELECT problem Hi, I have three tables, a title's table, a genre's table and a genre_titles map table (to model the many to many relationship between genre's and title's). I need to write a query that will return title's that match two or more genre's. An example would be one title could be a comedy/drama and I would need to find other title's that have a reference to the genre's comedy and drama. I have tried this query - select titles.name, genre_titles.titleid from titles, genre_titles where genre_titles.genid = 4 and genre_titles.titleid = titles.titleid or genre_titles.genid = 5 and genre_titles.titleid = titles.titleid with programmatic sorting but the result sets are too large and the sorting algorithm is too slow. I was wondering whether there was a query that would return the exact result set needed. I am using mysql 4.0.13 Regards AndyB -- 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]
Select problem
Hi all, I have a table with the following data: ++---+--+ | lpcval | smiles_id | crhash | ++---+--+ | 0.81 | 996 | 0597b6f84e0feaf9596869284e6e0660 | | 0.86 | 996 | 53a88ef9f72a77eeb47da15969e6fbc0 | | 0.71 | 996 | 251f58e8485335b094f06352e65bb6a8 | |0.8 | 996 | dd59144f1df0c54f299a2f9a5587042a | | 0.86 | 997 | bad8fe7edb74c3ed4495a4825750d34d | | 0.88 | 997 | aefd10198ff8db947c78e0aa0e1e349d | | 0.76 | 997 | ade6c8f5d4911091eed515ad75db070a | | 0.73 | 998 | e2b0623ad9b77b95d76c00fb76614c0e | |0.7 | 998 | 2672157a6ec823d2170cfe4b38123079 | | 0.78 | 998 | 92746af8be0431c2fd0dda646a1827cf | | 0.77 | 998 | ac087a6b796057e29941a2d1358c3eb1 | | 0.79 | 998 | a631bb5f3d3ecacd9c206f6d9f0c8bee | ++---+--+ What I want to be able to do is for each value of smiles_id to be able to select the row with the largest value of lpcval. This should leave me with a table like ++---+--+ | lpcval | smiles_id | crhash | ++---+--+ | 0.86 | 996 | 53a88ef9f72a77eeb47da15969e6fbc0 | | 0.88 | 997 | aefd10198ff8db947c78e0aa0e1e349d | | 0.79 | 998 | a631bb5f3d3ecacd9c206f6d9f0c8bee | ++---+--+ My closest attempt is select max(lpcval) as lpcval, smiles_id, crhash from results group by smiles_id; but that gives me the wrong crhash. I guess my understanding of max is less than it should be :) Any pointers on were to look next would be greatly appreciated. Dermot -- whathaveibecome? mysweetestfriend everyoneiknow goesawayintheend youcouldhaveitall myempireofdirt iwillletyoudown iwillmakeyouhurt ificouldstartagain amillionmilesaway iwouldkeepmyself iwouldfindaway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select problem
Dermot Frost wrote: Hi all, I have a table with the following data: ++---+--+ | lpcval | smiles_id | crhash | ++---+--+ | 0.81 | 996 | 0597b6f84e0feaf9596869284e6e0660 | | 0.86 | 996 | 53a88ef9f72a77eeb47da15969e6fbc0 | | 0.71 | 996 | 251f58e8485335b094f06352e65bb6a8 | |0.8 | 996 | dd59144f1df0c54f299a2f9a5587042a | | 0.86 | 997 | bad8fe7edb74c3ed4495a4825750d34d | | 0.88 | 997 | aefd10198ff8db947c78e0aa0e1e349d | | 0.76 | 997 | ade6c8f5d4911091eed515ad75db070a | | 0.73 | 998 | e2b0623ad9b77b95d76c00fb76614c0e | |0.7 | 998 | 2672157a6ec823d2170cfe4b38123079 | | 0.78 | 998 | 92746af8be0431c2fd0dda646a1827cf | | 0.77 | 998 | ac087a6b796057e29941a2d1358c3eb1 | | 0.79 | 998 | a631bb5f3d3ecacd9c206f6d9f0c8bee | ++---+--+ What I want to be able to do is for each value of smiles_id to be able to select the row with the largest value of lpcval. This should leave me with a table like ++---+--+ | lpcval | smiles_id | crhash | ++---+--+ | 0.86 | 996 | 53a88ef9f72a77eeb47da15969e6fbc0 | | 0.88 | 997 | aefd10198ff8db947c78e0aa0e1e349d | | 0.79 | 998 | a631bb5f3d3ecacd9c206f6d9f0c8bee | ++---+--+ My closest attempt is select max(lpcval) as lpcval, smiles_id, crhash from results group by smiles_id; but that gives me the wrong crhash. I guess my understanding of max is less than it should be :) Any pointers on were to look next would be greatly appreciated. Dermot How about if you try this two-step solution: create temporary table my_Smiles select lpcval, smiles_id, crhash from results order by smiles_id asc, lpcval desc; select * from my_Smiles Group by smiles_id; The first SQL statement creates a temporary table ordering by smiles_id, then by lpcval. The second uses Group By to pick the first. The reason is that the specification says that in a single statement GROUP BY must precede the ORDER BY. To beat that we have to use 2 statements. -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail)
RE: SELECT problem
Hi, Rachel, It seems most people have missed this message. Since you didn't give enough information in your question, in order to answer your question, I need to make up some assumptions, which might or might not be correct :-( Suppose the same favsub could appear in either or both tables year9 and year10. (From an overall DB design point of view, it would be better off not to create distinct tables for distinct years, but have one single table with an additional year column -- with this alternative design, there would be no need to create a new table each year and no need for this question.) Without being able to build your tables locally to test it (thus have no 100% certainty -- apology), I suggest the following sequence of 4 SQL statements that use a temporary table, with the same structure, to first merge year9 and year10 data to make up for your separate-year table design: Create temporary table X (favsub int, sex char(1), . ) ; Insert into X ((select favsub, sex, . from year9 ) union all (select favsub, sex, . from year10 ) ) ; Select a.favsub, count(m.sex)/count(a.*)*100, count(f.sex)/count(a.*)*100 from X a, X m, X f where a.favsub = m.favsub and a.favsub = f.favsub and m.sex = 'm' and f.sex = 'f' order by a.favsub ; -- you might need to play around to format the query result to meet your needs. After executing the query, you can then issue the 4th SQL to drop temporary table X. Hope this helps. Best regards, Lin -Original Message- From: Rachel Cunliffe [mailto:[EMAIL PROTECTED] Sent: Sunday, July 27, 2003 9:00 PM To: [EMAIL PROTECTED] Subject: SELECT problem Hi, I'm new at complex SELECT statements, so any help would be appreciated. I need to create a summary table of counts from two tables in the database: year9 has a stack of variables including sex and favsub (favourite subject) year10 also has a stack of variables including sex and favsub I'd like to output a table with the following (column %'s if possible, otherwise counts): sex favsub malefemale 1 10% 5% 2 ... ... At the moment, I have this as my MYSQL query: SELECT favsub, sex, COUNT(favsub) FROM year9 GROUP BY favsub,sex ORDER BY favsub,sex * Problems: this is only for one of the tables, and also it's quite messy formatting it to a nice HTML layout as there are possibly two rows for each favourite subject, they aren't on the same row. It's also outputting the counts, not percentages so I need to do another query to figure out the total number of males/females. Again, any help appreciated. Kind regards Rachel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT problem
Hi, I'm new at complex SELECT statements, so any help would be appreciated. I need to create a summary table of counts from two tables in the database: year9 has a stack of variables including sex and favsub (favourite subject) year10 also has a stack of variables including sex and favsub I'd like to output a table with the following (column %'s if possible, otherwise counts): sex favsub malefemale 1 10% 5% 2 ... ... At the moment, I have this as my MYSQL query: SELECT favsub, sex, COUNT(favsub) FROM year9 GROUP BY favsub,sex ORDER BY favsub,sex * Problems: this is only for one of the tables, and also it's quite messy formatting it to a nice HTML layout as there are possibly two rows for each favourite subject, they aren't on the same row. It's also outputting the counts, not percentages so I need to do another query to figure out the total number of males/females. Again, any help appreciated. Kind regards Rachel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie SELECT problem
Hello everyone, I have the following select statement SELECT DISTINCT sessionID, userID, date, time FROM sti_tracking WHERE userID = 999 What I want is to have only records with the userID of 99 and where the sessionID is distinct (meaning only on of each session id). Neither sessionID nor userID are keys or unique. Obviously this isn't working. Can someone suggest how this should be done? Tim Winters Creative Development Manager Sampling Technologies Incorporated 1600 Bedford Highway, Suite 212 Bedford, Nova Scotia B4A 1E8 www.samplingtechnologies.com [EMAIL PROTECTED] [EMAIL PROTECTED] Office: 902 450 5500 Cell: 902 430 8498 Fax:: 902 484 7115
RE: Newbie SELECT problem
Well, it is important to remember that SELECT DISTINCT simply restricts that the WHOLE ROW is distinct, therefore it takes into account all columns, not just the sessionID column, when deciding if a row is distinct. One way to do this would be to do SELECT sessionID, userID, date, time FROM sti_tracking WHERE sessionID IN (SELECT DISTINCT sessionID FROM sti_tracking WHERE userID = 99); Assuming you have MySQL 4.1 that is (which supports subselects). Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Tim Winters [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 10:56 AM To: [EMAIL PROTECTED] Subject: Newbie SELECT problem Hello everyone, I have the following select statement SELECT DISTINCT sessionID, userID, date, time FROM sti_tracking WHERE userID = 999 What I want is to have only records with the userID of 99 and where the sessionID is distinct (meaning only on of each session id). Neither sessionID nor userID are keys or unique. Obviously this isn't working. Can someone suggest how this should be done? Tim Winters Creative Development Manager Sampling Technologies Incorporated 1600 Bedford Highway, Suite 212 Bedford, Nova Scotia B4A 1E8 www.samplingtechnologies.com [EMAIL PROTECTED] [EMAIL PROTECTED] Office: 902 450 5500 Cell: 902 430 8498 Fax:: 902 484 7115 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT problem
Tim Winters wrote: Hello everyone, I have the following select statement SELECT DISTINCT sessionID, userID, date, time FROM sti_tracking WHERE userID = 999 What I want is to have only records with the userID of 99 and where the sessionID is distinct (meaning only on of each session id). Neither sessionID nor userID are keys or unique. Obviously this isn't working. Can someone suggest how this should be done? If I understand you properly, you want only a single line for each of userID 999's sessions, is that right? Is there some specific date and time that you are interested in for that session, for instance, the first? If so, try: SELECT sessionID, userID, min(date), min(time) FROM sti_tracking WHERE userID = 999 GROUP BY userI, sessionID Even if I misunderstood, you can probably adapt this into what you really want. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT problem
Hello everyone, I have the following select statement SELECT DISTINCT sessionID, userID, date, time FROM sti_tracking WHERE userID = 999 What I want is to have only records with the userID of 99 and where the sessionID is distinct (meaning only on of each session id). Neither sessionID nor userID are keys or unique. Obviously this isn't working. Can someone suggest how this should be done? Tim Winters Creative Development Manager Sampling Technologies Incorporated Had a similar experience, and I've been doing it long enough to know better. 'DISTINCT' would work only if date and time returned the same values. Are '999' and '99' supposed to be the same? Let me see if I can rephrase what you are looking for: a. For user '999' give me the information where there is only one record with a given SessionID? b. For user '999' for each sessionID give me the unique Date and Time values. c. something else entirely. Also, are you running this in a procedureal language (e.g., perl, java)? This will give us other options. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie SELECT problem
Hello, Very sorry to everyone about the confusing message. I should have read it over again before pressing send. First of all I'm looking for userID 999. A typo in the message not in the code. The table is set up like this. Table name sti_tracking hitID (primary key) (autonumber) userID sessionID date time pageName What it's for is a simple page tracing counter for a FLash site. Each time a section is accessed a new row is written in the table. userID identifies the user. So if the user comes to the site today and comes back again tomorrow the userID will be maintained. sessionID identifies 1 visit to the site. During 1 visit a user may view many sections within the site but as long as he doesn't close the browser the session number remains the same. Date and time will always be different (as will the hitID obviously). So what I want to be able to do is single out a user (999) and retrieve all the sessions he was involved in. But I don't want duplicate session numbers (one is enough). Make any more sense? Tim Winters Creative Development Manager Sampling Technologies Incorporated 1600 Bedford Highway, Suite 212 Bedford, Nova Scotia B4A 1E8 www.samplingtechnologies.com [EMAIL PROTECTED] [EMAIL PROTECTED] Office: 902 450 5500 Cell: 902 430 8498 Fax:: 902 484 7115 -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: July 2, 2003 2:13 PM To: [EMAIL PROTECTED] Subject: Re: Newbie SELECT problem Hello everyone, I have the following select statement SELECT DISTINCT sessionID, userID, date, time FROM sti_tracking WHERE userID = 999 What I want is to have only records with the userID of 99 and where the sessionID is distinct (meaning only on of each session id). Neither sessionID nor userID are keys or unique. Obviously this isn't working. Can someone suggest how this should be done? Tim Winters Creative Development Manager Sampling Technologies Incorporated Had a similar experience, and I've been doing it long enough to know better. 'DISTINCT' would work only if date and time returned the same values. Are '999' and '99' supposed to be the same? Let me see if I can rephrase what you are looking for: a. For user '999' give me the information where there is only one record with a given SessionID? b. For user '999' for each sessionID give me the unique Date and Time values. c. something else entirely. Also, are you running this in a procedureal language (e.g., perl, java)? This will give us other options. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- 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: Newbie SELECT problem
Tim Winters wrote: So what I want to be able to do is single out a user (999) and retrieve all the sessions he was involved in. But I don't want duplicate session numbers (one is enough). Make any more sense? Yes. The solution I posted earlier should work. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT problem
select sessionID,userID,date,time from sti_tracking where userID=99 group by sessionID; hope that works ,though i didn't clearly got your question,may be some expert suggest better regards harsh On Wed, 2 Jul 2003, Tim Winters wrote: Hello everyone, I have the following select statement SELECT DISTINCT sessionID, userID, date, time FROM sti_tracking WHERE userID = 999 What I want is to have only records with the userID of 99 and where the sessionID is distinct (meaning only on of each session id). Neither sessionID nor userID are keys or unique. Obviously this isn't working. Can someone suggest how this should be done? Tim Winters Creative Development Manager Sampling Technologies Incorporated 1600 Bedford Highway, Suite 212 Bedford, Nova Scotia B4A 1E8 www.samplingtechnologies.com [EMAIL PROTECTED] [EMAIL PROTECTED] Office: 902 450 5500 Cell: 902 430 8498 Fax:: 902 484 7115 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie SELECT problem
Table name sti_tracking hitID (primary key) (autonumber) userID sessionID date time pageName this might work select userID,sessionID from sti_tracking where userID=999 group by sessionID; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT problem
Tim:
Assuming that in your ealier posting the 99 was supposed to be 999, then
the solution given by Mike Hillyer is excellent and should work.
However, when I read your new posting, I seem to get confused. The
scenario sounds totally different - excuse me - from the earlier one and
would therefore need a different solution. You might help us by giving
sample data.
Or is this what you mean by But I don't want duplicate session numbers
(one is enough)? == In a single session (sessionID) user 999 (userID
999) may visit 3 pages. This results in three inserts being made into
table sti_tracking all having same sessionID and userID. Correct? When
retrieving you do not want to retrieve all these three records. Correct?
You just want one of the records. Which one? The first, second or third
because they each probably have a different time and pageName (even
date!!). If you did not want the date, time and pageName then the
solution is simple SELECT DISTINCT userID, sessionID FROM sti_tracking
WHERE userID = 999.
If you do not care which of the entries (3 in my example) is returned
and you still want the date, time and pageName (my guess is the first
will be returned), then you need to generate all the distinct userID and
sessionID pairs using the above SQL. Then for each pair (use a loop) run
SELECT userID, sessionID, date, time, pageName FROM sti_tracking WHERE
userID = {provide from loop} AND sessionID = {provide from loop} LIMIT 1.
Peter Aganyo
Tim Winters wrote:
Hello,
Very sorry to everyone about the confusing message. I should have read
it over again before pressing send.
First of all I'm looking for userID 999. A typo in the message not in
the code.
The table is set up like this.
Table name sti_tracking
hitID (primary key) (autonumber)
userID
sessionID
date
time
pageName
What it's for is a simple page tracing counter for a FLash site. Each
time a section is accessed a new row is written in the table.
userID identifies the user. So if the user comes to the site today and
comes back again tomorrow the userID will be maintained.
sessionID identifies 1 visit to the site. During 1 visit a user may
view many sections within the site but as long as he doesn't close the
browser the session number remains the same. Date and time will always
be different (as will the hitID obviously).
So what I want to be able to do is single out a user (999) and retrieve
all the sessions he was involved in. But I don't want duplicate session
numbers (one is enough).
Make any more sense?
Tim Winters
Creative Development Manager
Sampling Technologies Incorporated
1600 Bedford Highway, Suite 212
Bedford, Nova Scotia
B4A 1E8
www.samplingtechnologies.com
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Office: 902 450 5500
Cell: 902 430 8498
Fax:: 902 484 7115
-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: July 2, 2003 2:13 PM
To: [EMAIL PROTECTED]
Subject: Re: Newbie SELECT problem
Hello everyone,
I have the following select statement
SELECT DISTINCT sessionID, userID, date, time
FROM sti_tracking
WHERE userID = 999
What I want is to have only records with the userID of 99 and where
the
sessionID is distinct (meaning only on of each session id). Neither
sessionID nor userID are keys or unique.
Obviously this isn't working.
Can someone suggest how this should be done?
Tim Winters
Creative Development Manager
Sampling Technologies Incorporated
Had a similar experience, and I've been doing it long enough to know
better. 'DISTINCT' would work only if date and time returned the
same
values.
Are '999' and '99' supposed to be the same?
Let me see if I can rephrase what you are looking for:
a. For user '999' give me the information where there is only one
record
with a given SessionID?
b. For user '999' for each sessionID give me the unique Date and Time
values.
c. something else entirely.
Also, are you running this in a procedureal language (e.g., perl, java)?
This will give us other options.
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
Your favorite stores, helpful shopping tools and great gift ideas.
Experience the convenience of buying online with [EMAIL PROTECTED]
http://shopnow.netscape.com/
Re: Newbie SELECT problem
Tim:
Assuming that in your ealier posting the 99 was supposed to be 999, then
the solution given by Mike Hillyer is excellent and should work.
However, when I read your new posting, I seem to get confused. The
scenario sounds totally different - excuse me - from the earlier one and
would therefore need a different solution. You might help us by giving
sample data.
Or is this what you mean by But I don't want duplicate session numbers
(one is enough)? == In a single session (sessionID) user 999 (userID
999) may visit 3 pages. This results in three inserts being made into
table sti_tracking all having same sessionID and userID. Correct? When
retrieving you do not want to retrieve all these three records. Correct?
You just want one of the records. Which one? The first, second or third
because they each probably have a different time and pageName (even
date!!). If you did not want the date, time and pageName then the
solution is simple SELECT DISTINCT userID, sessionID FROM sti_tracking
WHERE userID = 999.
If you do not care which of the entries (3 in my example) is returned
and you still want the date, time and pageName (my guess is the first
will be returned), then you need to generate all the distinct userID and
sessionID pairs using the above SQL. Then for each pair (use a loop)
run SELECT userID, sessionID, date, time, pageName FROM sti_tracking
WHERE userID = {provide from loop} AND sessionID = {provide from loop}
LIMIT 1.
Peter Aganyo
Tim Winters wrote:
Hello,
Very sorry to everyone about the confusing message. I should have read
it over again before pressing send.
First of all I'm looking for userID 999. A typo in the message not in
the code.
The table is set up like this.
Table name sti_tracking
hitID (primary key) (autonumber)
userID
sessionID
date
time
pageName
What it's for is a simple page tracing counter for a FLash site. Each
time a section is accessed a new row is written in the table.
userID identifies the user. So if the user comes to the site today and
comes back again tomorrow the userID will be maintained.
sessionID identifies 1 visit to the site. During 1 visit a user may
view many sections within the site but as long as he doesn't close the
browser the session number remains the same. Date and time will always
be different (as will the hitID obviously).
So what I want to be able to do is single out a user (999) and retrieve
all the sessions he was involved in. But I don't want duplicate
session numbers (one is enough).
Make any more sense?
Tim Winters
Creative Development Manager
Sampling Technologies Incorporated
--snip--
While I was trying to figure an elegant solution to this I noticed that
you have a separate date and time field. Is there a reason for this. It
would be easier to get single row for each sessionID if they were one
field. Otherwise I think you will have to go with the method Peter
proposed above.
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT problem
Tim:
Assuming that in your ealier posting the 99 was supposed to be 999,
then
the solution given by Mike Hillyer is excellent and should work.
However, when I read your new posting, I seem to get confused. The
scenario sounds totally different - excuse me - from the earlier one
and
would therefore need a different solution. You might help us by
giving
sample data.
Or is this what you mean by But I don't want duplicate session
numbers (one is enough)? == In a single session (sessionID) user 999
(userID 999) may visit 3 pages. This results in three inserts being
made into table sti_tracking all having same sessionID and userID.
Correct? When retrieving you do not want to retrieve all these three
records. Correct?
You just want one of the records. Which one? The first, second or
third because they each probably have a different time and pageName
(even
date!!). If you did not want the date, time and pageName then the
solution is simple SELECT DISTINCT userID, sessionID FROM sti_tracking
WHERE userID = 999.
If you do not care which of the entries (3 in my example) is returned
and you still want the date, time and pageName (my guess is the first
will be returned), then you need to generate all the distinct userID
and
sessionID pairs using the above SQL. Then for each pair (use a loop)
run SELECT userID, sessionID, date, time, pageName FROM sti_tracking
WHERE userID = {provide from loop} AND sessionID = {provide from
loop} LIMIT 1.
Peter Aganyo
Tim Winters wrote:
Hello,
Very sorry to everyone about the confusing message. I should have
read
it over again before pressing send.
First of all I'm looking for userID 999. A typo in the message not in
the code.
The table is set up like this.
Table name sti_tracking
hitID (primary key) (autonumber)
userID
sessionID
date
time
pageName
What it's for is a simple page tracing counter for a FLash site. Each
time a section is accessed a new row is written in the table.
userID identifies the user. So if the user comes to the site today and
comes back again tomorrow the userID will be maintained.
sessionID identifies 1 visit to the site. During 1 visit a user may
view many sections within the site but as long as he doesn't close
the browser the session number remains the same. Date and time will
always be different (as will the hitID obviously).
So what I want to be able to do is single out a user (999) and
retrieve
all the sessions he was involved in. But I don't want duplicate
session numbers (one is enough).
Make any more sense?
Tim Winters
Creative Development Manager
Sampling Technologies Incorporated
--snip--
While I was trying to figure an elegant solution to this I noticed that
you have a separate date and time field. Is there a reason for this.
It would be easier to get single row for each sessionID if they were one
field. Otherwise I think you will have to go with the method Peter
proposed above.
--Somedays I just need more tea.. ok how about this:
select sessionID,max(concat(idate,' ',itime)) from test group by sessionID
Note I thought date and time were reserved so I substituted..
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie SELECT problem
Hello Peter, I agree I don't seem to be explaining what I want properly. Let me offer some data: (hopefully this will come out ok in the message). hitID sessionID userID timedatepageName 251 2917000 3591528 6:22:17 2003-7-2News Section 250 2917000 3591528 6:22:6 2003-7-2News Section 249 2917000 3591528 6:22:2 2003-7-2News Section 248 2917000 3591528 6:21:53 2003-7-2News Section 247 2917000 3591528 6:21:48 2003-7-2News Section 246 2769572 3630081 6:21:2 2003-7-2Slider : Pharma Facts 245 2769572 3630081 6:21:0 2003-7-2Slider : Pharma Facts 244 3281651 3630081 6:20:10 2003-7-2Slider : SmartSample Benefits 243 9198624 7495400 5:56:3 2003-7-2Physician : ExpressSample 242 6250098 7495400 5:51:16 2003-7-2Investor Login 241 3053989 7495400 5:22:47 2003-7-2Patient : When dealing with Meds 240 3208231 930881 5:20:42 2003-7-2Corporate Services Login 239 5815985 7206699 5:19:17 2003-7-2News Section 238 5358098 637853 5:17:50 2003-7-2Payer : Reduces Costs 237 5909188 637853 5:17:33 2003-7-2Pharma Company : Pharma Companies 236 7699639 951681 5:16:22 2003-7-2Slider : Mission Statement 235 7699639 951681 5:16:13 2003-7-2Slider : CSNM 234 6259004 951681 5:15:27 2003-7-2Pharma Company : Ready, Set, Go 233 2592554 9502072 5:4:59 2003-7-2Physician : ExpressSample 232 4721794 9502072 5:3:32 2003-7-2Pharma Company : Ready, Set, Go 231 4721794 9502072 5:3:22 2003-7-2Slider : Mission Statement 230 3053989 7495400 4:59:40 2003-7-2About STI : Board of Directors 229 3053989 7495400 4:59:25 2003-7-2Slider : Pharma Facts 228 2236829 7495400 4:58:9 2003-7-2Slider : CSNM 227 2236829 7495400 4:56:39 2003-7-2Slider : Paragon So this is the data. Lets say I want my query to give me the sessions for userID 7495400. As you can see there are lots of userID's of that number listed in the table and they are sometimes associated with different sessionID's. The sessionID's are what I'm after. Now the way I had the query was SELECT DISTINCT sessionID, date,time FROM sti_tracking WHERE userID=7495400 But what it gives me is this: sessionID timedatepageName 9198624 5:56:3 2003-7-2Physician : ExpressSample 6250098 5:51:16 2003-7-2Investor Login 3053989 5:22:47 2003-7-2Patient : When dealing with Meds 3053989 4:59:40 2003-7-2About STI : Board of Directors 3053989 4:59:25 2003-7-2Slider : Pharma Facts 2236829 4:58:9 2003-7-2Slider : CSNM 2236829 4:56:39 2003-7-2Slider : Paragon Note the multiple instances of the sessionID's What I want returned is this: sessionID timedatepageName 9198624 5:56:3 2003-7-2Physician : ExpressSample 6250098 5:51:16 2003-7-2Investor Login 3053989 5:22:47 2003-7-2Patient : When dealing with Meds 2236829 4:58:9 2003-7-2Slider : CSNM I don't really care which sessionID in relation to date/time it chooses as long as it's consistent. I just want a general idea of date and time (really date is always going to be the same for each session). Mike H gave me a great solution using multiselects but unfortunate the version of mysql I'm working with doesn't support that (3.23). Maybe this isn't possible with only mySQL. Perhaps I have to do some work with the data in PHP as well (which Mike also suggested). One last search for a mySQL answer and then I'll hunker down for a coding solution. Thanks everyone who responded. Tim Winters Manager, Creative Development Sampling Technologies Incorporated (STI) [EMAIL PROTECTED] [EMAIL PROTECTED] W: 902 450 5500 C: 902 430 8498 -Original Message- From: Peter K Aganyo [mailto:[EMAIL PROTECTED] Sent: July 2, 2003 8:00 PM To: [EMAIL PROTECTED] Subject: Re: Newbie SELECT problem Tim: Assuming that in your ealier posting the 99 was supposed to be 999, then the solution given by Mike Hillyer is excellent and should work. However, when I read your new posting, I seem to get confused. The scenario sounds totally different - excuse me - from the earlier one and would therefore need a different solution. You might help us by giving sample data. Or is this what you mean by But I don't want duplicate session numbers (one is enough)? == In a single session (sessionID) user 999 (userID 999) may visit 3 pages. This results in three inserts being made into table sti_tracking all having same sessionID and userID. Correct? When retrieving you do not want to retrieve all these three records. Correct? You just want one of the records. Which one? The first, second or third because they each probably have a different time and pageName (even date!!). If you did not want the date, time and pageName then the solution is simple SELECT
Re: SELECT problem with mysql 3.23.53-log
Stefan Schulte [EMAIL PROTECTED] wrote: i am analyzing a very strange behaviour of mysql-3.23-53-log on a Suse 8.1 system: I have created a table Customer with a column: customer_id int(11) Now i want to select all rows with customer_id=41: SELECT * from Customer WHERE customer_id=41; The result is: Empty set (0.13 sec) If i change the query to: SELECT * from Customer WHERE customer_id LIKE 41 then i get all results !? If I search for Customers with Ids 20 I also get results. It think, that my provider has updated the SuSe-Release or the mysql-Version of my Server. Is there any configuration or option of mysql that can cause this strange behaviour ??? No. Any other ideas ? Try to recreate indexes. If it doesn't help create a repeatable test case. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT problem with mysql 3.23.53-log
Hi all, i am analyzing a very strange behaviour of mysql-3.23-53-log on a Suse 8.1 system: I have created a table Customer with a column: customer_id int(11) Now i want to select all rows with customer_id=41: SELECT * from Customer WHERE customer_id=41; The result is: Empty set (0.13 sec) If i change the query to: SELECT * from Customer WHERE customer_id LIKE 41 then i get all results !? If I search for Customers with Ids 20 I also get results. It think, that my provider has updated the SuSe-Release or the mysql-Version of my Server. Is there any configuration or option of mysql that can cause this strange behaviour ??? Any other ideas ? Thanks Stefan Jetzt bei WEB.DE FreeMail anmelden = 1qm Regenwald schuetzen! Helfen Sie mit! Nutzen Sie den Serien-Testsieger. http://user.web.de/Regenwald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Problem
Hi Just try this select distinct(a.id) from test a , test b where a.code = 23 and b.code = 45 and a.id = b.id Regards, -Arul - Original Message - From: Robert Gehrig [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 10:52 PM Subject: Select Problem Hi all I have a detail table that has multiple records associated with an ID number Both fields are integers E.G. Id Code 4 23 4 27 34 23 34 45 34 28 What I need to find is the Id where the code is 23 and 45 for the same Id (the result in this case would be 34) How do I do this with a SQL query Thanks Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select Problem
Hi all I have a detail table that has multiple records associated with an ID number Both fields are integers E.G. Id Code 4 23 4 27 34 23 34 45 34 28 What I need to find is the Id where the code is 23 and 45 for the same Id (the result in this case would be 34) How do I do this with a SQL query Thanks Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select problem with not equal syntax
Found solution, the right syntax is: SELECT hl7incom.id FROM hl7incom, pid_segment LEFT JOIN pid_segment ON hl7incom.id = pid_segment.id WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.id IS NULL; Cheers for try of help, Karsten Same result, also if I do not define unique index. Just a suggestion: SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND not(pid_segment.id = hl7incom.id) GROUP BY hl7incom.id; Otherwise my only other suggestion would involve using the 'NOT IN' logic, but I think that might be too convoluted for your needs. No way, I've already tried this. I have two tables CREATE TABLE pid_segment ( id INT NOT NULL UNIQUE PRIMARY KEY, msg TEXT) TYPE=INNODB CREATE TABLE hl7incom( id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment (id). msg TEXT, time TIMESTAMP NOT NULL) TYPE=INNODB There are few data stored in both tables. Now I will select new messages from hl7incom, where hl7incom.id is not equal pid_segment.id and store this id, msg in pid_segment. With query... SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.id != hl7incom.id GROUP BY hl7incom.id; Try SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.idhl7incom.id GROUP BY hl7incom.id; John Coder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select problem with not equal syntax
I have two tables CREATE TABLE pid_segment ( id INT NOT NULL UNIQUE PRIMARY KEY, msg TEXT) TYPE=INNODB CREATE TABLE hl7incom( id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment (id). msg TEXT, time TIMESTAMP NOT NULL) TYPE=INNODB There are few data stored in both tables. Now I will select new messages from hl7incom, where hl7incom.id is not equal pid_segment.id and store this id, msg in pid_segment. With query... SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.id != hl7incom.id GROUP BY hl7incom.id; ...I get also the id's which are allready in table pid_segment. I tried different querys, also with LeftJoins but couldn't fix the problem. Anybody some idea? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select problem with not equal syntax
On Mon, 2002-09-30 at 23:44, Gebhardt, Karsten wrote: I have two tables CREATE TABLE pid_segment ( id INT NOT NULL UNIQUE PRIMARY KEY, msg TEXT) TYPE=INNODB CREATE TABLE hl7incom( id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment (id). msg TEXT, time TIMESTAMP NOT NULL) TYPE=INNODB There are few data stored in both tables. Now I will select new messages from hl7incom, where hl7incom.id is not equal pid_segment.id and store this id, msg in pid_segment. With query... SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.id != hl7incom.id GROUP BY hl7incom.id; Try SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.idhl7incom.id GROUP BY hl7incom.id; John Coder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select problem with not equal syntax
No way, I've already tried this. I have two tables CREATE TABLE pid_segment ( id INT NOT NULL UNIQUE PRIMARY KEY, msg TEXT) TYPE=INNODB CREATE TABLE hl7incom( id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment (id). msg TEXT, time TIMESTAMP NOT NULL) TYPE=INNODB There are few data stored in both tables. Now I will select new messages from hl7incom, where hl7incom.id is not equal pid_segment.id and store this id, msg in pid_segment. With query... SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.id != hl7incom.id GROUP BY hl7incom.id; Try SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.idhl7incom.id GROUP BY hl7incom.id; John Coder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select problem with not equal syntax
Just a suggestion: SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND not(pid_segment.id = hl7incom.id) GROUP BY hl7incom.id; Otherwise my only other suggestion would involve using the 'NOT IN' logic, but I think that might be too convoluted for your needs. -Original Message- From: Gebhardt, Karsten [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 01, 2002 12:07 AM To: '[EMAIL PROTECTED]' Subject: RE: select problem with not equal syntax No way, I've already tried this. I have two tables CREATE TABLE pid_segment ( id INT NOT NULL UNIQUE PRIMARY KEY, msg TEXT) TYPE=INNODB CREATE TABLE hl7incom( id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment (id). msg TEXT, time TIMESTAMP NOT NULL) TYPE=INNODB There are few data stored in both tables. Now I will select new messages from hl7incom, where hl7incom.id is not equal pid_segment.id and store this id, msg in pid_segment. With query... SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.id != hl7incom.id GROUP BY hl7incom.id; Try SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.idhl7incom.id GROUP BY hl7incom.id; John Coder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select problem with not equal syntax
Same result, also if I do not define unique index. Just a suggestion: SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND not(pid_segment.id = hl7incom.id) GROUP BY hl7incom.id; Otherwise my only other suggestion would involve using the 'NOT IN' logic, but I think that might be too convoluted for your needs. No way, I've already tried this. I have two tables CREATE TABLE pid_segment ( id INT NOT NULL UNIQUE PRIMARY KEY, msg TEXT) TYPE=INNODB CREATE TABLE hl7incom( id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment (id). msg TEXT, time TIMESTAMP NOT NULL) TYPE=INNODB There are few data stored in both tables. Now I will select new messages from hl7incom, where hl7incom.id is not equal pid_segment.id and store this id, msg in pid_segment. With query... SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.id != hl7incom.id GROUP BY hl7incom.id; Try SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.idhl7incom.id GROUP BY hl7incom.id; John Coder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select problem
Here is my table on which I am doing sql query: ++--+-+---+--+-+ | fdr_id | fdr_uid | fdr_form_id | fdr_ff_id | fdr_value | fdr_date| ++--+-+---+--+-+ | 63 | Fmct560CMs9m8m6K | 37 | 193 | goodbye | 2002-07-08 11:44:35 | | 62 | Fmct560CMs9m8m6K | 37 | 192 | Clay | 2002-07-08 11:44:35 | | 61 | Fmct560CMs9m8m6K | 37 | 191 | A | 2002-07-08 11:44:35 | | 60 | Fmct560CMs9m8m6K | 37 | 190 | Keith | 2002-07-08 11:44:35 | | 64 | Fmct560CMs9m8m6K | 37 | 194 | test01 | 2002-07-08 11:44:35 | | 65 | Jowl587FHe3m4y8U | 37 | 190 | John | 2002-07-08 11:45:10 | | 66 | Jowl587FHe3m4y8U | 37 | 191 | Q | 2002-07-08 11:45:10 | | 67 | Jowl587FHe3m4y8U | 37 | 192 | Public | 2002-07-08 11:45:10 | | 68 | Jowl587FHe3m4y8U | 37 | 193 | hello | 2002-07-08 11:45:10 | | 69 | Jowl587FHe3m4y8U | 37 | 194 | test02 test03 test04 | 2002-07-08 11:45:10 | | 70 | Nfal733ELh6y6z1M | 37 | 190 | Keith | 2002-07-09 11:56:24 | | 71 | Nfal733ELh6y6z1M | 37 | 191 | G | 2002-07-09 11:56:24 | | 72 | Nfal733ELh6y6z1M | 37 | 192 | Public | 2002-07-09 11:56:24 | | 73 | Nfal733ELh6y6z1M | 37 | 193 | hello | 2002-07-09 11:56:24 | | 74 | Nfal733ELh6y6z1M | 37 | 194 | yes | 2002-07-09 11:56:24 | ++--+-+---+--+-+ This is the output of a submitted form so that anything with the same fdr_uid is from the same submitted form and every thing with fdr_form_id the same is from the form( there are multiple submitted forms for each form which is basically the template) and fdr_ff_id is the question on the form. I am trying to search for entries on fdr_form_id='37' and I want entries where fdr_ff_id='190' and contains keith and fdr_ff_id='192' and contains public. This select is dynamically generated. select * from forms_data_recs where fdr_form_id='37' and ( fdr_ff_id = '190' and ( fdr_value like '%keith%' )) and ( fdr_ff_id = '192' and ( fdr_value like '%public%' )) order by fdr_date,fdr_id I get no records. What am I doing wrong if anything? What can I do to get the select to work? keith -- - Keith Clay, [EMAIL PROTECTED] Lead Programmer, Web Integration and Programming 286 Adams Center for Teaching Excellence Abilene Christian University Abilene, TX 79699 (915) 674-2187 (915) 674-2834 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select problem
select * from forms_data_recs where fdr_form_id='37' and ( fdr_ff_id ='190' and ( fdr_value like '%keith%' )) OR ( fdr_ff_id = '192' and (fdr_value like '%public%' )) order by fdr_date,fdr_id -Original Message- From: Keith Clay [mailto:[EMAIL PROTECTED]] Sent: Friday, August 09, 2002 12:42 PM To: [EMAIL PROTECTED] Subject: select problem Here is my table on which I am doing sql query: ++--+-+---+- -+-+ | fdr_id | fdr_uid | fdr_form_id | fdr_ff_id | fdr_value | fdr_date| ++--+-+---+- -+-+ | 63 | Fmct560CMs9m8m6K | 37 | 193 | goodbye | 2002-07-08 11:44:35 | | 62 | Fmct560CMs9m8m6K | 37 | 192 | Clay | 2002-07-08 11:44:35 | | 61 | Fmct560CMs9m8m6K | 37 | 191 | A | 2002-07-08 11:44:35 | | 60 | Fmct560CMs9m8m6K | 37 | 190 | Keith | 2002-07-08 11:44:35 | | 64 | Fmct560CMs9m8m6K | 37 | 194 | test01 | 2002-07-08 11:44:35 | | 65 | Jowl587FHe3m4y8U | 37 | 190 | John | 2002-07-08 11:45:10 | | 66 | Jowl587FHe3m4y8U | 37 | 191 | Q | 2002-07-08 11:45:10 | | 67 | Jowl587FHe3m4y8U | 37 | 192 | Public | 2002-07-08 11:45:10 | | 68 | Jowl587FHe3m4y8U | 37 | 193 | hello | 2002-07-08 11:45:10 | | 69 | Jowl587FHe3m4y8U | 37 | 194 | test02 test03 test04 | 2002-07-08 11:45:10 | | 70 | Nfal733ELh6y6z1M | 37 | 190 | Keith | 2002-07-09 11:56:24 | | 71 | Nfal733ELh6y6z1M | 37 | 191 | G | 2002-07-09 11:56:24 | | 72 | Nfal733ELh6y6z1M | 37 | 192 | Public | 2002-07-09 11:56:24 | | 73 | Nfal733ELh6y6z1M | 37 | 193 | hello | 2002-07-09 11:56:24 | | 74 | Nfal733ELh6y6z1M | 37 | 194 | yes | 2002-07-09 11:56:24 | ++--+-+---+- -+-+ This is the output of a submitted form so that anything with the same fdr_uid is from the same submitted form and every thing with fdr_form_id the same is from the form( there are multiple submitted forms for each form which is basically the template) and fdr_ff_id is the question on the form. I am trying to search for entries on fdr_form_id='37' and I want entries where fdr_ff_id='190' and contains keith and fdr_ff_id='192' and contains public. This select is dynamically generated. select * from forms_data_recs where fdr_form_id='37' and ( fdr_ff_id = '190' and ( fdr_value like '%keith%' )) and ( fdr_ff_id = '192' and ( fdr_value like '%public%' )) order by fdr_date,fdr_id I get no records. What am I doing wrong if anything? What can I do to get the select to work? keith -- - Keith Clay, [EMAIL PROTECTED] Lead Programmer, Web Integration and Programming 286 Adams Center for Teaching Excellence Abilene Christian University Abilene, TX 79699 (915) 674-2187 (915) 674-2834 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select Problem
I want to select from three tables where there may or may not be a record in the third table. Table 1 and 2 have a one to one relationship and table 1 and 2 both have a one to many relationship with table three. All three tables have a column called 'code' and I want to select where code is in table one and table1.code = table2.code and table1.code = table3.code. I also want the records that are in table 1 and 2 but do not have any any record(s) in table three. Is it possible to build a query that would get the results into one recordset? I have tried various queries that don't quite return what I need. I hope this makes some sort of sense. This e-mail is intended for the recipient only and may contain confidential information. If you are not the intended recipient then you should reply to the sender and take no further ation based upon the content of the message. Internet e-mails are not necessarily secure and CCM Limited does not accept any responsibility for changes made to this message. Although checks have been made to ensure this message and any attchments are free from viruses the recipient should ensure that this is the case. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Problem
Hello! This one should work: SELECT ... FROM table1 t1 LEFT JOIN table2 t2 USING (code) LEFT OUTER JOIN table3 t3 USING(code) WHERE t1.code = t3.code OR t3.code IS NULL ; Mark Colvin wrote: I want to select from three tables where there may or may not be a record in the third table. Table 1 and 2 have a one to one relationship and table 1 and 2 both have a one to many relationship with table three. All three tables have a column called 'code' and I want to select where code is in table one and table1.code = table2.code and table1.code = table3.code. I also want the records that are in table 1 and 2 but do not have any any record(s) in table three. Is it possible to build a query that would get the results into one recordset? I have tried various queries that don't quite return what I need. I hope this makes some sort of sense. Greetings Ralf -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Problem
Hello! This one should work: SELECT ... FROM table1 t1 LEFT JOIN table2 t2 USING (code) LEFT OUTER JOIN table3 t3 USING(code) WHERE t1.code = t3.code OR t3.code IS NULL ; Mark Colvin wrote: I want to select from three tables where there may or may not be a record in the third table. Table 1 and 2 have a one to one relationship and table 1 and 2 both have a one to many relationship with table three. All three tables have a column called 'code' and I want to select where code is in table one and table1.code = table2.code and table1.code = table3.code. I also want the records that are in table 1 and 2 but do not have any any record(s) in table three. Is it possible to build a query that would get the results into one recordset? I have tried various queries that don't quite return what I need. I hope this makes some sort of sense. Greetings Ralf -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Problem
Hi, Use LEFT JOIN instead = Bye and Good Luck! --- Mark Colvin [EMAIL PROTECTED] wrote: I want to select from three tables where there may or may not be a record in the third table. Table 1 and 2 have a one to one relationship and table 1 and 2 both have a one to many relationship with table three. All three tables have a column called 'code' and I want to select where code is in table one and table1.code = table2.code and table1.code = table3.code. I also want the records that are in table 1 and 2 but do not have any any record(s) in table three. Is it possible to build a query that would get the results into one recordset? I have tried various queries that don't quite return what I need. I hope this makes some sort of sense. This e-mail is intended for the recipient only and may contain confidential information. If you are not the intended recipient then you should reply to the sender and take no further ation based upon the content of the message. Internet e-mails are not necessarily secure and CCM Limited does not accept any responsibility for changes made to this message. Although checks have been made to ensure this message and any attchments are free from viruses the recipient should ensure that this is the case. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Date and Select problem
My SQL query problem: I met a strange problem: For example, I have the following table: Mytable: No Name Date Project 1 Bob 2002-05-27Bob's project 2 John -00-00 John's project When I use select * from Mytable WHERE Date = 'WWW'; it gives me the result: 2 John -00-00 John's project I think it should not return any rows. Why? Thanks. I. TS - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date and Select problem
Hehe, this is almost identical to the post I submitted yesterday, entitled SELECT 'foobar' = 0 There's a patch in that post, doesn't work for dates but should give an idea of what has to be done. The reason is that 'WWW' has to be converted to a date. And the conversion gives it a value of 0. So you're comparing 0 and 0, which results in 'true'. Anyway, that's why it happens :). Adam Hooper [EMAIL PROTECTED] On Tue, 28 May 2002 11:07:08 -0600 I. TS [EMAIL PROTECTED] wrote: My SQL query problem: I met a strange problem: For example, I have the following table: Mytable: No Name Date Project 1 Bob 2002-05-27Bob's project 2 John -00-00 John's project When I use select * from Mytable WHERE Date = 'WWW'; it gives me the result: 2 John -00-00 John's project I think it should not return any rows. Why? Thanks. I. TS - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Simple select problem.
How about SELECT @last:=MAX(Date + 0) from stat; Eyal Rif wrote: Hi, I have a table with the format of : char(20),int(7),char(32) the Date char(20) contents in actually a number(seconds since 1970) - I want to use a select query that will give the max value according to number value of that column. select @last:=MAX(Date) from stat1; What I need to know is how to convert a string to a number on a select statement. Any help/reference will be appreciated Thanks, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Gerald L. Clark [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Simple select problem.
Hi, I have a table with the format of : char(20),int(7),char(32) the Date char(20) contents in actually a number(seconds since 1970) - I want to use a select query that will give the max value according to number value of that column. select @last:=MAX(Date) from stat1; What I need to know is how to convert a string to a number on a select statement. Any help/reference will be appreciated Thanks, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT-problem
Hi there, This is my problem now... I have a table containing different paths, like this: +--++---++ | Path | X | Y | WaypointNr | +--++---++ | P1 | 1 | 5 | 1 | | P1 | 2 | 6 | 2 | | P1 | 3 | 7 | 3 | | P1 | 8 | 3 | 4 | | P2 | 11 | 4 | 1 | | P2 | 7 | 3 | 2 | | P2 | 5 | 2 | 3 | | P2 | 2 | 1 | 4 | +--++---++ I need to draw a map from these paths, so I make a following query to get waypoints and paths located in defined area (where the corners are (3,2) and (7,6). mysql SELECT * FROM waypoints WHERE X 2 AND X 8 AND Y 1 AND Y 7; +--+---+---++ | Path | X | Y | WaypointNr | +--+---+---++ | P2 | 7 | 3 | 2 | | P2 | 5 | 2 | 3 | +--+---+---++ That's OK... but now I would like to get also those waypoints which ones are next to these results (on the same path). In this case, I want also points 1 and 4 on P2. How? Emm... Hope that you could understand even something. ;) - Ville . Ville Mattila Ikaalinen, Finland [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: SELECT-problem
Ville, as you are probably aware, there are no subselects in MySQL. Therefore, you will probably have to retrieve the additional waypoints through a second query. (As an aside: there may exist a solution using self-joins, but from looking at the problem I don't think so) I assume you will be using an API and a programming language. Then you could simply go and say (for Perl DBI): SELECT * FROM waypoints WHERE path = ? and (waypoint_nr = ? -1 or waypoint_nr = ? + 1) Parametrize that with the information from your first result set for each of its rows. Merge all the result sets retrieved and eliminate duplicate combinations of path and waypoint, and there you are. Pretty ugly but workable, I think. In fact using Perl hashes even easy to do. However, whether this approach is feasible for you depends entirely on what combination of API and language/tool you are using for your development. Also, there may be better solutions. If so, the list will probably point them out to you. ;-) Cheers, Christian Sage -Ursprüngliche Nachricht- Von: Ville Mattila [mailto:[EMAIL PROTECTED]] Gesendet: Samstag, 8. September 2001 18:17 An: MySQL-mailinglist Betreff: SELECT-problem Hi there, This is my problem now... I have a table containing different paths, like this: +--++---++ | Path | X | Y | WaypointNr | +--++---++ | P1 | 1 | 5 | 1 | | P1 | 2 | 6 | 2 | | P1 | 3 | 7 | 3 | | P1 | 8 | 3 | 4 | | P2 | 11 | 4 | 1 | | P2 | 7 | 3 | 2 | | P2 | 5 | 2 | 3 | | P2 | 2 | 1 | 4 | +--++---++ I need to draw a map from these paths, so I make a following query to get waypoints and paths located in defined area (where the corners are (3,2) and (7,6). mysql SELECT * FROM waypoints WHERE X 2 AND X 8 AND Y 1 AND Y 7; +--+---+---++ | Path | X | Y | WaypointNr | +--+---+---++ | P2 | 7 | 3 | 2 | | P2 | 5 | 2 | 3 | +--+---+---++ That's OK... but now I would like to get also those waypoints which ones are next to these results (on the same path). In this case, I want also points 1 and 4 on P2. How? Emm... Hope that you could understand even something. ;) - Ville . Ville Mattila Ikaalinen, Finland [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
An interesting SELECT problem
I've been creating a site for someone using MySQL and PHP4. Basically the table concerned is structured like this; id int(5) UNSIGNED Noauto_increment Primary ship varchar(50) NoIndex year varchar(15) NoIndex voyage varchar(50) Yes sex varchar(50)Yes notes text Yes Everthing is working fine except the ship order in which the pages are generated. For example, the following are ships names and yes the records do show the voyage date (they are in official records that way and I can change them. I prudently added the year field and the year is put in that field as well. Some ships don't have this date after their name, sometimes they have just a voyage number. Henrietta Henry Henry Porcher Hibernia Hindostan (1) Hindostan (2) Hyderabad (1) Hyderabad (2) Hyderabad (3) Marian Watson (08-06-1842) Marian Watson (28-10-1841) Marion Watson (08-03-1842) Here in lies the problem, these records have been entered at different times so their id's are all over the place When I get these records and display them, they will be in the correct alphanumeric order except for the ones with the date after them. they will only display in the order they were entered. I've tried the following SELECTs $result = mysql_query(SELECT * FROM ships ORDER BY ship,$db) $result = mysql_query(SELECT * FROM ships ORDER BY ship, year,$db) but they dont sort the way we want them to (in year order) as per below Henrietta Henry Henry Porcher Hibernia Hindostan (1) Hindostan (2) Hyderabad (1) Hyderabad (2) Hyderabad (3) Marian Watson (28-10-1841) Marion Watson (08-03-1842) Marian Watson (08-06-1842) Anyone got any ideas? Thanks in advance Howard Picken [EMAIL PROTECTED] -- Database, SQL, Query etc... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: An interesting SELECT problem
I've been creating a site for someone using MySQL and PHP4. Basically the table concerned is structured like this; id int(5) UNSIGNED Noauto_increment Primary ship varchar(50) NoIndex year varchar(15) NoIndex Sir, change the type of this column to Date, which stores the date as -mm-dd. This will allow you to order the records by the date. You can display the date in a different format using Date_format(). Using a 15 character varchar field for data that is exclusively dates and requires a maximum of 10 characters is an invitation for trouble. Also, since the column contains the full date of the voyage, change the name of the column from year (it's not the year, it's the date) to something like sail_date or embarked. voyage varchar(50) Yes sex varchar(50)Yes How do you determine the sex of a voyage? And why does it take 50 characters to specify it? :-) notes text Yes Everthing is working fine except the ship order in which the pages are generated. For example, the following are ships names and yes the records do show the voyage date (they are in official records that way and I can change them. I prudently added the year field and the year is put in that field as well. Some ships don't have this date after their name, sometimes they have just a voyage number. Henrietta Henry Henry Porcher Hibernia Hindostan (1) Hindostan (2) Hyderabad (1) Hyderabad (2) Hyderabad (3) Marian Watson (08-06-1842) Marian Watson (28-10-1841) Marion Watson (08-03-1842) Here in lies the problem, these records have been entered at different times so their id's are all over the place When I get these records and display them, they will be in the correct alphanumeric order except for the ones with the date after them. they will only display in the order they were entered. I've tried the following SELECTs $result = mysql_query(SELECT * FROM ships ORDER BY ship,$db) $result = mysql_query(SELECT * FROM ships ORDER BY ship, year,$db) but they dont sort the way we want them to (in year order) as per below Henrietta Henry Henry Porcher Hibernia Hindostan (1) Hindostan (2) Hyderabad (1) Hyderabad (2) Hyderabad (3) Marian Watson (28-10-1841) Marion Watson (08-03-1842) Marian Watson (08-06-1842) Anyone got any ideas? Thanks in advance Howard Picken [EMAIL PROTECTED] Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql: DATETIME - MONTH( select problem)
Hi, I'm having the following sql problem: I have two datetime fields (start and end), I need to find out if the month is either equal to start-month or equal to end-month but also if it's a month between start and end. I also check if the end-date is greater than today so it's a current one. My problem is that I can't get the months in between when, how do I do that? Any ideas? $current_month is a php variable. (MONTH(start)= '$current_month' OR MONTH(end) = '$current_month') AND end = CURDATE() Greetings from Sweden Jimmy. database query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql: DATETIME - MONTH( select problem)
Hi, I'm having the following sql problem: I have two datetime fields (start and end), I need to find out if the month is either equal to start-month or equal to end-month but also if it's a month between start and end. I also check if the end-date is greater than today so it's a current one. My problem is that I can't get the months in between when, how do I do that? Any ideas? $current_month is a php variable. (MONTH(start)= '$current_month' OR MONTH(end) = '$current_month') AND end = CURDATE() Greetings from Sweden Jimmy. Sir, try (MONTH(start) = $current_month AND MONTH(end) = $current_month) AND end = CURDATE() The MS Titanic (my wintel machine) just sank again, so I don't have a machine I can run the statement on to see if it is correct. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie Select Problem URGENT
Hi All I am trying to compare information in 2 different tables and return matches. The problem is that this query only returns the listings where I want it to return a row from AutoEmail when it finds a match. What am I doing wrong? SELECT * FROM AutoEmail, listings WHERE ((listings.prop_style LIKE '%AutoEmail.prop_style%') AND (listings.price = AutoEmail.range) AND (listings.bedrooms = AutoEmail.bedrooms) AND (listings.bathrooms = AutoEmail.bathrooms) AND (listings.garage LIKE '%AutoEmail.garage%') AND (listings.fpropover LIKE '%AutoEmail.feature%') AND (listings.location LIKE '%AutoEmail.location%')) AND ((AutoEmail.residential = listings.residential) OR (AutoEmail.multi = listings.multi) AND (AutoEmail.farm = listings.farm) OR (AutoEmail.waterfront = listings.waterfront) OR (AutoEmail.commercial = listings.commercial) OR (AutoEmail.vacant = listings.vacant)) -- Cheers! Ken Tuck EyeCreate Inc. Net~Solutions Design - Hosting - E-Commerce [EMAIL PROTECTED] http://www.eyecreate.net/ ph: 705.755.1120 fx: 705.743.9259 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select problem
Hi, I having trouble working out how to get a result set similar to the following where I select from a table with Date Sales column. My specific question is can I have a column that accumulates values, if so could I have some guidance on how to express this in a select statement please. +--+---+---+ | Month| Sales | Cum Sales | +--+---+---+ | Jan | 1000 | 1000 | | Feb | 1500 | 2500 | | Mar | 1200 | 3700 | | April| 1400 | 5100 | +--+---+---+ Many thanks in advance. Richard Sir, at times like this it's handy to have a copy of 'SQL for Smarties'. The following is taken from one of the examples, with only the table and column names changed. SELECT s1.mnd, s1.sales, Sum(s2.sales) AS Cum_sales FROM sales AS s1, sales AS s2 WHERE s2.mnd = s1.mnd GROUP BY s1.mnd; Note that I used mnd instead of Month. Month is a function name, so you probably don't want to use it for a column name. I used a date instead of a month name to make the WHERE clause work, i.e. the mnd column contained 2001-1-1, 2001-2-1, 2001-3-1, 2001-4-1, instead of Jan, Feb, etc. I ran the statement above on the MS Titanic (aka my Wintel box) and it worked fine. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select problem
Hi, I having trouble working out how to get a result set similar to the following where I select from a table with Date Sales column. My specific question is can I have a column that accumulates values, if so could I have some guidance on how to express this in a select statement please. +--+---+---+ | Month| Sales | Cum Sales | +--+---+---+ | Jan | 1000 | 1000 | | Feb | 1500 | 2500 | | Mar | 1200 | 3700 | | April| 1400 | 5100 | +--+---+---+ Many thanks in advance. Richard === Richard Vibert [EMAIL PROTECTED] Tatura Mitre10 === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select problem
On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, I having trouble working out how to get a result set similar to the following where I select from a table with Date Sales column. My specific question is can I have a column that accumulates values, if so could I have some guidance on how to express this in a select statement please. +--+---+---+ | Month| Sales | Cum Sales | +--+---+---+ | Jan | 1000 | 1000 | | Feb | 1500 | 2500 | | Mar | 1200 | 3700 | | April| 1400 | 5100 | +--+---+---+ Many thanks in advance. Richard I assume the query should be like: select MONTHNAME(date_col) as Month,count(sales_amount) as Sales,sum(sales_amount) as Cum_sales from lala_table group by MONTHNAME(date_col); regards, thalis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select problem
Hi, At 01:52 pm 2/03/2001, Thalis A. Kalfigopoulos wrote: On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, I having trouble working out how to get a result set similar to the following where I select from a table with Date Sales column. My specific question is can I have a column that accumulates values, if so could I have some guidance on how to express this in a select statement please. +--+---+---+ | Month| Sales | Cum Sales | +--+---+---+ | Jan | 1000 | 1000 | | Feb | 1500 | 2500 | | Mar | 1200 | 3700 | | April| 1400 | 5100 | +--+---+---+ Many thanks in advance. Richard I assume the query should be like: select MONTHNAME(date_col) as Month,count(sales_amount) as Sales,sum(sales_amount) as Cum_sales from lala_table group by MONTHNAME(date_col); regards, thalis Thanks for your reply. This is not quite what I'm after. Count(sales) gives me the number of sales transactions. The first two cols I write as monthname(date_col) as Month, sum(sales) as Sales It's the next column that's got me. That needs to be a "Running total" if you like. Richard === Richard Vibert [EMAIL PROTECTED] Tatura Mitre10 === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select problem
Now that I notice more closely the numbers, my answer was obviously wrong with regard to the 3rd column :o) Very interesting question...but I doubt there is a SQL way to do that. Looking fwd to what the rest will sugest. cheers, thalis On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, At 01:52 pm 2/03/2001, Thalis A. Kalfigopoulos wrote: On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, I having trouble working out how to get a result set similar to the following where I select from a table with Date Sales column. My specific question is can I have a column that accumulates values, if so could I have some guidance on how to express this in a select statement please. +--+---+---+ | Month| Sales | Cum Sales | +--+---+---+ | Jan | 1000 | 1000 | | Feb | 1500 | 2500 | | Mar | 1200 | 3700 | | April| 1400 | 5100 | +--+---+---+ Many thanks in advance. Richard I assume the query should be like: select MONTHNAME(date_col) as Month,count(sales_amount) as Sales,sum(sales_amount) as Cum_sales from lala_table group by MONTHNAME(date_col); regards, thalis Thanks for your reply. This is not quite what I'm after. Count(sales) gives me the number of sales transactions. The first two cols I write as monthname(date_col) as Month, sum(sales) as Sales It's the next column that's got me. That needs to be a "Running total" if you like. Richard === Richard Vibert [EMAIL PROTECTED] Tatura Mitre10 === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select problem
Can't you do something with SUM() to get the results, possibly in coordination with GROUP BY? --Nathan On 2001.03.01 23:49:28 -0500 Thalis A. Kalfigopoulos wrote: Now that I notice more closely the numbers, my answer was obviously wrong with regard to the 3rd column :o) Very interesting question...but I doubt there is a SQL way to do that. Looking fwd to what the rest will sugest. cheers, thalis On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, At 01:52 pm 2/03/2001, Thalis A. Kalfigopoulos wrote: On Fri, 2 Mar 2001, Richard Vibert wrote: Hi, I having trouble working out how to get a result set similar to the following where I select from a table with Date Sales column. My specific question is can I have a column that accumulates values, if so could I have some guidance on how to express this in a select statement please. +--+---+---+ | Month| Sales | Cum Sales | +--+---+---+ | Jan | 1000 | 1000 | | Feb | 1500 | 2500 | | Mar | 1200 | 3700 | | April| 1400 | 5100 | +--+---+---+ Many thanks in advance. Richard I assume the query should be like: select MONTHNAME(date_col) as Month,count(sales_amount) as Sales,sum(sales_amount) as Cum_sales from lala_table group by MONTHNAME(date_col); regards, thalis Thanks for your reply. This is not quite what I'm after. Count(sales) gives me the number of sales transactions. The first two cols I write as monthname(date_col) as Month, sum(sales) as Sales It's the next column that's got me. That needs to be a "Running total" if you like. Richard === Richard Vibert [EMAIL PROTECTED] Tatura Mitre10 === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Nathan Clemons [EMAIL PROTECTED] 978-635-5300 ext 123 Linux Systems Administrator IRC: etrnl ICQ: 2810688 AIM: StormeRidr O | S | D | N,50 Nagog Park,Acton,MA01720 http://www.osdn.com/ Open Source Development Network Nextel: 978-423-0165 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select problem
On Fri, 2 Mar 2001, Nathan Clemons wrote:
Can't you do something with SUM() to get the results, possibly in
coordination with GROUP BY?
--Nathan
Not to my knowledge/imagination. What are you going to group by? You want and
incremental grouping or better you want a dynamic calculation on a very specific
subset of the rows (0-current_row) while current_row inrcrements through the result.
This cannot be done in a query and probably not in SQL at all.
I can only imagine this as a loop in a higher level language (take your pick:
perl/php/C). I would start thinking of creating a temporary table with
Id,Month,Sum(sales) and from there getting the runnning sales (the Id would range from
1-12).
dummy_code follows:
for(curr_count=1;curr_count=12;curr_count++){
pose_query("select curr_count,sum(sales_per_month) from temp_table where
id=curr_count");
}
Maybe I'm too tired to see straight and the answer is looking at me straight in the
face but that was my $0.02 anyhow.
regards,
thalis
On 2001.03.01 23:49:28 -0500 Thalis A. Kalfigopoulos wrote:
Now that I notice more closely the numbers, my answer was obviously wrong
with regard to the 3rd column :o)
Very interesting question...but I doubt there is a SQL way to do that.
Looking fwd to what the rest will sugest.
cheers,
thalis
On Fri, 2 Mar 2001, Richard Vibert wrote:
Hi,
At 01:52 pm 2/03/2001, Thalis A. Kalfigopoulos wrote:
On Fri, 2 Mar 2001, Richard Vibert wrote:
Hi,
I having trouble working out how to get a result set similar to the
following where I select from a table with Date Sales column.
My specific question is can I have a column that accumulates
values, if so
could I have some guidance on how to express this in a select
statement
please.
+--+---+---+
| Month| Sales | Cum Sales |
+--+---+---+
| Jan | 1000 | 1000 |
| Feb | 1500 | 2500 |
| Mar | 1200 | 3700 |
| April| 1400 | 5100 |
+--+---+---+
Many thanks in advance.
Richard
I assume the query should be like:
select MONTHNAME(date_col) as Month,count(sales_amount) as
Sales,sum(sales_amount) as Cum_sales from lala_table group by
MONTHNAME(date_col);
regards,
thalis
Thanks for your reply.
This is not quite what I'm after. Count(sales) gives me the number of
sales
transactions.
The first two cols I write as monthname(date_col) as Month, sum(sales)
as Sales
It's the next column that's got me. That needs to be a "Running total"
if
you like.
Richard
===
Richard Vibert
[EMAIL PROTECTED]
Tatura Mitre10
===
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Nathan Clemons [EMAIL PROTECTED] 978-635-5300 ext 123
Linux Systems Administrator IRC: etrnl ICQ: 2810688 AIM: StormeRidr
O | S | D | N,50 Nagog Park,Acton,MA01720
http://www.osdn.com/ Open Source Development Network
Nextel: 978-423-0165 [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select Problem
Linux , Apache, MySQL I have a problem in my MySQL database, this is the senario: I made an update statement on my table by wrong, then I restor my backup copy by copying the backup files on the exist ones without stopping the MySQL. Now I have problems in searching data: for example when I made select statement using = , no results, but when I use %something% I can get the result, this thing not happend with all records, just some of them. any help will be appreciated. thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Problem
Try rebuilding the indexes. -Original Message- From: Alaiddin Tayeh [SMTP:[EMAIL PROTECTED]] Sent: 15 February 2001 10:34 To: [EMAIL PROTECTED] Subject: Select Problem Linux , Apache, MySQL I have a problem in my MySQL database, this is the senario: I made an update statement on my table by wrong, then I restor my backup copy by copying the backup files on the exist ones without stopping the MySQL. Now I have problems in searching data: for example when I made select statement using = , no results, but when I use %something% I can get the result, this thing not happend with all records, just some of them. any help will be appreciated. thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select-Problem
Sir, Joe Celko's 'SQL For Smarties' has two chapters devoted to tree problems. After a quick look in the book, it appears to me that you can use one of his algorithms if you restructure your table and adapt his SQL to the MySQL dialect. See the chapter on Nested Set Models. Bob Hall Hi, I have a recursive Problem. I have a Table with columns "id", "name" and "pid". E.g: name1 name11 name12 name121 name122 name13 name2 name21 In the Table it would look like: id, name, pid 1 name1 0 2 name11 1 3 name12 1 4 name121 3 5 name122 3 6 name13 1 7 name2 0 8 name21 7 Is there any select statement, which give me the path e.g to the id 5? I want to provide the 5 and will get the following: id5 (pid3) - id3 (pid1) - id1 (pid0) name122 - name12 - name1 Do you have any idea? Oliver -- [EMAIL PROTECTED] * [EMAIL PROTECTED] * [EMAIL PROTECTED] * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select-Problem
so you want to list all of them starting with the highest level and going up to the lowest level? On Mon, 29 Jan 2001, Oliver Joa wrote: Hi, I have a recursive Problem. I have a Table with columns "id", "name" and "pid". E.g: name1 name11 name12 name121 name122 name13 name2 name21 In the Table it would look like: id, name, pid 1 name1 0 2 name11 1 3 name12 1 4 name121 3 5 name122 3 6 name13 1 7 name2 0 8 name21 7 Is there any select statement, which give me the path e.g to the id 5? I want to provide the 5 and will get the following: id5 (pid3) - id3 (pid1) - id1 (pid0) name122 - name12 - name1 Do you have any idea? Oliver -- -Spinlock EmpireQuest Creator http://www.empirequest.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Insert-Select problem
I am performing a query in two tables and output it into a temporary table for further sorting. The problem is that in one of the tables (TableB), I have an additional id that I want to be added to the temptable. This id is not present in TableA. I get this error message when I run the query for TableA (see below): ERROR 1136: Column count doesn't match value count at row 1 I understand the problem (the number of columns in the query doesnt match the ones in the table), but not how to solve it. Any ideas? create temporary table temptable date VARCHAR(40) NOT NULL, id INT(20) NOT NULL, heading VARCHAR(255) NOT NULL, body text NOT NULL, author VARCHAR(255), domain VARCHAR(40), bid INT(20) ); TableA: INSERT INTO temptable SELECT date,id,rubrik,body,auth,domain,(What-do-add-here) FROM tablea ORDER BY aid DESC; TableB: INSERT INTO temptable SELECT date,id,rubrik,body,auth,domain,bid FROM tableb ORDER BY bid DESC; Thanks // Tobias - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
