Re: Having trouble with SQL query
Hi Nitin Thanks - I tried that and got 0 rows... I have spent more time on describing my problem -- see below hopefully this will make the issue more clear... Rich I have a MySQL database with a menu table and a product table. - The products are linked to the menus in a one-to-many relationship i.e. each product can be linked to more than one menu - The menus are nested in a parent child relationship - Some menus may contain no products The desire is that when a user clicks on a menu entry then all products linked to that menu - there may be none - will get displayed as well as all products linked to any child menus of the menu clicked on ... So say we have a menu like this:- Motor cycles - Sports bikes - Italian - Ducati Motor cycles - Sports bikes - Italian - Moto Guzzi Motor cycles - Sports bikes - British - Triumph Motor cycles - Tourers - British - Triumph Motor cycles - Tourers - American - Harley-Davidson . etc etc Clicking on 'Sports bikes' will show all products linked to 'Sports bikes' itself as well as all products linked to ALL menus below 'Sports bikes', clicking on 'Harley-Davidson' will just show products for that entry only. Below are 'describe table' for the 2 main tables in question NB there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant here:- CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 For the sake of this question I will simplify it and say there is only 2 levels of nesting i.e. root level and 1 level below that... this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway when I run the above query it returns far too many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Having trouble with SQL query
Hello Rich, On 8/27/2012 12:19 PM, rich gray wrote: Hi Nitin Thanks - I tried that and got 0 rows... I have spent more time on describing my problem -- see below hopefully this will make the issue more clear... Rich ... snip ... There are many resources out there that can tell you how to build this type of data structure. However, my favorite and the one I think is most accessible is this: http://www.sitepoint.com/hierarchical-data-database/ As you can see, his menu also has branches (fruit) and leaves (cherry, banana) just as your equipment menu does. I think this will be an excellent starting point for you to use to build the menu tree. From there, it should be easy to extend this to link your leaf nodes to any information records you may want. Let us know if we can give any additional insights or suggestions. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Having trouble with SQL query
I have a MySQL database with a menu table and a product table linked to the menus *(each product can be linked to more than menu row)* and the menus are nested. The query is that when a user clicks on a menu entry then all products linked to that entry *(there may be none)* will get displayed as well as all products linked to child menus... below are describe tables for the 2 main tables in question (there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant) CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM AUTO_INCREMENT=225 DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Anyway for the sake of this question lets say there is only 2 levels of nesting so a parent menu can only have children so no grandkids+ this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway the above query returns many many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Having trouble with SQL query
I'm more of an hit and try guy and do good only with a data set available. Still I think making a little change might do the trick. SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid AND m.name = 'name obtained from user's click') INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Hope that works. Regards, From: rich gray r...@richgray.com To: mysql@lists.mysql.com Sent: Monday, August 27, 2012 2:46 AM Subject: Having trouble with SQL query I have a MySQL database with a menu table and a product table linked to the menus *(each product can be linked to more than menu row)* and the menus are nested. The query is that when a user clicks on a menu entry then all products linked to that entry *(there may be none)* will get displayed as well as all products linked to child menus... below are describe tables for the 2 main tables in question (there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant) CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM AUTO_INCREMENT=225 DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Anyway for the sake of this question lets say there is only 2 levels of nesting so a parent menu can only have children so no grandkids+ this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway the above query returns many many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
Hi. On Friday 18 May 2012 18:21:07 Daevid Vincent wrote: Actually, I may have figured it out. Is there a better way to do this? I don't see why you need the dvds table when the dvd_id is in the scene table: SELECT a.dvd_id FROM scenes_list a, moviefiles b WHERE a.scene_id = b.scene_id AND b.format_id = '13'; or am I misunderstanding something? Cheers, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
There are a bunch of other columns in all these tables. A quick reason is need the dvd.title too therefore the dvd table is needed. Another reason is that the query is generated programmatically based upon parameters passed to a method. But yes, I do she your point and maybe I can refactor some things in this special case. I haven't tried your query as I'm home and not at work right ATM, but I think you need a DISTINCT dvd_id right? Otherwise I'll get a bunch of rows all with the same dvd_id since multiple scene_ids will match. d -Original Message- From: Mark Kelly [mailto:my...@wastedtimes.net] Sent: Saturday, May 19, 2012 3:34 PM To: mysql@lists.mysql.com Subject: Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format Hi. On Friday 18 May 2012 18:21:07 Daevid Vincent wrote: Actually, I may have figured it out. Is there a better way to do this? I don't see why you need the dvds table when the dvd_id is in the scene table: SELECT a.dvd_id FROM scenes_list a, moviefiles b WHERE a.scene_id = b.scene_id AND b.format_id = '13'; or am I misunderstanding something? Cheers, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
I would work from the inside out. What you're doing is grouping scenes by DVD and throwing away the ones that have no scenes. If you start with DVDs and do a subquery for each row, you'll process DVDs without scenes and then filter them out. If you start with a subquery that's grouped by DVD ID, alias it with an AS clause, and then join from that into the other tables, you can avoid that. It requires a little backwards-thinking but it tends to work well in a lot of cases. It would look something like this. Here's the query against the scenes: select dvd_id, count(*) as cnt from scenes_list group by dvd_id having count(*) 0; Now you can put that into a subquery and join to it: select ... from ( copy/paste the above ) as s_sl inner join dvds using (dvd_id) rest of query; I'm taking shortcuts because you said there is more to this query than you've shown us, so I won't spend the time to make it a complete query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
I have a table of DVDs, another of scenes and a last one of encoding formats/files... I want to find in one query all the dvd_id that have 0 scene_id that's encoded in format_id = 13. In other words all DVDs that are format_id = 13 despite not having a direct link. CREATE TABLE `dvds` ( `dvd_id` smallint(6) unsigned NOT NULL auto_increment, `dvd_title` varchar(64) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`dvd_id`), ) CREATE TABLE `scenes_list` ( `scene_id` int(11) NOT NULL auto_increment, `dvd_id` int(11) NOT NULL default '0', `description` text NOT NULL, PRIMARY KEY (`scene_id`), ) CREATE TABLE `moviefiles` ( `scene_id` int(11) NOT NULL default '0', `format_id` int(3) NOT NULL default '0', `filename` varchar(255), `volume` smallint(6) NOT NULL default '0', PRIMARY KEY (`scene_id`,`format_id`), ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
-Original Message- Sent: Friday, May 18, 2012 5:34 PM I have a table of DVDs, another of scenes and a last one of encoding formats/files... I want to find in one query all the dvd_id that have 0 scene_id that's encoded in format_id = 13. In other words all DVDs that are format_id = 13 despite not having a direct link. CREATE TABLE `dvds` ( `dvd_id` smallint(6) unsigned NOT NULL auto_increment, `dvd_title` varchar(64) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`dvd_id`), ) CREATE TABLE `scenes_list` ( `scene_id` int(11) NOT NULL auto_increment, `dvd_id` int(11) NOT NULL default '0', `description` text NOT NULL, PRIMARY KEY (`scene_id`), ) CREATE TABLE `moviefiles` ( `scene_id` int(11) NOT NULL default '0', `format_id` int(3) NOT NULL default '0', `filename` varchar(255), `volume` smallint(6) NOT NULL default '0', PRIMARY KEY (`scene_id`,`format_id`), ) Actually, I may have figured it out. Is there a better way to do this? SELECT DISTINCT d.`dvd_id` AS `id`, (SELECT COUNT(s_sl.scene_id) AS s_tally FROM scenes_list AS s_sl JOIN moviefiles AS s_mf USING (scene_id) WHERE s_sl.dvd_id = d.`dvd_id` AND s_mf.format_id = 13) AS s_tally FROM `dvds` AS d WHEREd.`date_release` = '2012-05-18' HAVING s_tally 0 ORDER BY d.`date_release` DESC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
2012/04/11 17:51 -0500, Peter Brawley
select b.peopleID, concat('(',p.fname,,')'), b.stateID, concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;
Since the names are the same in the tables, it works to use USING, too, and
you are relieved of the burden of an alias:
from bridge
join people USING(peopleID)
join state USING(stateID)
If the fields peopleId and stateID are the only field names in common,
NATURAL JOIN also works.
from bridge
NATURAL join people
NATURAL join state
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
My initial goal was to write a very convenient php function that display a
table view based on arguments that are super simple to write - without
requiring the developer to type-in ( or know ) the ins and outs of joins,
natural joins etc.
Something like this
function showtable($dbh,$table,$fields){
//get the $fields argument parse it out to come up
//with one of the SQL statements you guys are discussing.
SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}
The magic would be happening in the $fields argument.
The example in my original question was like this.
I'm repeating it for convenience purposes.
quote
I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs
peopleID___stateID
1__1
2__4
3__5
people table is like this;
___peopleID_FName_
___1joe
___2bob___
___3charlie_
and finally the state table goes like this;
___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__
What's the most straightforward way to achieve the following view with one
SQL statement?
peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
/quote
if the final table ( PeopleAndStates ) view I want were to be as follows;
peopleID__stateID_
1_1___
2_4___
3_5___
Then I would have called the function like this;
showtable($dbh,$myTable,peopleID,stateID)
But if I want to get, the following view instead;
peopleID__stateID_
1_(joe)___1___
2_(bob)___4___
3_(charlie)___5___
I would like to be able to call my function as follows;
showtable($dbh,$PeopleAndStates,peopleID(PeopleTable.PeopleID
FName),stateID)
To mean the following;
When you are outputting the peopleID, provide the corresponding Fname
field from the PeopleTable where peopleID there is equal to the peopleID
you are outputting.
What I was seeking from you guys was to find out to most simplistic SQL
statement so that when I parse the area with (PeopleTable.PeopleID
FName), I can extract the pieces and place it in the final SQL.
I'm not sure if you all get the idea of how such a function make debugging
super easy.
Once you write the parser, you can deploy it over many different cases such
as
ShowRecord($dbh,$table,$fields,where peopleID5,limit 100) and so on.
So, the simpler the SQL, the easier the transition from the starting slate
which is really no different than
SELECT peopleID(PeopleTable.PeopleID FName),stateID from PeopleAndStates
2012/4/12 Halász Sándor h...@tbbs.net
2012/04/11 17:51 -0500, Peter Brawley
select b.peopleID, concat('(',p.fname,,')'), b.stateID,
concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;
Since the names are the same in the tables, it works to use USING, too,
and you are relieved of the burden of an alias:
from bridge
join people USING(peopleID)
join state USING(stateID)
If the fields peopleId and stateID are the only field names in common,
NATURAL JOIN also works.
from bridge
NATURAL join people
NATURAL join state
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
; 2012/04/12 11:56 -0700, Haluk Karamete
My initial goal was to write a very convenient php function that display a
table view based on arguments that are super simple to write - without
requiring the developer to type-in ( or know ) the ins and outs of joins,
natural joins etc.
Something like this
function showtable($dbh,$table,$fields){
//get the $fields argument parse it out to come up
//with one of the SQL statements you guys are discussing.
SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}
The magic would be happening in the $fields argument.
What's the most straightforward way to achieve the following view with one SQL
statement?
peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
/quote
if the final table ( PeopleAndStates ) view I want were to be as follows;
peopleID__stateID_
1_1___
2_4___
3_5___
Then I would have called the function like this;
showtable($dbh,$myTable,peopleID,stateID)
But if I want to get, the following view instead;
peopleID__stateID_
1_(joe)___1___
2_(bob)___4___
3_(charlie)___5___
I would like to be able to call my function as follows;
showtable($dbh,$PeopleAndStates,peopleID(PeopleTable.PeopleID
FName),stateID)
To mean the following;
When you are outputting the peopleID, provide the corresponding Fname field
from the PeopleTable where peopleID there is equal to the peopleID you are
outputting.
What I was seeking from you guys was to find out to most simplistic SQL
statement so that when I parse the area with (PeopleTable.PeopleID FName), I
can extract the pieces and place it in the final SQL.
I'm not sure if you all get the idea of how such a function make debugging
super easy.
Once you write the parser, you can deploy it over many different cases such as
ShowRecord($dbh,$table,$fields,where peopleID5,limit 100) and so on.
So, the simpler the SQL, the easier the transition from the starting slate
which is really no different than
SELECT peopleID(PeopleTable.PeopleID FName),stateID from PeopleAndStates
(note that in MySQL '' is only right-shift.)
I fear that for this function in the end you will need
information_schema.COLUMNS.
Peter Brawley already gave you a good answer for one of your examples (but I
thus would write it):
select concat(peopleID, ' (',fname,')') AS peopleID, concat(stateID, '
(',state,')') AS stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)
The middle example:
select peopleID, stateID from PeopleAndStates
The last:
select concat(peopleID, ' (',fname,')') AS peopleID, stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)
I have assumed that you mean to join only on same-named fields with equality;
if not, JOIN ... ON ... is needed. In any case, you have to keep track of it,
whether in a result field the table name, too, is needed. If you use USING, for
that field leave the table name out.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
On 4/11/2012 1:30 PM, Haluk Karamete wrote:
I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs
peopleID___stateID
1__1
2__4
3__5
people table is like this;
___peopleID_FName_
___1joe
___2bob___
___3charlie_
and finally the state table goes like this;
___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__
What's the most straightforward way to achieve the following view with one
SQL statement?
peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
select b.peopleID, concat('(',p.fname,,')'), b.stateID,
concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;
PB
-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
; 2012/04/11 11:30 -0700, Haluk Karamete I've got this relational mySQL table that ties peopleIDs from the people table to the states IDs peopleID___stateID 1__1 2__4 3__5 people table is like this; ___peopleID_FName_ ___1joe ___2bob___ ___3charlie_ and finally the state table goes like this; ___stateID_State___ ___1___california ___2___new york ___3___washington__ ___4___texas___ ___5___florida__ What's the most straightforward way to achieve the following view with one SQL statement? peopleID__stateID_ 1_(joe)___1__(california)_ 2_(bob)___4__(texas)__ 3_(charlie)___5__(florida) Look at Stephen Tu s original post under the subject forcing mysql to use batched key access (BKA) optimization for joins. That his query solves a problem very much like yours--but use explicit JOINing, not implicit. He also uses an unhappy style of making every field name in the database unique. Joining is easier if the fields to be joined on have same names. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: sql query advise
Have a look at GROUP BY and aggregate functions:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
- Original Message -
From: Norman Khine nor...@khine.net
To: mysql@lists.mysql.com
Sent: Thursday, 23 June, 2011 4:05:35 PM
Subject: sql query advise
hello,
i have this SQL code in a python programme but i wanted to change the
SQL so that it returns totals for each date. or do i have to make a
loop for each date range so that i get the following results which
then i would like to plot on a graph.
$ python daily_totals.py
(2L, Decimal('173.958344'), Decimal('159.966349')) 2011-06-23
(6L, Decimal('623.858200'), Decimal('581.882214')) 2011-06-22
...
here is the code: http://pastie.org/2111226
thanks
norman
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
sql query advise
hello,
i have this SQL code in a python programme but i wanted to change the
SQL so that it returns totals for each date. or do i have to make a
loop for each date range so that i get the following results which
then i would like to plot on a graph.
$ python daily_totals.py
(2L, Decimal('173.958344'), Decimal('159.966349')) 2011-06-23
(6L, Decimal('623.858200'), Decimal('581.882214')) 2011-06-22
...
here is the code: http://pastie.org/2111226
thanks
norman
--
˙ʇı ɹoɟ ƃuıʎɐd ǝɹ,noʎ ʍou puɐ ǝɔıoɥɔ ɐ ʞooʇ ı ʇɐɥʇ sı 'ʇlnɔıɟɟıp sı ʇɐɥʍ
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )
Re: Complicated SQL Query
On 26/08/2010 4:31 a, Jacob Steinberger wrote: I found an answer without having to worry about complicated SQL statements - it's more about managing the tables than the SQL. Jacob I usually just turn on binary logging, that way I have a record of anything that changes in the entire database and can re-construct or roll back (by reconstructing from beginning to the time I want) from the binary log. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Complicated SQL Query
Inventions come from need. Congratulations and thank you for sharing your science, Its very interesting. May be useful for other uses. Claudio On Aug 26, 2010 9:11 AM, Jangita jang...@jangita.com wrote: On 26/08/2010 4:31 a, Jacob Steinberger wrote: I found an answer without having to worry about complicated SQL statements - it's more about managing the tables than the SQL. Jacob I usually just turn on binary logging, that way I have a record of anything that changes in the entire database and can re-construct or roll back (by reconstructing from beginning to the time I want) from the binary log. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
RE: Complicated SQL Query
-Original Message- From: Jacob Steinberger [mailto:trefal...@realitybytes.net] Sent: Wednesday, August 25, 2010 8:36 PM To: mysql@lists.mysql.com Subject: Complicated SQL Query I have a requirement to keep track of a set of data, and all changes that might occur. In order to do this, for each field of the data set, I've created a table that keeps track of the version, the value, the time the change was made, and a linking number that links all the different tables back to a single record. I'm assuming, hoping, and believe this is a very common setup. [JS] That's a lot of tables. Do you need separate version tracking for each individual field? Or would it be sufficient to have version tracking for each row, with a list of fields modified and their before values? If the latter, then you can get by with a lot less complexity; if the former, then I think your design might be the only way to go. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.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: Complicated SQL Query
Quoting Jerry Schwartz je...@gii.co.jp: -Original Message- From: Jacob Steinberger [mailto:trefal...@realitybytes.net] I have a requirement to keep track of a set of data, and all changes that might occur. In order to do this, for each field of the data set, I've created a table that keeps track of the version, the value, the time the change was made, and a linking number that links all the different tables back to a single record. I'm assuming, hoping, and believe this is a very common setup. [JS] That's a lot of tables. Do you need separate version tracking for each individual field? Or would it be sufficient to have version tracking for each row, with a list of fields modified and their before values? If the latter, then you can get by with a lot less complexity; if the former, then I think your design might be the only way to go. Separate version tracking for each individual field. This is due to all fields have a possibility of being edited/changed, but more than likely only a few fields will be regularly updated. The end idea that was given to me, which is quite easy, is to maintain two methods. Use the multi-table method to track all the historical changes, then use a single table with all the columns to base searches / processing off of. When needing to do an update, the only difference is you update both locations instead of relying on a weird JOIN or nested-sub-select view. Cheers, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Complicated SQL Query
I have a requirement to keep track of a set of data, and all changes that might occur. In order to do this, for each field of the data set, I've created a table that keeps track of the version, the value, the time the change was made, and a linking number that links all the different tables back to a single record. I'm assuming, hoping, and believe this is a very common setup. What I'm having trouble with is queries that aren't nested sub-selects, or joins that won't show NULL data. For example ... select rsi.value, rsi.record_id, ssn.value as serviceseqnum, esn.value as eventseqnum from record_set_id as rsi LEFT JOIN serviceseqnum as ssn ON rsi.record_id = ssn.record_id LEFT JOIN eventseqnum as esn ON ssn.record_id = esn.record_id Will join the tables, but doesn't take the version information into consideration. If I add a where to include the maximum version, to get the most recent value, it won't show anything if one of the values happens to be NULL. Using sub-selects generally causes long query time ... select rsi.value, rsi.record_id ( select value from serviceseqnum where record_id = rsi.record and version = ( select max(version) from serviceseqnum where record_id = rsi.record_id ) ) from record_set_id ) from record_set_id as rsi ... especially when trying to get a dozen values strung together so they appear as one record. Is there a better way to handle these queries that I'm just not thinking of? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Complicated SQL Query
I found an answer without having to worry about complicated SQL statements - it's more about managing the tables than the SQL. Jacob Quoting Jacob Steinberger trefal...@realitybytes.net: I have a requirement to keep track of a set of data, and all changes that might occur. In order to do this, for each field of the data set, I've created a table that keeps track of the version, the value, the time the change was made, and a linking number that links all the different tables back to a single record. I'm assuming, hoping, and believe this is a very common setup. What I'm having trouble with is queries that aren't nested sub-selects, or joins that won't show NULL data. For example ... select rsi.value, rsi.record_id, ssn.value as serviceseqnum, esn.value as eventseqnum from record_set_id as rsi LEFT JOIN serviceseqnum as ssn ON rsi.record_id = ssn.record_id LEFT JOIN eventseqnum as esn ON ssn.record_id = esn.record_id Will join the tables, but doesn't take the version information into consideration. If I add a where to include the maximum version, to get the most recent value, it won't show anything if one of the values happens to be NULL. Using sub-selects generally causes long query time ... select rsi.value, rsi.record_id ( select value from serviceseqnum where record_id = rsi.record and version = ( select max(version) from serviceseqnum where record_id = rsi.record_id ) ) from record_set_id ) from record_set_id as rsi ... especially when trying to get a dozen values strung together so they appear as one record. Is there a better way to handle these queries that I'm just not thinking of? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=trefal...@realitybytes.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: concatenate sql query with group by and having
With some databases such as MySQL, subqueries have to be explicitly named. For example select * from (select * from (select * from table) sub1) sub2; If not, you will see an error like: ERROR 1248 (42000): Every derived table must have its own alias If I understand your problem correctly, you are looking to limit your result set to only those records that have symbols with a single unique combination of chrom, and strand. If that's correct, something like the query below might work: select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat where geneName in -- returns all geneNames (symbols) with one unique combination of chrom and strand (select geneName from -- returns all unique combinations of symbol, chrom, and strand (select distinct geneName, chrom, strand from refFlat) sub1 group by geneName having count(*) = 1) group by refSeq having count(*) = 1; Date: Wed, 28 Jul 2010 11:10:32 -0500 Subject: concatenate sql query with group by and having From: pengyu...@gmail.com To: mysql@lists.mysql.com mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A I start mysql with the above command. Then I want to select the rows from the result of the following query, provided that for any rows that have the same symbol, chrom and strand should be the same (basically, discard the rows that have the same symbols but different chrom and strand). Could anybody show me how to do it? select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1; I think that something like SELECT name FROM (SELECT name, type_id FROM (SELECT * FROM foods)); works for sqlite3 (in terms of syntax). But the following do not work for mysql. Is this a difference between mysql and sqlite3? (I'm always confused by the difference between different variants of SQL) select * from (select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1); -- Regards, Peng -- 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
concatenate sql query with group by and having
mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A I start mysql with the above command. Then I want to select the rows from the result of the following query, provided that for any rows that have the same symbol, chrom and strand should be the same (basically, discard the rows that have the same symbols but different chrom and strand). Could anybody show me how to do it? select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1; I think that something like SELECT name FROM (SELECT name, type_id FROM (SELECT * FROM foods)); works for sqlite3 (in terms of syntax). But the following do not work for mysql. Is this a difference between mysql and sqlite3? (I'm always confused by the difference between different variants of SQL) select * from (select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1); -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
sql query advise
hello,
i have to write a query which has to pull data from a remote mysql
server, modify the table scheme, format some of the fields and then
populate the new database.
i am using MySQLdb which is a python interface to mysql db.
how would i write a query to do this update from from a single
statement that uses tables from both databases?
in essence how to merge these two lines into one statement:
select_promoCode_records = SELECT oppc_id, limitedDate FROM
db1.partner_promoCode
update_promoCode_record = UPDATE db2.partner_promoCode SET
limitedDate =%s WHERE oppc_id =%s
here is a simplified version of what i have so far.
[code]
#!/usr/local/bin/python2.6
# -*- coding: utf-8 -*-
#
import MySQLdb
# connect to the MySQL server and select the databases
dbhost = 'localhost'
dbuser = 'user'
dbpasswd = 'password'
try:
# connect to db
origin = MySQLdb.connect (host = dbhost,
user = dbuser,
passwd =
dbpasswd,
)
except MySQLdb.Error, e:
print Error %s % e
sys.exit (1)
select_promoCode_records = SELECT oppc_id, limitedDate FROM
db1.partner_promoCode
update_promoCode_record = UPDATE db2.partner_promoCode SET
limitedDate =%s WHERE oppc_id =%s
org = origin.cursor()
org.execute(select_promoCode_records)
results = org.fetchall()
try:
for row in results:
oppc_id, date = row
org.execute(update_promoCode_record, (int(date), int(oppc_id)))
source.commit()
except:
print Error: enable to put data
# bye!
origin.close()
source.close()
[/code]
thanks
--
¿noʎ uɐɔ uʍop ǝpısdn ǝʇıɹʍ uɐɔ ı - % .join( [
{'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in
,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sql query advise
hi martin,
On Fri, Apr 23, 2010 at 9:50 PM, Martin Gainty mgai...@hotmail.com wrote:
Norm-
I would strongly suggest locking the table before updating..a SELECT for
UPDATE would accomplish that objective:
thanks for the reply and the advise on locking the table
SELECT oppc_id, limitedDate FROM db1.partner_promoCode_record FOR UPDATE;
UPDATE db2.partner_promoCode SET limitedDate =%s WHERE oppc_id =%s
so in essence one can chain sql statements by using the ';' as a separator.
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.
Date: Fri, 23 Apr 2010 20:28:32 +0200
Subject: sql query advise
From: nor...@khine.net
To: mysql@lists.mysql.com
hello,
i have to write a query which has to pull data from a remote mysql
server, modify the table scheme, format some of the fields and then
populate the new database.
i am using MySQLdb which is a python interface to mysql db.
how would i write a query to do this update from from a single
statement that uses tables from both databases?
in essence how to merge these two lines into one statement:
select_promoCode_records = SELECT oppc_id, limitedDate FROM
db1.partner_promoCode
update_promoCode_record = UPDATE db2.partner_promoCode SET
limitedDate =%s WHERE oppc_id =%s
here is a simplified version of what i have so far.
[code]
#!/usr/local/bin/python2.6
# -*- coding: utf-8 -*-
#
import MySQLdb
# connect to the MySQL server and select the databases
dbhost = 'localhost'
dbuser = 'user'
dbpasswd = 'password'
try:
# connect to db
origin = MySQLdb.connect (host = dbhost,
user = dbuser,
passwd = dbpasswd,
)
except MySQLdb.Error, e:
print Error %s % e
sys.exit (1)
select_promoCode_records = SELECT oppc_id, limitedDate FROM
db1.partner_promoCode
update_promoCode_record = UPDATE db2.partner_promoCode SET
limitedDate =%s WHERE oppc_id =%s
org = origin.cursor()
org.execute(select_promoCode_records)
results = org.fetchall()
try:
for row in results:
oppc_id, date = row
org.execute(update_promoCode_record, (int(date), int(oppc_id)))
source.commit()
except:
print Error: enable to put data
# bye!
origin.close()
source.close()
[/code]
thanks
--
¿noʎ uɐɔ uʍop ǝpısdn ǝʇıɹʍ uɐɔ ı - % .join( [
{'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in
,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with
Hotmail. Get busy.
--
¿noʎ uɐɔ uʍop ǝpısdn ǝʇıɹʍ uɐɔ ı - % .join( [
{'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in
,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SQL query for unique values.
Hi, I'm very new to SQL and databases. I need a query for the following: (I'm sure google would have found the answer, but I could not really frame the sentence for the task I'm looking for. Also, please let me know how do I search in google for such tasks - so that I can try it myself in future). Assume I have a table like: Sl No Fruit Grown in states 1 Apple KA 2 Orange AP 3 Banana TN 4 Jackfruit MH 5 Mango MP 6 Jackfruit MP 7 Banana AP 8 Mango KA 9 Banana TN 10 Apple MH 11 Jackfruit AP 12 Orange MH 13 Mango KA 14 Apple TN 15 Banana MP 16 Banana MH 17 Mango KA 18 Orange MP 19 Jackfruit AP 20 Apple TN From the above table, I want a SQL query which will list me the unique fruits and the states in which they are grown, like: Apple: KA, MH, TN Banana: TN, AP, MP, MH Jackfruit: MH,MP,AP Mango: MP, KA Orange: AP,MH,MP Thanks in advance for the help, Ravi. http://www.mindtree.com/email/disclaimer.html
Re: SQL query for unique values.
Hi, A simple group by function should work for this: Select Fruit,GrownInStates From tbl1 Group By Fruit; and if you want grownstates in comma separated format then you can use Group_Concat function Select Fruit, Group_Concat(GrownInStates, SEPARATOR ',') From tbl1 Group By Fruit; Hope this will work fine. -- Regards, Manasi Save Quoting Ravishankar BV. ravishankar...@mindtree.com: Hi, I'm very new to SQL and databases. I need a query for the following: (I'm sure google would have found the answer, but I could not really frame the sentence for the task I'm looking for. Also, please let me know how do I search in google for such tasks - so that I can try it myself in future). Assume I have a table like: Sl No Fruit Grown in states 1 Apple KA 2 Orange AP 3 Banana TN 4 Jackfruit MH 5 Mango MP 6 Jackfruit MP 7 Banana AP 8 Mango KA 9 Banana TN 10 Apple MH 11 Jackfruit AP 12 Orange MH 13 Mango KA 14 Apple TN 15 Banana MP 16 Banana MH 17 Mango KA 18 Orange MP 19 Jackfruit AP 20 Apple TN From the above table, I want a SQL query which will list me the unique fruits and the states in which they are grown, like: Apple: KA, MH, TN Banana: TN, AP, MP, MH Jackfruit: MH,MP,AP Mango: MP, KA Orange: AP,MH,MP Thanks in advance for the help, Ravi. http://www.mindtree.com/email/disclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Force index command in sql query
On 09/12/2009, at 5:10 PM, Jeetendra Ranjan wrote: Hi, After analysing slow query log i found that some queries are not using index and so i used the force index command in query and test it and now it starts using index properly.Accordingly i implemented the same query with force index in my application code and regeneratet the slow query log. Now i found that the same queries having force index clause are againg not using index and surprisingly its starting using index without any force index clause. Please suggest how it happened and should i continue with the force index command in that query or remove the force index clause from those queries ? One of the things to be aware of is that force index only forces the index if the optimizer chooses to use an index. That is, if the optimizer decides it is better to do a table scan or the join order changes so the index cannot be used, then it will not use it. It might be worth trying to do an EXPLAIN EXTENDED ... followed by SHOW WARNINGS to see how the optimizer has reorganized the query. Hope that helps. - Jesper Thanks Regards Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Force index command in sql query
Hi, After analysing slow query log i found that some queries are not using index and so i used the force index command in query and test it and now it starts using index properly.Accordingly i implemented the same query with force index in my application code and regeneratet the slow query log. Now i found that the same queries having force index clause are againg not using index and surprisingly its starting using index without any force index clause. Please suggest how it happened and should i continue with the force index command in that query or remove the force index clause from those queries ? Thanks Regards Jeetendra Ranjan
sql query question that puzzles me
Hi, This thing puzzles me for quite some time and I wasn't successful in finding a clear answer anywhere - I would be grateful for some help. Here is a db example: table_1 id some_field_01 [...] some_field_20 table_2 itemid (table_1_id) value Let's say that the table_2 is used to store some properties of the item in table_1, there can be many of them (let's say these are just integers values - not that important in this example). What I'd like to get is the item from table_1 and the item properties from table_2, I can do 2 things: 1: on table_1 SELECT * FROM table_1 WHERE id = SOME_ID then on table_2 SELECT value FROM table_1 WHERE itemid = SOME_ID.from.table_1 so I get one row from table_1 and many rows from table_2 2: on both tables: SELECT tb_1.*,tb_2.value AS property FROM table_1 AS tb_1, table_2 AS tb_2 WHERE tb_1.id = SOME_ID AND tb_1.id = tb_2.itemid so I get many rows with one query. The question is, which way is better if these requests may be executed a large number of times? I think 1 query is better than 2 but if the table_1 holds a lot of data - resending the whole thing every time (example 2) just to get the one integer value seems like a waste of resources. Or is there a better way to do it? Could someone enlighten me? Thanks! -- Regards, Lecho -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Basic SQL Query Help Needed
I have a basic invoice table with related line items table Goal :I'd like to get ALL the related line items - for ALL the 'open' invoices... -- this should get a list of open (unpaid) invoices $query_invoice = SELECT DISTINCT ID from invoices where status = 'open' - -- then I'd like to get ALL the line items - in ALL these 'open' invoices - so how do I write the next SQL statement : $query_items = ??? SELECT ID, NAME from lineitems where --xx??? xx-- ??? Thanks, c...@hosting4days.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: Basic SQL Query Help Needed
SELECT * FROM ORDER o INNER JOIN ORDER_LINE_ITEMS o_l ON (o.id=o_l.id) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. To: mysql@lists.mysql.com From: c...@hosting4days.com Subject: Basic SQL Query Help Needed Date: Tue, 25 Aug 2009 16:21:45 -0700 I have a basic invoice table with related line items table Goal :I'd like to get ALL the related line items - for ALL the 'open' invoices... -- this should get a list of open (unpaid) invoices $query_invoice = SELECT DISTINCT ID from invoices where status = 'open' - -- then I'd like to get ALL the line items - in ALL these 'open' invoices - so how do I write the next SQL statement : $query_items = ??? SELECT ID, NAME from lineitems where --xx??? xx-- ??? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ With Windows Live, you can organize, edit, and share your photos. http://www.windowslive.com/Desktop/PhotoGallery
REGEXP case insensitive SQL QUERY
How can I use REGEXP case insensitive SQL QUERY Ex: select * from table where a REGEXP 'abc' will match both 'abc' and 'ABC'
Create Table from Results of SQL Query
Hi, I have a SQL Query that has an inner join and it is taking too long to execute. I am thinking of speeding this up by dumping the result into a separate table – as I would be requiring the result of this query many times in the future. I am wondering if someone could show me how to create a table from the results of the SQL Query. I hope I have explained that correctly. I am ready to provide more explanation if you feel that is necessary. Thanks a lot to those of you who help. Thanks, O.O. Posta, news, sport, oroscopo: tutto in una sola pagina. Crea l#39;home page che piace a te! www.yahoo.it/latuapagina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Table from Results of SQL Query
In the last episode (Jul 04), O. Olson said: I have a SQL Query that has an inner join and it is taking too long to execute. I am thinking of speeding this up by dumping the result into a separate table - as I would be requiring the result of this query many times in the future. I am wondering if someone could show me how to create a table from the results of the SQL Query. CREATE TABLE table2 SELECT field1,field2 FROM table1 http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note that you may need to add appropriate indexes after this, to make your later queries on the temp table faster. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Table from Results of SQL Query
Thanks Dan for your quick response - it works now. I am new to SQL in general and MySQL in particular. O.O. --- Ven 4/7/08, Dan Nelson [EMAIL PROTECTED] ha scritto: Da: Dan Nelson [EMAIL PROTECTED] Oggetto: Re: Create Table from Results of SQL Query A: O. Olson [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Data: Venerdì 4 luglio 2008, 05:11 In the last episode (Jul 04), O. Olson said: I have a SQL Query that has an inner join and it is taking too long to execute. I am thinking of speeding this up by dumping the result into a separate table - as I would be requiring the result of this query many times in the future. I am wondering if someone could show me how to create a table from the results of the SQL Query. CREATE TABLE table2 SELECT field1,field2 FROM table1 http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note that you may need to add appropriate indexes after this, to make your later queries on the temp table faster. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Posta, news, sport, oroscopo: tutto in una sola pagina. Crea l#39;home page che piace a te! www.yahoo.it/latuapagina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A SQL Query Question
userId long picture MeduimBlob datePosted DateTime A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this? Latest pic for user N: SELECT userID,MAX(dateposted) FROM tbl WHERE userID=N; Latest pics per user: SELECT t1.userID,t1.dateposted FROM tbl t1 LEFT JOIN tbl t2 ON t1.userID=t2.userID AND t1.datepostedt2.dateposted WHERE t2.userID IS NULL; PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A SQL Query Question
See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of a User Hello everyone, I have a table A: userId long picture MeduimBlob datePosted DateTime A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this? Thanks Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A SQL Query Question
See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of a User select userId, picture, MAX(datePosted) from A order by datePosted; In Response To: Hello everyone, I have a table A: userId long picture MeduimBlob datePosted DateTime A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this? Thanks Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
I just thought of something else... could the same be accomplished
using stored routines? I could find no way in MySQL to create stored
routines which could be used with the 'group by' queries though.
If this were possible, it should then be also possible to define a
'LAST' stored routine, or something which would output a given field
value based on whether some other field (say, numeric ID, or timestamp)
was the highest in its group.
This looks to be possible with external functions ('CREATE AGGREGATE
FUNCTION'), but this would require writing an external library to handle
the call, too. It would be strange it if were impossible to create an
aggregate stored procedure.
Does anyone know if it's possible to define stored procedures this way?
Rob Wultsch wrote:
On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko
[EMAIL PROTECTED] wrote:
Oooh, this looks evil. It seems like such a simple thing. I guess
creating max(log_date) as a field, and then joining on it, is a solution --
but my actual query (not the abridged version) is already half a page long.
I think at this point, unless someone else suggests a better
solution, this would be easier to do programatically -- skip the group
altogether, and instead simply order the rows, and grab the last one for
each username in code.
I guess another alternative would be to use a View for the UNIONized
query, but doesn't MySQL 'fake' views in 5.0 somehow?
I have used views to good results, however I have read not good things
about them. I would not be surprised if they worked well for this use.
I would also not be surprised if the merge storage engine was a better
option for you.
Possibly interesting:
http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/
--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
On Fri, Apr 11, 2008 at 4:01 PM, Victor Danilchenko [EMAIL PROTECTED] wrote: Oooh, this looks evil. It seems like such a simple thing. I guess creating max(log_date) as a field, and then joining on it, is a solution -- but my actual query (not the abridged version) is already half a page long. A derived table or a LEFT JOIN are your best bets, as shown here: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html In most cases, the derived table is best. It creates a temp table automatically, so it's similar to using a view. My experiments with actual views gave dismal performance, and the user variable trick described on Baron's blog is pretty hard to get right. I think at this point, unless someone else suggests a better solution, this would be easier to do programatically -- skip the group altogether, and instead simply order the rows, and grab the last one for each username in code. If you don't have that much data to worry about then this could be good, but it's often tricky to code correctly because of the state you have to keep track of. Also, use UNION ALL if you don't need MySQL to remove duplicate rows. It makes a big difference in performance. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query question for GROUP BY
Hi all,
I trying to run a query where, after doing a UNION on two different
SELECTs, I need to sort the result by username and log_date fields, and
then grab the last entry for each username ('last' as determined by the
ordering of the log_date field, which is a datetime).
GROUP BY seems like an obvious choice; 'GROUP BY username', to be
exact. However, this seems to produce not the last row's values, but
ones from a random row in the group.
I don't think the fact that I am doing this on a subquery is relevant,
but just in case, I am including this info.
Here is what the query looks like, abridged:
SELECT id,username,log_date,event_type
FROM (SELECT * FROM a
UNION
SELECT * from b) as h
GROUP BY username
Basically, what I need is the chronologically last event_type value for
each user. I can achieve something similar by doing SELECT
MAX(event_type) -- but I need the equivalent of SELECT LAST(event_type);
last row instead of max-field-value row.
I keep having a feeling that I am making this way more complicated than
it has to be, and that there's a very simple way to return only the last
row for each username; but i am at a loss as to how to do it.
--
Victor Danilchenko
Senior Software Engineer, AskOnline.net
[EMAIL PROTECTED] - 617-273-0119
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko [EMAIL PROTECTED] wrote: GROUP BY seems like an obvious choice; 'GROUP BY username', to be exact. However, this seems to produce not the last row's values, but ones from a random row in the group. Under most databases your query is erroneous. Take a look at http://lists.mysql.com/mysql/212084 . I don't think the fact that I am doing this on a subquery is relevant, but just in case, I am including this info. Here is what the query looks like, abridged: SELECT id,username,log_date,event_type FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username Read http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Your are probably going to end up with a fairly ugly query (mostly because of the union) with what you have a derived table which will join against a and b again. SELECT if(a2.id is NULL, b2.id, a2.id) ,a1.username ... FROM (SELECT username, MAX(log_date) as mlog_date FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username) AS a1 LEFT JOIN a AS a2 ON a1.mlog_date = a2.log_date AND username... LEFT JOIN b AS b2 ... Any one have a suggestion for how to do with in a way that is not ugly as heck? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
Oooh, this looks evil. It seems like such a simple thing. I guess creating max(log_date) as a field, and then joining on it, is a solution -- but my actual query (not the abridged version) is already half a page long. I think at this point, unless someone else suggests a better solution, this would be easier to do programatically -- skip the group altogether, and instead simply order the rows, and grab the last one for each username in code. I guess another alternative would be to use a View for the UNIONized query, but doesn't MySQL 'fake' views in 5.0 somehow? Rob Wultsch wrote: On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko [EMAIL PROTECTED] wrote: GROUP BY seems like an obvious choice; 'GROUP BY username', to be exact. However, this seems to produce not the last row's values, but ones from a random row in the group. Under most databases your query is erroneous. Take a look at http://lists.mysql.com/mysql/212084 . I don't think the fact that I am doing this on a subquery is relevant, but just in case, I am including this info. Here is what the query looks like, abridged: SELECT id,username,log_date,event_type FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username Read http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Your are probably going to end up with a fairly ugly query (mostly because of the union) with what you have a derived table which will join against a and b again. SELECT if(a2.id is NULL, b2.id, a2.id) ,a1.username ... FROM (SELECT username, MAX(log_date) as mlog_date FROM (SELECT * FROM a UNION SELECT * from b) as h GROUP BY username) AS a1 LEFT JOIN a AS a2 ON a1.mlog_date = a2.log_date AND username... LEFT JOIN b AS b2 ... Any one have a suggestion for how to do with in a way that is not ugly as heck? -- Victor Danilchenko Senior Software Engineer, AskOnline.net [EMAIL PROTECTED] - 617-273-0119 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query question for GROUP BY
On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko [EMAIL PROTECTED] wrote: Oooh, this looks evil. It seems like such a simple thing. I guess creating max(log_date) as a field, and then joining on it, is a solution -- but my actual query (not the abridged version) is already half a page long. I think at this point, unless someone else suggests a better solution, this would be easier to do programatically -- skip the group altogether, and instead simply order the rows, and grab the last one for each username in code. I guess another alternative would be to use a View for the UNIONized query, but doesn't MySQL 'fake' views in 5.0 somehow? I have used views to good results, however I have read not good things about them. I would not be surprised if they worked well for this use. I would also not be surprised if the merge storage engine was a better option for you. Possibly interesting: http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/ -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with SQL query construction
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the reference sequence:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | NULL |
| B|2 | 2 | 998 | NULL |
| C|4 | 1100 | 2000 | NULL |
| D|3 | 3050 | 4100 | NULL |
| E|5 | 2040 | 3000 | NULL |
| F|6 | 1102 | 2000 | NULL |
| G|7 | 1098 | 1998 | NULL |
| H|8 | 3048 | 4100 | NULL |
| I|9 | 3051 | 4102 | NULL |
+--+--+---+--+--+
A graphical representation of fragments mapped to the ref sequence:
ref
1 A--
2 B
3 D--
4 C--
5 E
6 F---
7 G---
8 H---
9 I---
Now, I want to group fragments in each overlapping position and sub-rank
them according to their rank in that position. The final table would
then look like:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | 1|
| B|2 | 2 | 998 | 2|
| C|4 | 1100 | 2000 | 1|
| D|3 | 3050 | 4100 | 1|
| E|5 | 2040 | 3000 | 1|
| F|6 | 1102 | 2000 | 2|
| G|7 | 1098 | 1998 | 3|
| H|8 | 3048 | 4100 | 2|
| I|9 | 3051 | 4102 | 3|
+--+--+---+--+--+
Is this possible to achieve using SQL queries alone (perhaps with GROUP
BY, nested SELECTs etc)?
I've managed to do this with a Perl-DBI script, but would much prefer to
do it completely with MySQL instead. The Perl code is below and below
that is the MySQL-dump of the test data set...
Many thanks in advance!
Marcus
while (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
IS NULL)}) {
@null_sub_ranks = @{$dbh-selectcol_arrayref(SELECT rank FROM test
WHERE sub_rank IS NULL AND NOT (start=(SELECT stop FROM test WHERE rank
= (SELECT min(rank) FROM test WHERE sub_rank IS NULL)) OR stop =
(SELECT start FROM test WHERE rank = (SELECT min(rank) FROM test WHERE
sub_rank IS NULL};
for ($rank=0; $rank scalar(@null_sub_ranks); $rank++ ) {
$sub_rank = $rank + 1;
$dbh-do(UPDATE test SET sub_rank=$sub_rank WHERE rank=
$null_sub_ranks[$rank]);
}
}
-- MySQL dump 10.10
--
-- Host: localhostDatabase: bxb
-- --
-- Server version 5.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` text,
`rank` int(11) default NULL,
`start` int(11) default NULL,
`stop` int(11) default NULL,
`sub_rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
/*!4 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES
('A',1,1,1000,NULL),('B',2,2,998,NULL),('C',4,1100,2000,NULL),('D',3,3050,4100,NULL),('E',5,2040,3000,NULL),('F',6,1102,2000,NULL),('G',7,1098,1998,NULL),('H',8,3048,4100,NULL),('I',9,3051,4102,NULL);
UNLOCK TABLES;
/*!4 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with SQL query construction
-Original Message-
From: Marcus Claesson [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 7:49 AM
To: mysql@lists.mysql.com
Subject: Help with SQL query construction
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the reference sequence:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | NULL |
| B|2 | 2 | 998 | NULL |
| C|4 | 1100 | 2000 | NULL |
| D|3 | 3050 | 4100 | NULL |
| E|5 | 2040 | 3000 | NULL |
| F|6 | 1102 | 2000 | NULL |
| G|7 | 1098 | 1998 | NULL |
| H|8 | 3048 | 4100 | NULL |
| I|9 | 3051 | 4102 | NULL |
+--+--+---+--+--+
A graphical representation of fragments mapped to the ref sequence:
ref
1 A--
2 B
3 D--
4 C--
5 E
6 F---
7 G---
8 H---
9 I---
Now, I want to group fragments in each overlapping position and sub-
rank
them according to their rank in that position. The final table would
then look like:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | 1|
| B|2 | 2 | 998 | 2|
| C|4 | 1100 | 2000 | 1|
| D|3 | 3050 | 4100 | 1|
| E|5 | 2040 | 3000 | 1|
| F|6 | 1102 | 2000 | 2|
| G|7 | 1098 | 1998 | 3|
| H|8 | 3048 | 4100 | 2|
| I|9 | 3051 | 4102 | 3|
+--+--+---+--+--+
Is this possible to achieve using SQL queries alone (perhaps with GROUP
BY, nested SELECTs etc)?
I've managed to do this with a Perl-DBI script, but would much prefer
to
do it completely with MySQL instead. The Perl code is below and below
that is the MySQL-dump of the test data set...
Many thanks in advance!
Marcus
while (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
IS NULL)}) {
@null_sub_ranks = @{$dbh-selectcol_arrayref(SELECT rank FROM test
WHERE sub_rank IS NULL AND NOT (start=(SELECT stop FROM test WHERE
rank
= (SELECT min(rank) FROM test WHERE sub_rank IS NULL)) OR stop =
(SELECT start FROM test WHERE rank = (SELECT min(rank) FROM test WHERE
sub_rank IS NULL};
for ($rank=0; $rank scalar(@null_sub_ranks); $rank++ ) {
$sub_rank = $rank + 1;
$dbh-do(UPDATE test SET sub_rank=$sub_rank WHERE rank=
$null_sub_ranks[$rank]);
}
}
-- MySQL dump 10.10
--
-- Host: localhostDatabase: bxb
-- --
-- Server version 5.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` text,
`rank` int(11) default NULL,
`start` int(11) default NULL,
`stop` int(11) default NULL,
`sub_rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
/*!4 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES
('A',1,1,1000,NULL),('B',2,2,998,NULL),('C',4,1100,2000,NULL),('D',3,30
50,4100,NULL),('E',5,2040,3000,NULL),('F',6,1102,2000,NULL),('G',7,1098
,1998,NULL),('H',8,3048,4100,NULL),('I',9,3051,4102,NULL);
UNLOCK TABLES;
/*!4 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
I'd say perl
Re: Help with SQL query construction
Marcus,
I've managed to do this with a Perl-DBI script, but
would much prefer to do it completely with MySQL instead.
You could port it to a recursive stored procedure. It would probably be
slower, and what would you have gained?
PB
Marcus Claesson wrote:
Hi!
I have a SQL query construction question that I hope someone can help
me with. After comparing a bunch of DNA fragments (see name below) with
a larger reference sequence I get a ordered list ranked according to
similarities, and with start/stop co-ordinates where the fragments map
to the reference sequence:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | NULL |
| B|2 | 2 | 998 | NULL |
| C|4 | 1100 | 2000 | NULL |
| D|3 | 3050 | 4100 | NULL |
| E|5 | 2040 | 3000 | NULL |
| F|6 | 1102 | 2000 | NULL |
| G|7 | 1098 | 1998 | NULL |
| H|8 | 3048 | 4100 | NULL |
| I|9 | 3051 | 4102 | NULL |
+--+--+---+--+--+
A graphical representation of fragments mapped to the ref sequence:
ref
1 A--
2 B
3 D--
4 C--
5 E
6 F---
7 G---
8 H---
9 I---
Now, I want to group fragments in each overlapping position and sub-rank
them according to their rank in that position. The final table would
then look like:
+--+--+---+--+--+
| name | rank | start | stop | sub_rank |
+--+--+---+--+--+
| A|1 | 1 | 1000 | 1|
| B|2 | 2 | 998 | 2|
| C|4 | 1100 | 2000 | 1|
| D|3 | 3050 | 4100 | 1|
| E|5 | 2040 | 3000 | 1|
| F|6 | 1102 | 2000 | 2|
| G|7 | 1098 | 1998 | 3|
| H|8 | 3048 | 4100 | 2|
| I|9 | 3051 | 4102 | 3|
+--+--+---+--+--+
Is this possible to achieve using SQL queries alone (perhaps with GROUP
BY, nested SELECTs etc)?
I've managed to do this with a Perl-DBI script, but would much prefer to
do it completely with MySQL instead. The Perl code is below and below
that is the MySQL-dump of the test data set...
Many thanks in advance!
Marcus
while (@{$dbh-selectcol_arrayref(SELECT rank FROM test WHERE sub_rank
IS NULL)}) {
@null_sub_ranks = @{$dbh-selectcol_arrayref(SELECT rank FROM test
WHERE sub_rank IS NULL AND NOT (start=(SELECT stop FROM test WHERE rank
= (SELECT min(rank) FROM test WHERE sub_rank IS NULL)) OR stop =
(SELECT start FROM test WHERE rank = (SELECT min(rank) FROM test WHERE
sub_rank IS NULL};
for ($rank=0; $rank scalar(@null_sub_ranks); $rank++ ) {
$sub_rank = $rank + 1;
$dbh-do(UPDATE test SET sub_rank=$sub_rank WHERE rank=
$null_sub_ranks[$rank]);
}
}
-- MySQL dump 10.10
--
-- Host: localhostDatabase: bxb
-- --
-- Server version 5.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`name` text,
`rank` int(11) default NULL,
`start` int(11) default NULL,
`stop` int(11) default NULL,
`sub_rank` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
/*!4 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES
('A',1,1,1000,NULL),('B',2,2,998,NULL),('C',4,1100,2000,NULL),('D',3,3050,4100,NULL),('E',5,2040,3000,NULL),('F',6,1102,2000,NULL),('G',7,1098,1998,NULL),('H',8,3048,4100,NULL),('I',9,3051,4102,NULL);
UNLOCK TABLES;
/*!4 ALTER TABLE `test` ENABLE KEYS */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query problem
Hi, I have built a site with Dreamweaver and I have a problem with a query. I am trying to pass a parameter from one page to another to drill down. Basically, I have one product entry that is in multiple categories on my website. So, say it's a dress, it is therefore related to category 1 which is 'Girls', but it is also more specifically related to category 2 which is 'Girls Dresses'. The way I have set this up is to have a column called MultiCategoryID that holds both the number 1 and 2 like this: /1/2/ When a user clicks a link to look at dresses, the parameter 2 is passed, but my query on the result page is wrong in some way because no records are displaying even though there is content to display. This is what I have so far: SELECT * FROM Products WHERE MultiCategoryID LIKE '/catdrill/' ORDER BY ProductID DESC The parameter settings are: Name: catdrill Type: Numeric Value: Request(MCID) MCID is the url parameter being passed Default value: 2 Only when I test the Default value with an exact match of /1/2/ does any product display. What have I done wrong here? Is there a way to get it to recognise that I want it to pick specific numbers between the slashes rather than the whole lot? I have tried to change the slashes to full stops just in case they are causing problems, but it's still giving the same problem. Thanks. Mat
Re: SQL query problem
Dear Mat, Your mail is not very clear. But I have a feeling that using '%' wildcard in the like operand should help you Regards, Ravi. On 11/14/07, Matthew Stuart [EMAIL PROTECTED] wrote: Hi, I have built a site with Dreamweaver and I have a problem with a query. I am trying to pass a parameter from one page to another to drill down. Basically, I have one product entry that is in multiple categories on my website. So, say it's a dress, it is therefore related to category 1 which is 'Girls', but it is also more specifically related to category 2 which is 'Girls Dresses'. The way I have set this up is to have a column called MultiCategoryID that holds both the number 1 and 2 like this: /1/2/ When a user clicks a link to look at dresses, the parameter 2 is passed, but my query on the result page is wrong in some way because no records are displaying even though there is content to display. This is what I have so far: SELECT * FROM Products WHERE MultiCategoryID LIKE '/catdrill/' ORDER BY ProductID DESC The parameter settings are: Name: catdrill Type: Numeric Value: Request(MCID) MCID is the url parameter being passed Default value: 2 Only when I test the Default value with an exact match of /1/2/ does any product display. What have I done wrong here? Is there a way to get it to recognise that I want it to pick specific numbers between the slashes rather than the whole lot? I have tried to change the slashes to full stops just in case they are causing problems, but it's still giving the same problem. Thanks. Mat
Need help with a sql query
Hello all: I have two tables: 1. MenuAccess: accessId fkMenuId fkGroupid View Execute 2. MenuOptions MenuId MenuName I would like to get all of the records in MenuOptions and any record in MenuAccess with a specific fkGroupid. For example: A. MenuAccess AccessId fkMenuID fkgroupid view execute 1 1 2 1 1 2 2 2 1 0 3 3 2 0 1 4 1 1 1 1 B. MenuOptions Menuid MenuName 1 Order Maintenance 2 Customer Maintenance 3 Reports IIf I run a query for fkgroupid = 1 I should get AccessId fkMenuID fkgroupid view execute MenuId MenuName 1 1 2 1 1 1 Order Maintenence Null Null Null Null Null 2 Customer Maintenence Null Null Null Null Null 3 Reports Any help will be appreciated. Thanks
Re: Need help with a sql query
Imran wrote: Hello all: ... I would like to get all of the records in MenuOptions and any record in MenuAccess with a specific fkGroupid. For example: ... IIf I run a query for fkgroupid = 1 I should get AccessId fkMenuID fkgroupid view execute MenuId MenuName 1 1 2 1 1 1 Order Maintenence Null Null Null Null Null 2 Customer Maintenence Null Null Null Null Null 3 Reports You'll need a UNION for: SELECT ma.AccessId, ma.fkMenuID, ma.fkgroupid, ma.view, ma.execute, mo.MenuId, mo.MenuName FROM MenuAccess AS ma LEFT JOIN MenuOptions AS mo ON mo.Menuid = ma.fkMenuID WHERE ma.fkgroupid = 1 UNION SELECT NULL, NULL, NULL, NULL, NULL, MenuID, MenuName FROM MenuOptions WHERE MenuID NOT IN (SELECT fkMenuID FROM MenuAccess WHERE fkgroupid = 1); (untested but it looks about right) Note that the second WHERE clause has a subquery where you have to repeat the given fkgroupid. Otherwise, you'll get 2 rows for Order Maintenence. I'm sure there's a much more elegant way to achieve this. As an aside, you really should pick one column-naming convention and stick to it. The case of the column names is all over the place (fkMenuID, fkgroupid, MenuID). This can only cause you headaches down the road when you're trying to remember if it was MenuID, menuId, menuid, or something altogether different. Personally, I use fk_menu_id. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to rewrite SQL query for new MySQL LEFT JOINS
I hope someone can clue me in what a syntax of query that produces the same would look like for MySQL 5.0.12 Old query meant to list most recent message from each thread, e.g. select * from messages left join messages as messages_ on messages.thread = messages_.thread and messages.created messages_.created where messages_.id is null It worked in 4.x but does not work in the new syntax. How should I rewrite it to get the same result? TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rewrite SQL query for new MySQL LEFT JOINS
It worked in 4.x but does not work in the new syntax. How should I rewrite it to get the same result? OK, that was a lie. It works in 5.x as well. I should learn to describe my problem more accurately as well as RTFM :-( The correct description of the query in question would have been: select * from messages, someothertable left join messages as messages_ on messages.thread = messages_.thread and messages.created messages_.created where messages_.id is null Since there was a comma (inner) join there, the left join applied only to 'someothertable' and not to message table as intended. As I learned, in 5.0.12+, parentheses matter! Duh! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting SQL Query - Total and Grouped Counts together?
Baron, Thanks very much for that simple but very effective solution. I altered your SQL slightly, the final SQL looks like this: SELECT domain, count(*) AS 'count all', SUM(IF(mime = 'text/html', 1, 0)) AS 'count text', SUM(IF(mime LIKE 'image/%', 1, 0)) AS 'count image' FROM tableA GROUP BY domain ORDER BY domain Thanks again, Imran Chaudhry -- Atom Database A Custom Database Designed for Your Business [EMAIL PROTECTED] http://atomdatabase.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
monitoring SQL query response times
Is there a way to monitor SQL query response times? Here's my current my.cnf relating to logs, but I only see response times in the slow query log, and even then, the response time is rounded to the nearest second. log=/logs/mysql.log log-error=/logs/mysql-error.log log-slow-queries=/logs/mysql-slow-queries.log long-query-time=0 log-slow-admin-statements What is the best way to monitor response times of SQL queries? Thanks in advance, Masao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: monitoring SQL query response times
Is there a way to monitor SQL query response times? Mysqlperformanceblog has patches for higher granularity query log: http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/ You could always just wrap the query calls in between some time registration of your own. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interesting SQL Query - Total and Grouped Counts together?
I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of which are defined as: CREATE TABLE tableA ( domain text, mime text ); Where domain is a domain, such as: google.com emeraldcity.oohisntitgreen.com teddybears.com An example of rows might be: google.com, text/html google.com, image/gif google.com, image/jpeg google.com, text/html teddybears.com, text/html teddybears.com, image/png google.com, text/html google.com, image/png ... mime is defined as having entries such as: text/html image/png image/jpg image/gif application/x-tar What I am after doing with this table is writing an SQL query which produces a count of all domains where the mime is equal to text/html and next to that, a total count for that domain where the mime type is image/* -- so for example, I might expect the returned resultset to look like: Domain domaincount Mimecountimages - google.com120 12 emeraldcity. 200 40 teddybears.com 11 2 So far, we've considered and tried using a same-table join, various group-by and rollup ideas, but am unable to come up with anything which will produce the above in ONE row for each domain. Any advice and assistance would be great! -- Atom Database A Custom Database Designed for Your Business [EMAIL PROTECTED] http://atomdatabase.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting SQL Query - Total and Grouped Counts together?
Hi, Imran Chaudhry wrote: I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of which are defined as: CREATE TABLE tableA ( domain text, mime text ); Where domain is a domain, such as: google.com emeraldcity.oohisntitgreen.com teddybears.com An example of rows might be: google.com, text/html google.com, image/gif google.com, image/jpeg google.com, text/html teddybears.com, text/html teddybears.com, image/png google.com, text/html google.com, image/png ... mime is defined as having entries such as: text/html image/png image/jpg image/gif application/x-tar What I am after doing with this table is writing an SQL query which produces a count of all domains where the mime is equal to text/html and next to that, a total count for that domain where the mime type is image/* -- so for example, I might expect the returned resultset to look like: Domain domaincount Mimecountimages - google.com120 12 emeraldcity. 200 40 teddybears.com 11 2 So far, we've considered and tried using a same-table join, various group-by and rollup ideas, but am unable to come up with anything which will produce the above in ONE row for each domain. Any advice and assistance would be great! Try IF or CASE expressions: SELECT foo, count(*), sum(case when foo = 'bar' then 1 else 0 end) FROM tbl GROUP BY foo Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Interesting SQL Query - Total and Grouped Counts together?
On Thu, April 26, 2007 18:38, Baron Schwartz wrote: Hi, Imran Chaudhry wrote: I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of Try IF or CASE expressions: SELECT foo, count(*), sum(case when foo = 'bar' then 1 else 0 end) FROM tbl GROUP BY foo Baron Cool, it's actually working :) I've been looking for something like that before. SELECT * FROM tablea t order by domain,mime; domainmime -- 'google.com', 'image/gif' 'google.com', 'image/jpeg' 'google.com', 'image/png' 'google.com', 'text/html' 'google.com', 'text/html' 'google.com', 'text/html' 'teddybears.com', 'image/png' 'teddybears.com', 'text/html' SELECT domain, count(*) `all`, sum(case when mime = 'text/html' then 1 else 0 end) html, sum(case when mime like 'image/%' then 1 else 0 end) image FROM tablea GROUP BY domain; domain all html image - 'google.com', 6, 3, 3 'teddybears.com', 2, 1, 1 -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql sql query size limit
The closest thing to an absolute limit on query size is the value of the configuration variable max_allowed_packet which defaults to 1 meg. - michael On 4/10/07, Anil D [EMAIL PROTECTED] wrote: Hi List, What is the practical limit on size of the sql query in mysql 4.1.x Anil -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql sql query size limit
Hi List, What is the practical limit on size of the sql query in mysql 4.1.x Anil
SQL Query Question
If I have a dataset as below: Name, Age, Word Bob, 13, bill Joe, 13, oxo Alex, 14, thing Jim, 14, blob Phil, 14, whatsit Ben, 15, doodah Rodney, 15, thingy I want to select the first block where the age is equal, i.e. return in the case of the above set, Bob and Joe, and with the same query if Bob and Joe didn't exist, return Alex, Jim, and Phil. In broken SQL, I want to SELECT * FROM `table` WHERE 'Age'='Age 1st Row';. I'm guessing this could be done with nested queries, but this is me attempting to optimise a script from 3 queries to 1, so the gains by using nested queries would be negligible. I'm guessing that this isn't possible, but I thought asking some people that know more than myself couldn't hurt. Any assistance is appreciated. Thanks, Adam Bishop
Re: SQL Query Question
In the last episode (Jan 22), Adam Bishop said: If I have a dataset as below: Name, Age, Word Bob, 13, bill Joe, 13, oxo Alex, 14, thing Jim, 14, blob Phil, 14, whatsit Ben, 15, doodah Rodney, 15, thingy I want to select the first block where the age is equal, i.e. return in the case of the above set, Bob and Joe, and with the same query if Bob and Joe didn't exist, return Alex, Jim, and Phil. In broken SQL, I want to SELECT * FROM `table` WHERE 'Age'='Age 1st Row';. How about: SELECT * FROM mytable WHERE Age=MIN(age); The smallest and largest values for a column are alawys available via MIN() and MAX(). If you had wanted the 2nd smallest, or the top 3, then you would have needed a subquery. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
Ah, that would work. Looks like I was making the problem too complex in my mind, thanks for your help. Adam Bishop -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: 22 January 2007 07:07 To: Adam Bishop Cc: mysql@lists.mysql.com Subject: Re: SQL Query Question In the last episode (Jan 22), Adam Bishop said: If I have a dataset as below: Name, Age, Word Bob, 13, bill Joe, 13, oxo Alex, 14, thing Jim, 14, blob Phil, 14, whatsit Ben, 15, doodah Rodney, 15, thingy I want to select the first block where the age is equal, i.e. return in the case of the above set, Bob and Joe, and with the same query if Bob and Joe didn't exist, return Alex, Jim, and Phil. In broken SQL, I want to SELECT * FROM `table` WHERE 'Age'='Age 1st Row';. How about: SELECT * FROM mytable WHERE Age=MIN(age); The smallest and largest values for a column are alawys available via MIN() and MAX(). If you had wanted the 2nd smallest, or the top 3, then you would have needed a subquery. -- Dan Nelson [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]
sql query
Hello, Lets suppose I have a table like this one id id_1 id_2 date_time 1 101 1000 2006-07-04 11:25:43 2 102 1001 2006-07-04 11:26:43 3 101 1005 2006-07-04 11:27:43 4 103 1000 2006-07-04 11:25:43 I want to find all id_2 that has same id_1 and time difference in records is no more than 5 minutes ... I hope I explain well In this case this is record 1 and record 3. How can I do this ? Thanks in advance for your help. Peter Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query
Hi Peter - Something like this ought to work: SELECT t1.id_2 FROM mytable t1, mytable t2 WHERE t1.id_1 = t2.id_1 AND t1.id != t2.id AND ABS( UNIX_TIMESTAMP(t1.date_time) - UNIX_TIMESTAMP(t2.date_time) ) = 300 Dan On 10/17/06, Peter [EMAIL PROTECTED] wrote: Hello, Lets suppose I have a table like this one id id_1 id_2 date_time 1 101 1000 2006-07-04 11:25:43 2 102 1001 2006-07-04 11:26:43 3 101 1005 2006-07-04 11:27:43 4 103 1000 2006-07-04 11:25:43 I want to find all id_2 that has same id_1 and time difference in records is no more than 5 minutes ... I hope I explain well In this case this is record 1 and record 3. How can I do this ? Thanks in advance for your help. Peter Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query
I want to find all id_2 that has same id_1 and time difference in records is no more than 5 minutes ... How about ... SELECT id_2 FROM tbl AS t1 JOIN tbl AS t2 ON t1.id_2 = t2.id_1 WHERE ABS(SEC_TO_TIME(t1.date_time)-SEC_TO_TIME(t2.date_time))=300; PB - Peter wrote: Hello, Lets suppose I have a table like this one id id_1 id_2 date_time 1 101 1000 2006-07-04 11:25:43 2 102 1001 2006-07-04 11:26:43 3 101 1005 2006-07-04 11:27:43 4 103 1000 2006-07-04 11:25:43 I want to find all id_2 that has same id_1 and time difference in records is no more than 5 minutes ... I hope I explain well In this case this is record 1 and record 3. How can I do this ? Thanks in advance for your help. Peter Send instant messages to your online friends http://uk.messenger.yahoo.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.4/478 - Release Date: 10/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query
Dan's is correct because the clause 'AND t1.id != t2.id' prevents checking a row against itself since the time diff with a row against itself is zero, which is less than 300 - Original Message - From: Dan Buettner [EMAIL PROTECTED] To: Peter [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 17, 2006 2:55:37 PM GMT-0500 US/Eastern Subject: Re: sql query Hi Peter - Something like this ought to work: SELECT t1.id_2 FROM mytable t1, mytable t2 WHERE t1.id_1 = t2.id_1 AND t1.id != t2.id AND ABS( UNIX_TIMESTAMP(t1.date_time) - UNIX_TIMESTAMP(t2.date_time) ) = 300 Dan On 10/17/06, Peter [EMAIL PROTECTED] wrote: Hello, Lets suppose I have a table like this one id id_1 id_2 date_time 1 101 1000 2006-07-04 11:25:43 2 102 1001 2006-07-04 11:26:43 3 101 1005 2006-07-04 11:27:43 4 103 1000 2006-07-04 11:25:43 I want to find all id_2 that has same id_1 and time difference in records is no more than 5 minutes ... I hope I explain well In this case this is record 1 and record 3. How can I do this ? Thanks in advance for your help. Peter Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query
Rolando Edwards wrote: Dan's is correct because Thank you ALL for your kind help !!! Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query help
Friends, I am developing a database for accounting software. I have one problem regarding calculation of balances on daily basis for all ledgers. I am using Access 2003 as frontend. While designing I found that maintaining of daily balances is impossible to client's requirements. But as the solution I to execute two SQL queries for 365 times to calculate Opening and closing balances. what i need is a hint/example to write a function/SQL statement to run these queries in single/minimum iterations. table format: LedgerID | Opening Credit | Opening Debit | Current Credit | Current Debit | Closing Credit | Closing Debit | Date Previous dates closing balance should be the opening for next date. Please suggest the answer. Thanks, CPK
The length of the sql query
Hello, Just curious to know whether Mysql 3.23 has any length constraint about where part, such as Query = [ select col1, col2, ... coln from table 1, table2, where constraint1 + constraint2 +constraintN ] Is there any length arrange for the Query str such as 500M, 1G, etc? Or the query can be as long as it is. Thanks a lot! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The length of the sql query
--On August 23, 2006 1:55:36 PM -0400 Emi Lu [EMAIL PROTECTED] wrote: Hello, Just curious to know whether Mysql 3.23 has any length constraint about where part, such as Query = [ select col1, col2, ... coln from table 1, table2, where constraint1 + constraint2 +constraintN ] Is there any length arrange for the Query str such as 500M, 1G, etc? Or the query can be as long as it is. Thanks a lot! All SQL in/out is limited by the max packet size configuration parameter, however if you're running SELECT's with a multi-megabyte where clause, you'll have other more practical issues. Seriously you probably don't want to do what you're doing. Also MySQL 3.23 is very ancient history now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds. -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The length of the sql query
Emi, it appears in 3.23 your limit is 16 MB. In 4.0 and later, it is 1 GB. http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html Dan On 8/23/06, Emi Lu [EMAIL PROTECTED] wrote: Hello, Just curious to know whether Mysql 3.23 has any length constraint about where part, such as Query = [ select col1, col2, ... coln from table 1, table2, where constraint1 + constraint2 +constraintN ] Is there any length arrange for the Query str such as 500M, 1G, etc? Or the query can be as long as it is. Thanks a lot! -- 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]
How can I use a value computed in my SQL query for further computations?
Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations?
To add to this, I will also want to be able to ORDER BY those three new columns (totalviews, totalclicks, grandtotal) as well.. I'm using mySQL 5 and innodb tables. I saw this page: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html But it says: Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list. For example, the following statement does not work as expected: mysql SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5; So that seems pretty useless for my needs. And I'm using this in combination with PHP and Ruby for what it's worth. It seems silly that I would have to use PHP's multisort() to sort/order data that I already have in a database, and it seems silly that I should have to use PHP to do basic math on the table when mySQL can do it probably faster. -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Monday, April 17, 2006 7:33 PM To: mysql@lists.mysql.com Subject: How can I use a value computed in my SQL query for further computations? Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; -- 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: How can I use a value computed in my SQL query for further computations?
[snip] Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; [/snip] Use variables; http://dev.mysql.com/doc/refman/5.1/en/set-statement.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations?
[snip] Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. [/snip] More http://dev.mysql.com/doc/refman/5.1/en/example-user-variables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I use a value computed in my SQL query for further computations? [solved]
Okay, well it turns out that this works exactly how I want/expect it to. The documentation was a bit confusing. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (@tv:=(views * ppview)) AS totalviews, (@tc:=(clicks * ppclick)) AS totalclicks, @tv + @tc AS grandtotal FROM advertisements a ORDER BY grandtotal desc; Thanks Jay for your ideas. Daevid. -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Monday, April 17, 2006 7:42 PM To: mysql@lists.mysql.com Subject: RE: How can I use a value computed in my SQL query for further computations? To add to this, I will also want to be able to ORDER BY those three new columns (totalviews, totalclicks, grandtotal) as well.. I'm using mySQL 5 and innodb tables. I saw this page: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html But it says: Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list. For example, the following statement does not work as expected: mysql SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5; So that seems pretty useless for my needs. And I'm using this in combination with PHP and Ruby for what it's worth. It seems silly that I would have to use PHP's multisort() to sort/order data that I already have in a database, and it seems silly that I should have to use PHP to do basic math on the table when mySQL can do it probably faster. -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Monday, April 17, 2006 7:33 PM To: mysql@lists.mysql.com Subject: How can I use a value computed in my SQL query for further computations? Here is a paired down version of a query I want to make. How can I get the grandtotal column? I know about the HAVING clause, but that's only going to be good for weeding out rows I don't want. I just want to do some basic math here. SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, totalviews + totalclicks AS grandtotal FROM advertisements a; There has got to be a better way than this (which would be a colossal waste of computing power to recalculate something that was just done!): SELECT a.*, DATE_FORMAT(a.created_on,'%m/%d/%y %h:%i:%s %p') AS created_on_format, DATE_FORMAT(a.timestamp,'%m/%d/%y %h:%i:%s %p') AS timestamp_format, (views * ppview) AS totalviews, (clicks * ppclick) AS totalclicks, ((views * ppview) + (clicks * ppclick)) AS grandtotal FROM advertisements a; -- 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]
Can I do a boolean search and get the row count in 1 SQL query?
Hi,
I have a web app where I am doing a boolean search. I only want
to return 10 results per page, but I'd also like to know how many total
rows match the search query. I'm currently performing this with 2
query statements:
1. (To get the actual rows via the search)
SELECT $product_column[title],
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT $lower_limit,10
2. (To get the total number of results)
SELECT COUNT(*) as num_results
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
The queries might be a bit off as I don't have the exact code in
front of me right now.
Is there a way I can combine this into 1 query?
Thanks,
Grant
-
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
Re: Can I do a boolean search and get the row count in 1 SQL query?
Hi,
You can do:
select sql_calc_found_rows [and here follow the rest of the select query];
And then you can get the number of all found rows, not only those got by
limit 10 as follows:
select found_rows();
Teddy
From: Grant Giddens [EMAIL PROTECTED]
Hi,
I have a web app where I am doing a boolean search. I only want
to return 10 results per page, but I'd also like to know how many total
rows match the search query. I'm currently performing this with 2
query statements:
1. (To get the actual rows via the search)
SELECT $product_column[title],
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT $lower_limit,10
2. (To get the total number of results)
SELECT COUNT(*) as num_results
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
The queries might be a bit off as I don't have the exact code in
front of me right now.
Is there a way I can combine this into 1 query?
Thanks,
Grant
-
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I go a boolean search and get the row count in 1 SQL query?
Hi,
I have a web app where I am doing a boolean search. I only want to return
10 results per page, but I'd also like to know how many total rows match the
search query. I'm currently performing this with 2 query statements:
1. (To get the actual rows via the search)
SELECT $product_column[title],
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT $lower_limit,10
2. (To get the total number of results)
SELECT COUNT(*) as num_results
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
The queries might be a bit off as I don't have the exact code in front of me
right now.
Is there a way I can combine this into 1 query?
Thanks,
Grant
-
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
Can this SQL query be done with MySql?
Hi! I have a MySQL table with perid (person id), CaseId (the latter two fields are varchar). The persons (perid) can be on more than one case. They can get married and change caseids, or they can come of age and get their own caseid. So a given perid can be associated with multiple caseids. I'd like (using just sql) to create a list of those people (perids) who have changed cases. So the list would show a perid and then a bunch of cases that they have been on. My second question is, in MySql can we query a field just created in the query? So can you do something like this? sele count(*) as cnt, lastname from tablename where cnt1 Here we are using a created field name in the query itself. Is this possible? Thanks heaps! -Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can this SQL query be done with MySql?
See remarks interspersed below Rhino - Original Message - From: WARVIN BARKER [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 14, 2005 10:44 AM Subject: Can this SQL query be done with MySql? Hi! I have a MySQL table with perid (person id), CaseId (the latter two fields are varchar). The persons (perid) can be on more than one case. They can get married and change caseids, or they can come of age and get their own caseid. So a given perid can be associated with multiple caseids. I'd like (using just sql) to create a list of those people (perids) who have changed cases. So the list would show a perid and then a bunch of cases that they have been on. What you're describing is called a many-to-many relationship by database designers. The way that many-to-many relationships are normally implemented are as follows. (I'm guessing you are dealing with some sort of welfare administration system so that will be what I show in my examples). Person Table (primary key = PerID) PerIDFirstNameLastNameBirthdate... (other columns about the person) P001 MarySmith 1960-01-01 P002 FredSmith 1980-01-01 P003 Elaine Jones 1982-01-01 Case Table (primary key = CaseID) CaseID... (other columns about the case) C001 C002 Person_Case Table (primary key = PerID *and* CaseID) PerIDCaseID... (other columns about this particular person and case) P001 C001 P001 C002 P002 C001 P003 C002 If I were in your situation, I'd probably store the start date and end date for the person's association with the case (and maybe the reasons for starting and ending the association) in the Person_Case table. For example: PerIDCaseIDStartDateStartReason EndDateEndReason P001 C001 1978-01-01 Quit school, no work - P002 C001 1980-01-01 Child born 1998-01-01 legally adult P002 C002 1998-01-01No work -- P003 C002 2000-01-01Married, no work available 2003-01-01 got job In this example, we have 4 rows in the Person_Case table: 1. Mary Smith went on welfare when she turned 18, quit school, and couldn't find work. She has never found work and remain on welfare now (the '-' is a common notation for 'null', meaning 'unknown or not applicable'). 2. In 1980, when she was 20, Mary had a son, Fred. He was associated with her case until he turned 18, then he was detached from the case because he was now too old to be on his mother's case. 3. Fred Smith is now 18 and gets his own case number. (Let's assume he can't find work either). 4. Fred Smith marries Elaine Jones in 2000. She is added to case C002 but finds a job in 2003, ending her association with the case. Now, when you want to know what cases a given person has been associated with, you simply query the Person_Case table. If you need to determine more information about the person or case, just join those tables to Person_Case. My second question is, in MySql can we query a field just created in the query? So can you do something like this? sele count(*) as cnt, lastname from tablename where cnt1 Here we are using a created field name in the query itself. Is this possible? Certainly! You wouldn't do it quite that way though; you are not allowed to have that formulate the query the way your example shows (at least not in DB2, the database I use most; I'm pretty sure the same rule applies to MySQL). To accomplish what you want, you would do something like this: select PerID, count(*) from Person_Case group by PerID having count(*) 1 Explanation: For each different person in the Person_Case table, determine the number of rows for that case, which is the number of cases that are (or have ever been) associated with; only display that person's ID if he/she is associated with more than one case. [If you only want to show cases that the person is currently associated with, add WHERE conditions to ensure that the current date is between the start date and end date for that association. Something like this: select PerID, count(*) from Person_Case where current_date = StartDate and current_date = EndDate group by PerID having count(*) 1] -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Rhino wrote: I'm glad the explanation helped. I figured that the solution alone wouldn't be very useful if it wasn't explained since it is not obvious to most people how correlated queries work :-) I really wasn't trying to center you out with my rant about MySQL version. It's been a long-running frustration for me; I've tried to urge people to give this information in their notes to the list but an awful lot of people never think to mention their version. Then, an already lengthy reply gets even longer as you try to explain: If you have Version 3.x, the solution is this If you have Version 4.x the solution is this. If you have Version 5.x, the solution is this. You get the picture. Okay, let me explain the temp tables approach now. Most subqueries are not correlated so the subquery gets done once and then its result gets plugged into the outer query. For example, given a table called Employee in the Sample database which has one row for each employee and each row contains an employee number, last name, and salary, this query will return the employee number and last name of every employee who makes more than the average salary for all employees in the table: --- select empno, lastname from Sample.Employee where salary (select avg(salary) from Sample.Employee); --- If subqueries are supported, as in Version 4.1.x or above of MySQL, you just run that exact query and you will get a list of all of the people who make more than the average salary. However, if you are using an older version of MySQL which doesn't support subqueries, you need to use a temporary table approach. Basically, you create a temporary table that contains the same results that the subquery would have given you, then join it to the outer query. [Many, but not all, subqueries can be rewritten as joins.] For example: --- #Create the temporary table that will contain the average salary for all employees. create table if not exists temp_average_salary (average_salary decimal(9,2)); #Populate the temporary table with the average salary for all employees. insert into temp_average_salary(select avg(salary) from Sample.Employee); #Do a join of the employee table and the temporary table to obtain only employees who make #more than the average salary. select empno, lastname, salary from Sample.Employee e inner join temp_average_salary t where e.salary t.average_salary; --- This would give the same result as the original solution that used subqueries although there is obviously more work to do in accomplishing the desired result. Initially, I was almost certain that this approach could not be made to work for your problem due to its correlated nature but a couple of experiments persuaded me that it was possible after all. It's actually quite similar to the example I gave above, except that this time the temporary table is not just a single value on a single row but a table containing one row for each VBS_id, showing the max (latest) date for any row containing that VBS_id. Here's the definition of the table and the correct method of populating the table: --- create table if not exists temp_table (VBS_id int not null, max_date date not null); insert into temp_table (select VBS_id, max(date) from VBS_table group by VBS_id); --- If you display the contents of the temporary table, you get: --- VBS_idmax_date 112005-10-08 122005-10-08 --- Now, you simply join the VBS_table and the temp_table, as follows: --- select v.VBS_id, v.date, v.weight from VBS_table v inner join temp_table t on v.VBS_id = t.VBS_id and v.date = t.max_date; --- The result is exactly what you wanted: --- VBS_iddateweight 112005-10-0811.52 122005-10-0810.50 --- You could apply this solution to versions of MySQL that don't have subquery support; just remember that you need to re-create the temp_table immediately before you do the join each time; otherwise, you are going to determine whatever weights were in effect whenever you first populated the temp_table, not the current weights. In your case though, you can make life better for yourself. Since you have view support, you can simply create a view that is the equivalent to the temp_table and then join the view to the VBS_table to get the data you want. Since the view is always up-to-date, you eliminate the need to have the temp_table at all. Something like this: --- create view VBS_max_dates (VBS_id, max_date) as select VBS_id, max(date) from VBS_table group by VBS_id; select v.VBS_id, v.date, v.weight from VBS_table v inner join VBS_max_dates z on v.VBS_id = z.VBS_id and v.date = z.max_date; --- Note that the view is only created once, not each time you are about to do the join!! One other solution jumps to mind that should be just as good. Instead of creating temp tables or views, why not just put the original correlated subquery that I gave you into a stored procedure? Then, all you'd need to do is call that stored procedure every
Re: Help with an SQL query
Rhino wrote: I can't test this in MySQL- I'm using an older version of MySQL that doesn't support subqueries - but it works in DB2 and it should do the trick for getting the current weight of each VBS_id value: select VBS_id, date, weight from VBS_table x where date = (select max(date) from VBS_table where VBS_id = x.VBS_id); I'm assuming you are using a more version of MySQL which DOES support subqueries! If not, you may be able to get the same result with temp tables but I haven't tried that. Make sure to say something if you don't know how to use temp tables to simulate subqueries. I'm dubious that this query can be simulated with temp tables though (Rant: I REALLY wish people (all people, not just you) posting questions to this mailing list would get in the habit of specifying which version of MySQL they are using!! The answers to questions FREQUENTLY depend on the MySQL version so it would really help reduce the size of answers if people volunteered this information in the original question.) Also, I'm assuming that that MySQL supports correlated subqueries; I'm really not sure so I'll let you try the actual query and see if it works for you. Here's an explanation of how this query works, in case you've never seen one like this before. This is called a correlated subquery; the key thing that makes it obvious that this is a correlated subquery (in case you have to recognize one) is that a correlation name, in this case 'x', appears in the FROM clause of the outer query and again in the WHERE clause of the subquery. The subquery appears in brackets in case you are not familiar with subqueries. A correlated subquery works backwards to a normal subquery. In a normal subquery, the subquery is executed first and is only executed once: its result is plugged into the outer query which then executes just once as well. In a correlated subquery, both the outer query and the subquery are executed _repeatedly_, possibly MANY times each. The outer query is executed in order to obtain one row, THEN the subquery is executed to see if the row found by the outer query can be kept. In this case, let's say that the outer query returned the first row of the table, which has a VBS_id of 11 and a date of '10/3/2005': the subquery determines the maximum (most recent) date for any row that has same VBS_id as the one just found by the outer query; if the maximum date differs from the date found by the outer query, the outer query row is discarded and does not appear in the final result set. In this case, the maximum date for VBS_ID is 10/8/2005 which is not equal to the value found by the outer query, so that row is discarded. Having dealt with the first row of the outer query, the outer query executes again and gets another row. Again, the subquery is executed to see if the date is the same as maximum date for that VBS_id and again, the outer row is only kept if its date matches the maximum date found by the subquery. And so it goes, one row at a time, until the outer query has read every row of the table; a single row of the outer query is obtained, then the subquery determines if that row contains the maximum date for the VBS_id that was just found by the outer query. The final result will contain only rows that have the maximum dates for each VBS_id and will show the VBS_id, the maximum date, and the weight at that date. That is the result you requested. Bear in mind that a correlated query can perform much more poorly than a regular subquery, although the optimizer sometimes has tricks that make the performance quite tolerable. There may be a solution that doesn't involve a correlated subquery but it's not coming to me. However, others who are better at SQL may think of something that does the job. Rhino Wow, excellent explanation. By the way, my apologies... I am using MySQL 5.0.15. I would be interested in knowing how to use temp tables to simulate subqueries though. I found out that the sql statement, although it returns the correct result, does not work in a view because MySQL Views does not allow subqueries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
that result. Rhino - Original Message - From: Gobi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 05, 2005 8:05 AM Subject: Re: Help with an SQL query Rhino wrote: I can't test this in MySQL- I'm using an older version of MySQL that doesn't support subqueries - but it works in DB2 and it should do the trick for getting the current weight of each VBS_id value: select VBS_id, date, weight from VBS_table x where date = (select max(date) from VBS_table where VBS_id = x.VBS_id); I'm assuming you are using a more version of MySQL which DOES support subqueries! If not, you may be able to get the same result with temp tables but I haven't tried that. Make sure to say something if you don't know how to use temp tables to simulate subqueries. I'm dubious that this query can be simulated with temp tables though (Rant: I REALLY wish people (all people, not just you) posting questions to this mailing list would get in the habit of specifying which version of MySQL they are using!! The answers to questions FREQUENTLY depend on the MySQL version so it would really help reduce the size of answers if people volunteered this information in the original question.) Also, I'm assuming that that MySQL supports correlated subqueries; I'm really not sure so I'll let you try the actual query and see if it works for you. Here's an explanation of how this query works, in case you've never seen one like this before. This is called a correlated subquery; the key thing that makes it obvious that this is a correlated subquery (in case you have to recognize one) is that a correlation name, in this case 'x', appears in the FROM clause of the outer query and again in the WHERE clause of the subquery. The subquery appears in brackets in case you are not familiar with subqueries. A correlated subquery works backwards to a normal subquery. In a normal subquery, the subquery is executed first and is only executed once: its result is plugged into the outer query which then executes just once as well. In a correlated subquery, both the outer query and the subquery are executed _repeatedly_, possibly MANY times each. The outer query is executed in order to obtain one row, THEN the subquery is executed to see if the row found by the outer query can be kept. In this case, let's say that the outer query returned the first row of the table, which has a VBS_id of 11 and a date of '10/3/2005': the subquery determines the maximum (most recent) date for any row that has same VBS_id as the one just found by the outer query; if the maximum date differs from the date found by the outer query, the outer query row is discarded and does not appear in the final result set. In this case, the maximum date for VBS_ID is 10/8/2005 which is not equal to the value found by the outer query, so that row is discarded. Having dealt with the first row of the outer query, the outer query executes again and gets another row. Again, the subquery is executed to see if the date is the same as maximum date for that VBS_id and again, the outer row is only kept if its date matches the maximum date found by the subquery. And so it goes, one row at a time, until the outer query has read every row of the table; a single row of the outer query is obtained, then the subquery determines if that row contains the maximum date for the VBS_id that was just found by the outer query. The final result will contain only rows that have the maximum dates for each VBS_id and will show the VBS_id, the maximum date, and the weight at that date. That is the result you requested. Bear in mind that a correlated query can perform much more poorly than a regular subquery, although the optimizer sometimes has tricks that make the performance quite tolerable. There may be a solution that doesn't involve a correlated subquery but it's not coming to me. However, others who are better at SQL may think of something that does the job. Rhino Wow, excellent explanation. By the way, my apologies... I am using MySQL 5.0.15. I would be interested in knowing how to use temp tables to simulate subqueries though. I found out that the sql statement, although it returns the correct result, does not work in a view because MySQL Views does not allow subqueries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with an SQL query
Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Gobi wrote: Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. As a further note on my above post, I added idx to my query: Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID and it returned: idxVBS_IDmax(Date)weight 1 1110/8/200511.5 5 1210/8/200510.5 So actually, it is returning the wrong rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Gobi wrote: Gobi wrote: Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. As a further note on my above post, I added idx to my query: Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID and it returned: idxVBS_IDmax(Date)weight 1 1110/8/200511.5 5 1210/8/200510.5 So actually, it is returning the wrong rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Sorry for being slow but this statement is quite confusing and I am having difficulty trying to understand it in order to adapt it to my table. Can you explain it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Actually, taking a closer look at your link, I think my query is slightly different from Kemin's. Kemin was trying to get the top numbers from col2 AND col3 together. Therefore, in his original statement, he was trying to order both col2 and col3 in descending order. Me, I just want to get the most recent date for each unique VBS_ID. I don't need to get the max weight so I am not sure if it is necessary to concat my date and weight together. Comments? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Hi Gobi, the problem with your original query is that there is no guarantee that your max(date) and it's associated VBS_ID is picked, so what you have to ensure is that they get picked together, so I think your statement should be like this: SELECT VBS_ID, SUBSTRING( MAX( CONCAT( LPAD( Date,10,'0'),weight, 11 ) AS D LEFT( MAX( CONCAT( LPAD(Date,10,'0'),weight)), 10 ) AS W GROUP BY VBS_ID; What happens here is that you're not doing max on date, you're doing it on the combination of date and weight. What might be a problem though is your dateformat, you might run into problems with that I guess. Also you might have to play with lengths a bit. /Johan Gobi wrote: Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Sorry for being slow but this statement is quite confusing and I am having difficulty trying to understand it in order to adapt it to my table. Can you explain it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Figured out the query: select idx, vbs_id, date, weight from Weight, (select vbs_id as maxid, max(date) as maxdate from Weight group by vbs_id) as t where vbs_id = maxid and date = maxdate; It returns the proper weight and idx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Help with an SQL query
Oops, I meant to post this on the list AND copy the person asking the question Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Gobi [EMAIL PROTECTED] Sent: Friday, November 04, 2005 1:46 PM Subject: Re: Help with an SQL query I can't test this in MySQL- I'm using an older version of MySQL that doesn't support subqueries - but it works in DB2 and it should do the trick for getting the current weight of each VBS_id value: select VBS_id, date, weight from VBS_table x where date = (select max(date) from VBS_table where VBS_id = x.VBS_id); I'm assuming you are using a more version of MySQL which DOES support subqueries! If not, you may be able to get the same result with temp tables but I haven't tried that. Make sure to say something if you don't know how to use temp tables to simulate subqueries. I'm dubious that this query can be simulated with temp tables though (Rant: I REALLY wish people (all people, not just you) posting questions to this mailing list would get in the habit of specifying which version of MySQL they are using!! The answers to questions FREQUENTLY depend on the MySQL version so it would really help reduce the size of answers if people volunteered this information in the original question.) Also, I'm assuming that that MySQL supports correlated subqueries; I'm really not sure so I'll let you try the actual query and see if it works for you. Here's an explanation of how this query works, in case you've never seen one like this before. This is called a correlated subquery; the key thing that makes it obvious that this is a correlated subquery (in case you have to recognize one) is that a correlation name, in this case 'x', appears in the FROM clause of the outer query and again in the WHERE clause of the subquery. The subquery appears in brackets in case you are not familiar with subqueries. A correlated subquery works backwards to a normal subquery. In a normal subquery, the subquery is executed first and is only executed once: its result is plugged into the outer query which then executes just once as well. In a correlated subquery, both the outer query and the subquery are executed _repeatedly_, possibly MANY times each. The outer query is executed in order to obtain one row, THEN the subquery is executed to see if the row found by the outer query can be kept. In this case, let's say that the outer query returned the first row of the table, which has a VBS_id of 11 and a date of '10/3/2005': the subquery determines the maximum (most recent) date for any row that has same VBS_id as the one just found by the outer query; if the maximum date differs from the date found by the outer query, the outer query row is discarded and does not appear in the final result set. In this case, the maximum date for VBS_ID is 10/8/2005 which is not equal to the value found by the outer query, so that row is discarded. Having dealt with the first row of the outer query, the outer query executes again and gets another row. Again, the subquery is executed to see if the date is the same as maximum date for that VBS_id and again, the outer row is only kept if its date matches the maximum date found by the subquery. And so it goes, one row at a time, until the outer query has read every row of the table; a single row of the outer query is obtained, then the subquery determines if that row contains the maximum date for the VBS_id that was just found by the outer query. The final result will contain only rows that have the maximum dates for each VBS_id and will show the VBS_id, the maximum date, and the weight at that date. That is the result you requested. Bear in mind that a correlated query can perform much more poorly than a regular subquery, although the optimizer sometimes has tricks that make the performance quite tolerable. There may be a solution that doesn't involve a correlated subquery but it's not coming to me. However, others who are better at SQL may think of something that does the job. Rhino - Original Message - From: Gobi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 04, 2005 3:05 AM Subject: Help with an SQL query Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com
SQL query taking a long time
Following query is taking a long time (upto 10 secs) to return the resultset.
Would greatly appreciate if someone could help me understand why.
I have run 'analyze table tablename' on all the three tables involved. The
EXPLAIN output, record count and table description is also included.
SELECT SQL_CALC_FOUND_ROWS art.article_id aId, art.title, aFt.seq_no sn,
SUBSTRING(abs.abstract,1,10) abs
FROM art, abs, aFt
WHERE (
MATCH(art.title, art.subtitle, art.keywords, art.general_terms) AGAINST
('+recommender +systems' IN BOOLEAN MODE)
OR
MATCH(abs.abstract) AGAINST ('+recommender +systems' IN BOOLEAN MODE) )
AND art.article_id = aFt.article_id
AND art.article_id = abs.article_id
LIMIT 5
;
aId titlesn abs
245121 Recommender systems 1
245127 Recommender systems for evaluating1
331413 Workshop on recommender systems 1
353475 PYTHIA-II 1 Often scie
353481 Mining and visualizing recommendation 1 In this pa
table type possible_keys key key_len refrows Extra
aFt index PRIMARY PRIMARY 4 NULL 53191 Using index
art eq_ref PRIMARY PRIMARY 3 aFt.article_id 1
abs eq_ref PRIMARY PRIMARY 3 art.article_id 1 Using where
CREATE TABLE art ( -- Records: 54668
article_id mediumint(9),
title varchar(255),
subtitle varchar(127),
keywords mediumtext,
general_terms tinytext,
PRIMARY KEY (article_id),
FULLTEXT KEY title (title,subtitle,keywords,general_terms)
) TYPE=MyISAM;
CREATE TABLE abs ( -- Records: 54681
article_id mediumint(4),
abstract mediumtext,
PRIMARY KEY (article_id),
FULLTEXT KEY abstract (abstract)
) TYPE=MyISAM;
CREATE TABLE aFt ( -- Records: 53191
article_id mediumint(9),
seq_no tinyint(4),
PRIMARY KEY (article_id,seq_no)
) TYPE=MyISAM;
I am using mysql Ver 12.21 Distrib 4.0.15, for mandrake-linux-gnu (i586).
Thanks,
Nishi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query taking a long time...please
Just wondering if someone would be kind enough to take a look at it - Nishi
-Original Message-
Following query is taking a long time (upto 10 secs) to
return the resultset. Would greatly appreciate if someone
could help me understand why.
I have run 'analyze table tablename' on all the three
tables involved. The EXPLAIN output, record count and table
description is also included.
SELECT SQL_CALC_FOUND_ROWS art.article_id aId, art.title,
aFt.seq_no sn, SUBSTRING(abs.abstract,1,10) abs
FROM art, abs, aFt
WHERE (
MATCH(art.title, art.subtitle, art.keywords,
art.general_terms) AGAINST ('+recommender +systems' IN BOOLEAN MODE)
OR
MATCH(abs.abstract) AGAINST ('+recommender +systems' IN
BOOLEAN MODE) )
AND art.article_id = aFt.article_id
AND art.article_id = abs.article_id
LIMIT 5
;
aId titlesn abs
245121 Recommender systems 1
245127 Recommender systems for evaluating1
331413 Workshop on recommender systems 1
353475 PYTHIA-II 1 Often scie
353481 Mining and visualizing recommendation 1 In this pa
table type possible_keys key key_len refrows Extra
aFt index PRIMARY PRIMARY 4 NULL
53191 Using index
art eq_ref PRIMARY PRIMARY 3 aFt.article_id 1
abs eq_ref PRIMARY PRIMARY 3 art.article_id 1
Using where
CREATE TABLE art ( -- Records: 54668
article_id mediumint(9),
title varchar(255),
subtitle varchar(127),
keywords mediumtext,
general_terms tinytext,
PRIMARY KEY (article_id),
FULLTEXT KEY title (title,subtitle,keywords,general_terms)
) TYPE=MyISAM;
CREATE TABLE abs ( -- Records: 54681
article_id mediumint(4),
abstract mediumtext,
PRIMARY KEY (article_id),
FULLTEXT KEY abstract (abstract)
) TYPE=MyISAM;
CREATE TABLE aFt ( -- Records: 53191
article_id mediumint(9),
seq_no tinyint(4),
PRIMARY KEY (article_id,seq_no)
) TYPE=MyISAM;
I am using mysql Ver 12.21 Distrib 4.0.15, for
mandrake-linux-gnu (i586).
Thanks,
Nishi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
