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
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: 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
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
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: 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]
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]
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
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]
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]
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]
RE: SQL query taking a long time...please
At 01:58 PM 8/2/2005, you wrote:
Just wondering if someone would be kind enough to take a look at it - Nishi
Nishi,
What did EXPLAIN show? Also what happens if you have just one
Match? Is it faster? If so, why not run 2 queries and build a temporary
table from the results. Using OR may be what's slowing down the query.
Mike
-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]
--
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
Kapoor, Nishikant [EMAIL PROTECTED] wrote on
08/02/2005 02:58:08 PM:
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
Unfortunately for you this seems to be indexed well. You can *possibly*
speed this up if you split your FT search and your other information into
separate queries but it seems from your EXPLAIN output that you are using
the correct indexes and that your coverage is rather good. Here is my
idea, I do not guarantee it will work any better than what you already
have:
CREATE TEMPORARY tmpKwHits
SELECT art.article_id
FROM art
WHERE MATCH(art.title, art.subtitle, art.keywords,
art.general_terms) AGAINST ('+recommender +systems' IN BOOLEAN MODE)
UNION
SELECT abs.article_ID
FROM abs
WHERE MATCH(abs.abstract) AGAINST ('+recommender +systems' IN BOOLEAN
MODE);
ALTER TABLE tmpKwHits add key(article_id);
SELECT SQL_CALC_FOUND_ROWS art.article_id aId, art.title,
aFt.seq_no sn, SUBSTRING(abs.abstract,1,10) abs
FROM tmpKwhits tkw
INNER JOIN art
on art.article_id = tkw.article_id
INNER JOIN abs
ON abs.article_id = tkw.article_id
INNER JOIN aFt
ON aft.article_id = tkw.article_id
LIMIT 5;
DROP TEMPORARY TABLE tmpKwHits;
My other idea is to change your one query into a UNION of two (to perform
the same function as your OR clause).
(SELECT 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)
AND art.article_id = aFt.article_id
AND art.article_id = abs.article_id
LIMIT 5)
UNION
(SELECT art.article_id aId, art.title,
aFt.seq_no sn, SUBSTRING(abs.abstract,1,10) abs
FROM art, abs, aFt
WHERE 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)
LIMIT 5;
My problem is I don't know into which term of the UNION you need to add
the SQL_CALC_FOUND_ROWS predicate.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: SQL query taking a long time...please
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 02, 2005 3:14 PM
To: Kapoor, Nishikant
Cc: mysql@lists.mysql.com
Subject: RE: SQL query taking a long time...please
Kapoor, Nishikant [EMAIL PROTECTED] wrote on 08/02/2005 02:58:08 PM:
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
Unfortunately for you this seems to be indexed well. You can *possibly* speed
this up if you split your FT search and your other information into separate
queries but it seems from your EXPLAIN output that you are using the correct
indexes and that your coverage is rather good. Here is my idea, I do not
guarantee it will work any better than what you already have:
CREATE TEMPORARY tmpKwHits
SELECT art.article_id
FROM art
WHERE MATCH(art.title, art.subtitle, art.keywords,
art.general_terms) AGAINST ('+recommender +systems' IN BOOLEAN MODE)
UNION
SELECT abs.article_ID
FROM abs
WHERE MATCH(abs.abstract) AGAINST ('+recommender +systems' IN BOOLEAN MODE);
ALTER TABLE tmpKwHits add key(article_id);
SELECT SQL_CALC_FOUND_ROWS art.article_id aId, art.title,
aFt.seq_no sn, SUBSTRING(abs.abstract,1,10) abs
FROM tmpKwhits tkw
INNER JOIN art
on art.article_id = tkw.article_id
INNER JOIN abs
ON abs.article_id = tkw.article_id
INNER JOIN aFt
ON aft.article_id = tkw.article_id
LIMIT 5;
DROP TEMPORARY TABLE tmpKwHits;
My other idea is to change your one query into a UNION of two (to perform the
same function as your OR clause).
(SELECT 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)
AND art.article_id = aFt.article_id
AND art.article_id = abs.article_id
LIMIT 5)
UNION
(SELECT art.article_id aId, art.title,
aFt.seq_no sn, SUBSTRING(abs.abstract,1,10) abs
FROM art, abs, aFt
WHERE 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)
LIMIT 5;
My problem is I don't know into which term of the UNION you need to add the
SQL_CALC_FOUND_ROWS predicate.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Shawn,
Your solution with UNION is a huge improvement! Thank you very much. You are
good.
As for the SQL_CALC_FOUND_ROWS predicate, mySQL does not allow it to be put in
the second SELECT. It must be with the first SELECT statement, or else mySQL
complains:
-- ERROR 1234 at line 1: Wrong usage/placement of 'SQL_CALC_FOUND_ROWS'
Thanks again,
Nishi
Re: sql query to return unique ids from a table of date stamped results
Hi. May be it will be helpful: http://dev.mysql.com/doc/mysql/en/TIMESTAMP_4.1.html Rob Keeling [EMAIL PROTECTED] wrote: I am trying to find the sql statement needed to extract, from a table of data with multiple instances of a id no, a list of unique id nos, picking the latest (by datestamp which is stored as a second field) so that a master list is updated. The application is a list of student photos, each database row defines the filename of a photo, the student id is assigned to the photo once it is known, I then need to produce a list for all photos showing the details of the latest photo for each student. SELECT * FROM Photosforimport ORDER BY adno, Lastupdatetime DESC Gives me the data I need, however I need to be able to just pick out the row with the latest Lastupdatetime for each ADNO. I couldn`t find anything on google, but may have been asking the wrong question! Any help greatly appreciated. Rob Keeling -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query to return unique ids from a table of date stamped results
For each ADNO, you want the row with Lastupdatetime equal to that group's MAX(Lastupdatetime) . This is a little bit tricky and a frequently asked question. There are 3 ways to do it documented in the manual http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. Michael Rob Keeling wrote: I am trying to find the sql statement needed to extract, from a table of data with multiple instances of a id no, a list of unique id nos, picking the latest (by datestamp which is stored as a second field) so that a master list is updated. The application is a list of student photos, each database row defines the filename of a photo, the student id is assigned to the photo once it is known, I then need to produce a list for all photos showing the details of the latest photo for each student. SELECT * FROM Photosforimport ORDER BY adno, Lastupdatetime DESC Gives me the data I need, however I need to be able to just pick out the row with the latest Lastupdatetime for each ADNO. I couldn`t find anything on google, but may have been asking the wrong question! Any help greatly appreciated. Rob Keeling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query to return unique ids from a table of date stamped results
How will that help? He already has a timestamp column. He's asking how to get the rows conataining the groupwise maximum timestamps. Michael Gleb Paharenko wrote: Hi. May be it will be helpful: http://dev.mysql.com/doc/mysql/en/TIMESTAMP_4.1.html Rob Keeling [EMAIL PROTECTED] wrote: I am trying to find the sql statement needed to extract, from a table of data with multiple instances of a id no, a list of unique id nos, picking the latest (by datestamp which is stored as a second field) so that a master list is updated. The application is a list of student photos, each database row defines the filename of a photo, the student id is assigned to the photo once it is known, I then need to produce a list for all photos showing the details of the latest photo for each student. SELECT * FROM Photosforimport ORDER BY adno, Lastupdatetime DESC Gives me the data I need, however I need to be able to just pick out the row with the latest Lastupdatetime for each ADNO. I couldn`t find anything on google, but may have been asking the wrong question! Any help greatly appreciated. Rob Keeling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael Michael J. Pawlowsky wrote: Im not sure if this is possible or not. I have a Sales leads table. Part of the table has 2 employee_ids. 1. The Sales person the lead is assigned to. 2. The Marketing person that generated the lead. Then there is a employee table that has ids and names. When generating a report for leads I would like to lookup the name of the employee. I know I can do it with a seperate query, but I'm wondering if I can also do it in one query. Something like: SELECT employee.name as sales_name, employee.name as marketing_name, leads.id FROM leads, employee WHERE employee.id = leads.salesid AND employee.id = leads.marketingid Is there someway this can be done? Thanks for any assistance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Right. If the employee ID in either the rep_no or entered_by columns does not have a corresponding row in the global_employee table, then the regular join won't match that row. In that case, as you found, you need a LEFT JOIN, which guarantees you get the rows from the table on the left, and auto-creates NULL fields for the table on the right when it has no matching row. For reference, this is mentioned in the manual http://dev.mysql.com/doc/mysql/en/JOIN.html. Michael Michael J. Pawlowsky wrote: Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql Query Issue
I suggest that you add more indexes to your tables. If you run an EXPLAIN on your query, you will see that you are doing WAY too many table scans and that is what is slowing you down. Index the columns in each table that reference the ID values of another table. Then run your EXPLAIN again and you should see a major difference. Use the manual, it has great advice on optimizing queries and full documentation of the EXPLAIN command. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeyabalan MurugesanTo: [EMAIL PROTECTED] Sankarasubramaniacc: n Fax to: [EMAIL PROTECTED]Subject: Sql Query Issue xis.com 07/12/2004 03:55 AM Hi All, I migrated the data from Oracle to MySQL.The following query works fine with Oracle whereas in MySql its hanging. SELECT distinct caty.name, caty.c_id, caty.notes, count(distinct segs.in_id) as num FROM segs, caty, st_mbers, t_mbers, p_mbrs, pr_mbers where segs.c_id = caty.c_id and caty.c_id=st_mbers.c_id and st_mbers.st_id = t_mbers.st_id and t_mbers.t_id = p_mbers.t_id and p_mbers.p_id = pr_mbers.p_id and pr_mbers.p_id = 1 group by st_caty.c_id, st_caty.name, st_caty.notes order by st_caty.name Following are the table structures with Row count in each table. CREATE TABLE segs( s_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, c_idINT(12) NULL , textVARCHAR(255) NULL, lookupVARCHAR(255) NULL, in_id INT(12) NULL, prr_d VARCHAR(12) NULL, nxt_d VARCHAR(12) NULL, descn VARCHAR(255) NULL, notes VARCHAR(255) NULL, s_st_id INT(12) NULL, versn FLOAT(10,4) NULL, mesg_type VARCHAR(50) NULL ); Row count 34700 CREATE TABLE caty( c_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, nameVARCHAR(255) NULL, notes VARCHAR(255) NULL, version FLOAT(10,4) ); Row count 281 CREATE TABLE st_mbers( st_idINT(12) NULL, c_idINT(12) NULL, versionFLOAT(10,4) NULL, st_mber_id INT(12) NOT NULL ); Row count 1362 CREATE TABLE t_mbers( t_id INT(12) NOT NULL, st_id INT(12) NULL, seq_nbr INT(12) NULL, version FLOAT(10,4) NULL, t_mber_id INT(12) NOT NULL ); Row count 1260 CREATE TABLE p_mbers ( p_id INT(12) NOT NULL , t_id INT(12) NULL , seq_nbr INT(12) NULL, notes TEXT NULL, versionFLOAT(10,4) NULL, p_mber_id INT(12) NOT NULL ); Row Count 2198 CREATE TABLE pr_mbers( pr_idINT(12) NULL, p_idINT(12) NULL, seq_nbr INT(12) NULL, pr_mbr_id INT(12) NOT NULL ); Row Count 294 Help me in solving this hanging issue. I tried the same with both Linux and Windows XP but without any success. I tried with one record in each table mentioned above and its working fine. Kindly guide me in this regard. Thanks in advance. regards msjeyabalan ** CONFIDENTIAL INFORMATION
Re: Sql Query Issue
This is an index problem. Your tables don't contain any indices except on PKs. This can't work, given the number of joins and table sizes. Read the doc about indices. Stefan Am Monday 12 July 2004 09:55 schrieb Jeyabalan Murugesan Sankarasubramanian: Hi All, I migrated the data from Oracle to MySQL.The following query works fine with Oracle whereas in MySql its hanging. SELECT distinct caty.name, caty.c_id, caty.notes, count(distinct segs.in_id) as num FROM segs, caty, st_mbers, t_mbers, p_mbrs, pr_mbers where segs.c_id = caty.c_id and caty.c_id=st_mbers.c_id and st_mbers.st_id = t_mbers.st_id and t_mbers.t_id = p_mbers.t_id and p_mbers.p_id = pr_mbers.p_id and pr_mbers.p_id = 1 group by st_caty.c_id, st_caty.name, st_caty.notes order by st_caty.name Following are the table structures with Row count in each table. CREATE TABLE segs( s_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, c_id INT(12) NULL , text VARCHAR(255) NULL, lookup VARCHAR(255) NULL, in_id INT(12) NULL, prr_d VARCHAR(12) NULL, nxt_d VARCHAR(12) NULL, descn VARCHAR(255) NULL, notes VARCHAR(255) NULL, s_st_idINT(12) NULL, versn FLOAT(10,4) NULL, mesg_type VARCHAR(50) NULL ); Row count 34700 CREATE TABLE caty( c_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NULL, notes VARCHAR(255) NULL, versionFLOAT(10,4) ); Row count 281 CREATE TABLE st_mbers( st_id INT(12) NULL, c_id INT(12) NULL, version FLOAT(10,4) NULL, st_mber_id INT(12) NOT NULL ); Row count 1362 CREATE TABLE t_mbers( t_id INT(12) NOT NULL, st_id INT(12) NULL, seq_nbrINT(12) NULL, versionFLOAT(10,4) NULL, t_mber_id INT(12) NOT NULL ); Row count 1260 CREATE TABLE p_mbers ( p_id INT(12) NOT NULL , t_id INT(12) NULL , seq_nbr INT(12) NULL, notes TEXT NULL, version FLOAT(10,4) NULL, p_mber_id INT(12) NOT NULL ); Row Count 2198 CREATE TABLE pr_mbers( pr_id INT(12) NULL, p_id INT(12) NULL, seq_nbrINT(12) NULL, pr_mbr_id INT(12) NOT NULL ); Row Count 294 Help me in solving this hanging issue. I tried the same with both Linux and Windows XP but without any success. I tried with one record in each table mentioned above and its working fine. Kindly guide me in this regard. Thanks in advance. regards msjeyabalan ** CONFIDENTIAL INFORMATION ** This e-mail transmission and any attachments may contain confidential information belonging to the sender. The information is intended solely for the use of the individual(s) or entities addressed. If you are not the intended recipient, you are hereby notified that any copying, disclosing, distributing, or use of this e-mail and/or attachment is strictly prohibited. If you received this transmission in error please notify the sender immediately and delete the message and all attachments. -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
The you will need to use the second format. DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 4:09 PM Subject: Re: SQL Query Question - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- 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 Question
If you do any math on your column, no index on the column can be used. If possible, you should always try to write your condition so that the calculations are done on the value(s) to compare to, not on the column. So, assuming you have no rows with future timestamps, something like this should work: SELECT * FROM yourtable WHERE queue_time BETWEEN CURDATE() AND NOW(); If you might have timestamps later than NOW for some reason, you could change this to something like: SELECT * FROM yourtable WHERE queue_time BETWEEN CURDATE() AND (CURDATE + INTERVAL 1 DAY - INTERVAL 1 SECOND); To select yesterday's rows, you could do something like: SELECT * FROM yourtable WHERE queue_time BETWEEN (CURDATE() - INTERVAL 1 DAY) AND (CURDATE() - INTERVAL 1 SECOND); To help you visualize what's happening here, try SELECT CURDATE(), NOW(), CURDATE() + INTERVAL 1 DAY - INTERVAL 1 SECOND, CURDATE() - INTERVAL 1 DAY, CURDATE() - INTERVAL 1 SECOND; You say you've been wrestling with the docs, so you probably already seen these, but just in case: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html Michael Victor Pendleton wrote: The you will need to use the second format. DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() -Original Message- From: Dirk Bremer (NISC) - Original Message - From: Victor Pendleton [EMAIL PROTECTED] If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
This works for Oracle, give it a try, use any format you want for the MM/DD/YY area. select to_char(queue_time, 'MM/DD/YY'); Scott Purcell -Original Message- From: Dirk Bremer (NISC) [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 2:55 PM To: [EMAIL PROTECTED] Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- 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 Question
WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- 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 Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 3:25 PM Subject: Re: SQL Query Question - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- 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 Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Fri, 2004-04-16 at 14:09, Dirk Bremer (NISC) wrote: - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. You could keep any index you have and do it this way: SELECT a,b FROM x WHERE queue_time BETWEEN date_format(curdate(), %Y%m%e00) AND date_format(curdate(), %Y%m%e235959); -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Re: SQL Query problem
On Friday 20 February 2004 15:19, Claire Lee wrote: Hi All, I have a query problem here. Say I have a table with employee records of three different departments. If each department manager wants to see employee info of their own department. Three different queries will be needed. Is there a way that I can write one single query and let SQL decide which department info to display at the run time? Thanks. What language are you doing this in? You should be able to provide the appropriate 'where' clause when generating the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query help
This is probably tediously basic for all you super whiz MySQL people but help me out if you can. I have 2 tables in my database (there will be more) table_Applics table_keywords I want to select columns of information from table_applics based on the ID results from table_keywords. something like this I guess, Select ID From Keywords Where markets = 'Financial' This then gives me a list of ID's which I then want to take to table_applics and get the row of information for each ID number in the list that exist Select ID,NAME,LNAME,ADDRESS1 from table_applics Whats the best way to achieve this in a single query ? can any one help me with the Logic !!! Here from you soon I hope, Best regards Andy Fletcher -- You can try that: I do not know if it is what you are looking for: SELECT - FROM TABLE1 INNER JOIN TABLE2 USING (common_column) GROUP BY -- ORDER BY ; Another way: SELECT - FROM TABLE1 INNER JOIN TABLE2 ON table1.field=table2.field (field as common_column) GROUP BY -- ORDER BY ; --- I hope that it works. Marcelo Araujo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query help required
- Original Message - From: Riaan Oberholzer [EMAIL PROTECTED] 2-0, 2-1, 2-2, 2-3 1-0, 1-1, 1-2, 1-3 0-0, 0-1, 0-2, 0-3 SELECT CONCAT(predictionA, '-', predictionB) AS score, COUNT(CONCAT(predictionA, '-', predictionB)) AS count FROM table WHERE CONCAT(predictionA, '-', predictionB) 0 GROUP BY score ORDER BY predictionA DESC, predictionB Maybe not the fastest solution, but it is a single query ! The only thing you have to add is that you calculate the grandtotal to display the n% has selected.. part Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Hello, For my final solution I decided to use the inner join method. The query is created dynamically based upon a user interface component that allows people to build queries using parenthesis, ands and or's. Plus there is another field that I didn't include in the original question so as to keep the problem focused. So here is the basic structure of what I did, each query starts with the standard select table1.*,table2.* from table1,tabl2, then I append the inner joins for each search able field. The user can choose to search for one value or many values. Realistically I don't expect this to go above 3 to 5 fields. searchtable s1,searchtable s2 Then I include the queries where (table2.id=s1.rdid and then the dynamic part ((s2.vid=10109 and s2.value=5) and (s1.vid=10089 and s1.value4000))) now I make sure all the searchtables are matched and (s1.rdid=s2.rdid) then the rest of my query which binds table1 to table2 but that irrelevant to this discussion and blah blah blah Here is an example select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value=5) and (s1. vid =10089 and s1.value4000))) and (s1.rdid=s2.rdid) and blah blah blah This works really fast for a table with about 20,000 records. I hope it works against a large table. But unfortunately when I add an 'or' it really slows down by about 5 fold. select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and s3.value=5) and (s1. vid =10089 and s1.value4000)) or (v2. vid =10096 and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) and blah blah blah Thanks for everyone's help! I can't tell you how much I appreciate it. Joe On Monday, January 19, 2004, at 07:30 PM, Jochem van Dieten wrote: Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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 Question
I think I figured out the time problem. If I make s2 in the or s1 and remove any instances of s2 it works very fast with the 'or'. Joe On Tuesday, January 20, 2004, at 09:50 AM, sulewski wrote: Hello, For my final solution I decided to use the inner join method. The query is created dynamically based upon a user interface component that allows people to build queries using parenthesis, ands and or's. Plus there is another field that I didn't include in the original question so as to keep the problem focused. So here is the basic structure of what I did, each query starts with the standard select table1.*,table2.* from table1,tabl2, then I append the inner joins for each search able field. The user can choose to search for one value or many values. Realistically I don't expect this to go above 3 to 5 fields. searchtable s1,searchtable s2 Then I include the queries where (table2.id=s1.rdid and then the dynamic part ((s2.vid=10109 and s2.value=5) and (s1.vid=10089 and s1.value4000))) now I make sure all the searchtables are matched and (s1.rdid=s2.rdid) then the rest of my query which binds table1 to table2 but that irrelevant to this discussion and blah blah blah Here is an example select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value=5) and (s1. vid =10089 and s1.value4000))) and (s1.rdid=s2.rdid) and blah blah blah This works really fast for a table with about 20,000 records. I hope it works against a large table. But unfortunately when I add an 'or' it really slows down by about 5 fold. select table1.*,table2.* from table1,table2,searchtable s1,searchtable s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and s3.value=5) and (s1. vid =10089 and s1.value4000)) or (v2. vid =10096 and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) and blah blah blah Thanks for everyone's help! I can't tell you how much I appreciate it. Joe On Monday, January 19, 2004, at 07:30 PM, Jochem van Dieten wrote: Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- 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 Question
Let me post the question this way, MyTable --- pointerid valueid 811 54 811 63 812 100 813 200 814 300 815 400 I want all the records in MyTable where (valueid=54 and valueid=63) or valueid=400 group by pointerid Which means I would get the records whose pointer id is 811 and 815 Thanks, Joe On Monday, January 19, 2004, at 03:03 PM, Jamie Murray wrote: Joe didn't you already post this question last week and have it correctly answered by Roger ? I only ask because at that time I saw the query and thought to myself that the left outer join solution posted by Roger would not give you the results you had expected. - Original Message - From: sulewski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 19, 2004 3:47 PM Subject: SQL Query Question Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Thanks Joe -- 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 Question
On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) I *THINK* you're asking for the case where it has one of these values. In that case try Select Table1.* from Table1 t1 JOIN Table2 t2 on t1.ID = t2.rdid Where t2.vid in (46, 554) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query Question
Or, if I'm not mistaken, you could do something like: SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.id = t2.rdid AND t2.vid IN (46, 554) ; That should work, but the joining thing should too. I can never get the joining straight, so I always enjoy a shorter route. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 4:39 PM To: [EMAIL PROTECTED] Subject: Re: SQL Query Question Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- 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 Question
Jochem, I believe this works. This is also easy to build dynamically. The query is going to be generated based upon some user input. Thank you very much, Joe On Monday, January 19, 2004, at 04:38 PM, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 13:17, sulewski wrote: Okay, I think I'm missing something obvious. I have two tables Table 1 Table 2 ___ _ ID rdid vid ___ _ ID in table 1 links to rdid in table 2. This is a one to many relationship. Now I wish to find all the items in table 1 where table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) In other words which records from table 1 link to two records in table 2 who's vid are 46 and 554. I hope this makes sense. Actually, by definition this is an impossible match. The field vid can only have one value, and you're asking for a match where it has *TWO* values (46 and 554) So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- 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 Question
Lincoln Milner said: Or, if I'm not mistaken, you could do something like: SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.id = t2.rdid AND t2.vid IN (46, 554) ; That should work No. You are back to square one where there should only be one record in t2 with a vid of either 46 or 554. What is requested is a solution where there are 2 records in t2, one with a vid of 46 and one with a vid of 554. I can never get the joining straight, so I always enjoy a shorter route. Just make sure the shorter route gives the same results. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). If you absolutely need to do it with a join you might try Select t1.* from Table1 t1 JOIN Table2 t2 on (t1.id = t2.rdid and (t2.vid = 46 or t2.vid = 554)) I don't think this is as clear as a simple join with a Where, but there are a lot of ways to get the same result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? If you absolutely need to do it with a join you might try Select t1.* from Table1 t1 JOIN Table2 t2 on (t1.id = t2.rdid and (t2.vid = 46 or t2.vid = 554)) I don't think this is as clear as a simple join with a Where, but there are a lot of ways to get the same result. Build the tables, run the queries, compare the results. There are even more ways to get a different result, and yours is one of them. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of, and it's irrelevent. Table1 (t1) isn't used at all in the join parameters. As records from table1 are required in the result, this won't work as desired. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Michael Satterwhite wrote: On Monday 19 January 2004 16:30, Jochem van Dieten wrote: Michael Satterwhite said: On Monday 19 January 2004 15:38, Jochem van Dieten wrote: So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Although you're giving Table2 two aliases (t2 and t3) there is still only two tables and *ONE* field. In the join listed above, you are asking for the records where t2.rdid = t3.rdid (*NOT* what you want to do, you have now left t1 out of the join altogether) plus ??? (I'm not sure what this would match, although it looks interesting). Why not hold of judgement until you are sure what it would match? It's only the second part of the join that I'm not sure of Then why not hold of judgement until you are sure ? Table1 (t1) isn't used at all in the join parameters. Not all joins are specified using the join keyword. As records from table1 are required in the result, this won't work as desired. Would you please just create the tables and compare all the offered suggestions? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query
I think it should be: SELECT * FROM articles WHERE sectionID=1 ORDER BY Entrydate Desc LIMIT 1,10 Terry --Original Message- Any idea what is wrong with the following: SELECT * From articles ORDER BY EntryDate DESC LIMIT 1,10 WHERE SectionID=1 I want to return all articles with a particular SectionID, ordered by EntryDate and then I want to pick the start point and list the next 10 from that. Obviously in the final version the start point and the SectionID will be dynamic. I have tried removing the LIMIT part. I've tried changing the SectionID to a different field it always gives me an uninformative error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query
From: Terry Riley [EMAIL PROTECTED] I think it should be: SELECT * FROM articles WHERE sectionID=1 ORDER BY Entrydate Desc LIMIT 1,10 Terry That would be correct. I'll have to watch out for that ordering in the future. What confused me is if you just have the Select, Order By and Where the Order By can go on the first line. As I say, have to watch that. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Query
SELECT * From articles WHERE SectionID=1 ORDER BY EntryDate DESC LIMIT 1,10 the where clause should be after the table name HTH Peter -Original Message- From: Ian O'Rourke [mailto:[EMAIL PROTECTED] Sent: 18 January 2004 11:22 To: [EMAIL PROTECTED] Subject: SQL Query Any idea what is wrong with the following: SELECT * From articles ORDER BY EntryDate DESC LIMIT 1,10 WHERE SectionID=1 I want to return all articles with a particular SectionID, ordered by EntryDate and then I want to pick the start point and list the next 10 from that. Obviously in the final version the start point and the SectionID will be dynamic. I have tried removing the LIMIT part. I've tried changing the SectionID to a different field it always gives me an uninformative error? -- 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
My guess will be that the where clause is misplaced. Try SELECT * From articles WHERE SectionID=1 ORDER BY EntryDate DESC LIMIT 1,10 PLS read URL: http://www.mysql.com/doc/en/SELECT.html On that page it is stated that: All clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. Bernard On Sunday 18 January 2004 06:21, Ian O'Rourke wrote: Any idea what is wrong with the following: SELECT * From articles ORDER BY EntryDate DESC LIMIT 1,10 WHERE SectionID=1 I want to return all articles with a particular SectionID, ordered by EntryDate and then I want to pick the start point and list the next 10 from that. Obviously in the final version the start point and the SectionID will be dynamic. I have tried removing the LIMIT part. I've tried changing the SectionID to a different field it always gives me an uninformative error? . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query for faceted classification system
* Seamus R Abshere i am developing a photo gallery with php4/mysql4.0 that uses faceted classification. -my tables: photos(photoid) metadata(photoid,facetid) -to select all of the photoid's that are associated with either facetid 1 or 2: SELECT DISTINCT photos.* FROM photos,metadata WHERE photos.photoid = metadata.photoid AND (metadata.facetid = 1 OR metadata.facetid = 2) but what if i want to select all photoids that are associated with BOTH facetids? is there a join? (just sticking AND in there won't work, because any row in metadata can only have one facetid.) You can join the metadata table twice: SELECT photos.* FROM photos,metadata m1,metadata m2 WHERE photos.photoid = m1.photoid AND m1.facetid = 1 AND photos.photoid = m2.photoid AND m2.facetid = 2 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query question
Pael, Try this: SELECT firmal.beskrivelse as Businessline, lokasjon.navn as Location, count(person.[uniqueid]) FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid GROUP BY firmal.beskrivelse, lokasjon.navn Replace [uniqueid] with the primary key of the person table. Andy -Original Message- From: Paal Eriksen [mailto:[EMAIL PROTECTED] Sent: 11 November 2003 12:11 To: [EMAIL PROTECTED] Subject: SQL query question Hi, i have the following query: SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as Location FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid which will give me a list of Name, Businessline, Location. What i'm trying to do is to get a list which is grouped on Businessline and Location. Then i want to list a count of name at each location and businessline. How can i achieve this, if it's possible? So it should be like this: Businessline, Location, Sum people A AA 10 A AB 30 B AA 5 B AB 27 B AC 90 Paal Ny versjon av Yahoo! Messenger Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query question
So close, Thanks you very much Andy. I tried one similar to your suggestion, but didn't get quite the result i expected. Cheers Paal Ny versjon av Yahoo! Messenger Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom
Re: SQL query question
try group by SELECT firmal.beskrivelse as Businessline, lokasjon.navn as Location, count(person.name) as Sum People FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid group by firmal.beskrivelse, lokasjon.navn -leo- From: Paal Eriksen To: [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:11 PM Subject: SQL query question SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as Location FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid Businessline, Location, Sum people A AA 10 A AB 30 B AA 5 B AB 27 B AC 90
Re: SQL query needed
* Reto Baumann I'm working on a book database with some special requirements. Each book is associated with some keywords and put into a category. Category 0 is special, as this is Unsorted, i.e. not associated with a category (which most books are at the moment). For thei query, let's simplify the structure and define it as follows: CREATE TABLE books ( bookID mediumint(8) unsigned NOT NULL auto_increment, title varchar(200) NOT NULL default '', category mediumint(9) default '0', score mediumint(9) NOT NULL default '0', PRIMARY KEY (bookID) ) TYPE=MyISAM AUTO_INCREMENT=1; With each book, there is also a score field which represents the rating of a book, therefore the higher the better. Now I would like to construct a query which does the following: * Return all books that match a requested keyword * All matching books from category 0 * Only one book for each category 0 if there is a match with the keyword (and if so, the one with the highest score) * Order the books by score Kind of tricky, but maybe something like this could be used: SELECT DISTINCT books.* FROM books LEFT JOIN books b2 ON b2.title LIKE %$keyword% AND b2.category = books.category AND b2.score books.score WHERE books.title LIKE %$keyword% AND (b2.bookID IS NULL OR books.category = 0) ORDER BY books.score; The left join is used to check if there are any rows with a higher score for the same category. If there is, this row is _not_ included, unless category=0. You will get multiple books from the same category if two or more books share the same highest score within that category. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query help
Hi, I forgot to mention that the table contains more information, it has more columns than just a and b. These extra columns contains the actual information that I'm looking for. I.e. the mentioned table could be looking like this: +--+--+--+--+-+--- | a| b| data | user | comment | ... +--+--+--+--+-+--- And I still want those entire rows with DISTINCT A and MAX(B). Regards Svein I have this table where the columns and rows are organized like this: +--+--+ | a| b| +--+--+ |1 |1 | |2 |1 | |2 |2 | |2 |3 | |2 |4 | |3 |1 | |3 |2 | +--+--+ I want to run a select that gives me one row for each unique value of 'a'. And in the cases where several rows exists for one single value of 'a', I require the rows with the largest values of 'b'. In SQL lingo that would be DISTINCT A and MAX(B), but I dont know how to write this into one select... I.e. if I would apply these rules on the set above, I should get the following result: +--+--+ | a| b| +--+--+ |1 |1 | |2 |4 | |3 |2 | +--+--+ Does anyone know how to formalize this request into a single SELECT statement (using mysql 4.0.13), please? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query crashes MySQL
Hi! On Sep 12, Irwin Boutboul wrote: Here it is: select floor(avg(selection.bandwidth))*8000 as avgbandwidth from (select avg(bandwidth) as bandwidth from FEEDBACK_DOWNLOADS where servername= ? and ( bytesdownloaded 50 or timeduration 3000 ) group by id order by starttime desc limit 20) as selection; This complex (?) query makes mySQL crash everytime. If I replace the inner 'select avg(bandwidth)' by 'select *' it works fine. I am pretty sure this is not related to my table. You could create any table and try a query like that on it... If you can t reproduce the bug, I can give the details of my table. Shall I file a bug for this one ? If MySQL crashes you definitely shall to file a bugreport (on http://bugs.mysql.com) - so that you can be sure 4.1.1 will not crash :) But try to provide a test case. If you think that one could create any table and try a query like that on it - then provide a set of SQL statements starting from CREATE TABLE... that will crash MySQL. Though we try our best to carefully examine all bugreports, bugs with clear repeatable test cases get higher priority, of course. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query crashes MySQL
Yes, that is exactly what I am experiencing. The result comes back, and the server dies So it's probably a known bug It s via mysqlcc. With command line it works fine in fact (good catch). I'm not familiar with the JDBC driver/mysqlcc. Does it (the JDBC) also use mysqlcc ? Or can I run safely my queries with the JDBC driver ? (or shall I test too:) Thanks, Irwin Boutboul Advanced Internet Technology 150 Kettletown Road, B2-N06 Southbury, CT 06488 Phone: 203-486-5614 Little, Tim [EMAIL PROTECTED] 09/12/2003 01:58 PM To Irwin Boutboul/Watson/[EMAIL PROTECTED] cc Subject RE: SQL query crashes MySQL Does that crash the server in command-line mode (via mysql) or just in MySQLcc? I've seen similar crashes (actual full server crashes) in similar queries but only under mysqlcc. It seems that the results come back but the server dies just after that point. Is this what you are experiencing? Tim... -Original Message- From: Irwin Boutboul [mailto:[EMAIL PROTECTED] Sent: Friday, September 12, 2003 12:03 PM To: [EMAIL PROTECTED] Subject: SQL query crashes MySQL Here it is: select floor(avg(selection.bandwidth))*8000 as avgbandwidth from (select avg(bandwidth) as bandwidth from FEEDBACK_DOWNLOADS where servername= ? and ( bytesdownloaded 50 or timeduration 3000 ) group by id order by starttime desc limit 20) as selection; This complex (?) query makes mySQL crash everytime. If I replace the inner 'select avg(bandwidth)' by 'select *' it works fine. I am pretty sure this is not related to my table. You could create any table and try a query like that on it... If you can t reproduce the bug, I can give the details of my table. Shall I file a bug for this one ? (MySQL 4.1) Irwin Boutboul Advanced Internet Technology 150 Kettletown Road, B2-N06 Southbury, CT 06488 Phone: 203-486-5614
RE: SQL Query Syntax Error
Trevor Sather wrote: Hello The following query used to work when I was using an Access database, but now that I've moved to MySQL I get a syntax error when I try and run it: SELECT *, (SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_APPROVED = 'Yes') AS LINK_COUNT FROM Categories ORDER BY CAT_NAME ASC The error message is this: SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]syntax error near 'SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_AP' at line 1 Any immediate reactions? Thanks in advance for any help you can give me... What version are you using? Sub-selects only became available in MySQL 4.1 (I think). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query
SELECT Place.id, Place.name FROM Place LEFT JOIN Place_link ON Place.id=Place_link.Place WHERE Place.id!=1 AND Place_link.LinkTo!=1; This section of the manual will probably help you further. http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html Edward Dudlik Becoming Digital www.becomingdigital.com Did I help you? Want to show your thanks? www.amazon.com/o/registry/EGDXEBBWTYUU - Original Message - From: Grégory Verret [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 25 June, 2003 00:31 Subject: SQL Query Hi all, I just looked at the mysql doc and I didnt find an answer to my question. does mysql has the minus statement ? or is there something similar ? SELECT something FROM somewhere MINUS SELECT something_else FROM somewhere; Or this one could do the job but mysql doesnt seems to accept it either.. SELECT id FROM there WHERE id != ( SELECT id FROM this_one ); so Here's what I want to do... my Tables are : CREATE TABLE Place ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name VARCHAR(60) NOT NULL, Desc TEXT, PRIMARY KEY (id) ); CREATE TABLE Place_link ( Place INT UNSIGNED NOT NULL REFERENCES Place(id), LinkTo INT UNSIGNED NOT NULL REFERENCES Place(id), PRIMARY KEY (Place, LinkTo) ); I got a table with different places (Amusement park, Store, etc... ) and the Place_linf table contains the id of a place (Place) and if from that place, you can go to another place, its in the LinkTo. example : You got the 1:Market, 2:Zoo, 3:Pool, 4:Arena, 5:Stadium From the market you can go to the zoo and the Arena, and from the pool, you can go to the zoo, and the market... so we have : Place - LinkTo 1 2 3 1 3 2 1 4 so my query is: I want Place(id,Name) from Place but not the market (id != '1') and not the place where the market has a link to (not 2, 4). And I dont know how to do this query... Anyone knows ? (if someone understand what im trying to do hehe) Thx Gregory -- 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 - 3 tables - 3rd one conatins records to not display
What you need is a LEFT JOIN. When you use a LEFT JOIN, you get all rows from your main table, with either the data from the penpals_privmsgs_block table if there is corresponding data, or NULL if there is no related row. Take a look here: http://www.mysql.com/doc/en/JOIN.html for more information. Here's a quick rewrite, you fill in the blanks: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID FROM useronline LEFT JOIN penpals_privmsgs_block ON useronline.something = penpals_privmsgs_block.somethingelse, penpals_fav WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = $colname AND penpals.privmsgs_block.something IS NULL Regards, Mike Hillyer www.vbmysql.com -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: SQL query - 3 tables - 3rd one conatins records to not display I have a SQL query that needs to reference three different tables. I'm creating an online buddy list of members who are online. I have all of this functioning but am trying to also reference another table where the user is being blocked, in which case I do not what the user's name to be shown in the user's buddy list. I'm using the code below: //SET $colname TO USER'S ID SESSION $colname = $HTTP_SESSION_VARS['svUserID']; //SELECT THE TABLES FROM DATABASE SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID FROM useronline, penpals_fav, penpals_privmsgs_block // HERE I CHECK IF THE USER'S NAME IS IN THE FAVORITIES USER TABLE // AND THE ONLINE USER TABLE ALL OF WHICH WORKS FINE WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = $colname AND penpals_privmsgs_block.user_id = $colname It gets tricky here when I try to select from another table because this table will not always have the user's name in it, only if the user is blocked by another user will there be a record, in which case we do not want the user's name to be displayed. I was thinking this, but then realized that this would only create a list of those who are being blocked which not what I'm trying to do. AND penpals_privmsgs_block.blocked_id != colname -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [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 - 3 tables - 3rd one conatins records to not display
OK so now I have something like this: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID, penpals_privmsgs_block.user_id, penpals_privmsgs_block.blocked_id FROM useronline, penpals_privmsgs_block left join penpals_fav on penpals_privmsgs_block.user_id WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = $colname AND penpals_privmsgs_block.blocked_id IS NULL only this brings back nothing as when I remove the AND penpals_privmsgs_block.blocked_id IS NULL statement it results all the people online, but the penpals_privmsgs_block.blocked_id always equals 1 (the value I'm looking for on the blocked user only)for every record and none are null, which is in fact not the case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query - 3 tables - 3rd one conatins records to not display
Well, the following line does not join the penpals_privmsgs_block.user_id to anything: penpals_privmsgs_block left join penpals_fav on penpals_privmsgs_block.user_id Anyhow, by LEFT JOINing the block table to the penpals_fav table you are saying you want one row for every row in the block table, with entries in the fav table when they can be linked, and NULL otherwise. I think you want the order of the tables reversed: penpals_fav LEFT JOIN penpals_privmsgs_block ON penpals_fav.user_id = pempals_privmsgs_block.user_id That way you get one row for each row in penpals_fav, with either the block information form the block table, or NULL. The try putting the AND penpals_privmsgs_block.blocked_id IS NULL line back in? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 9:26 AM To: [EMAIL PROTECTED] Subject: RE: SQL query - 3 tables - 3rd one conatins records to not display OK so now I have something like this: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID, penpals_privmsgs_block.user_id, penpals_privmsgs_block.blocked_id FROM useronline, penpals_privmsgs_block left join penpals_fav on penpals_privmsgs_block.user_id WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = $colname AND penpals_privmsgs_block.blocked_id IS NULL only this brings back nothing as when I remove the AND penpals_privmsgs_block.blocked_id IS NULL statement it results all the people online, but the penpals_privmsgs_block.blocked_id always equals 1 (the value I'm looking for on the blocked user only)for every record and none are null, which is in fact not the case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [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 - 3 tables - 3rd one conatins records to not display
OK so now I have: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID, penpals_privmsgs_block.user_id, penpals_privmsgs_block.blocked_id FROM useronline, penpals_fav LEFT JOIN penpals_privmsgs_block ON penpals_fav.user_id = penpals_privmsgs_block.user_id WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = colname which comes back with a 1 in every record again and of course when I add the AND penpals_privmsgs_block.blocked_id IS NULL it comes up empty as every row has a 1 in it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query - 3 tables - 3rd one conatins records to not display
And what happens if you leave off the 'distinct' ? vernon wrote: OK so now I have: SELECT distinct useronline.uname, penpals_fav.fav_user_id, penpals_fav.ID, penpals_privmsgs_block.user_id, penpals_privmsgs_block.blocked_id FROM useronline, penpals_fav LEFT JOIN penpals_privmsgs_block ON penpals_fav.user_id = penpals_privmsgs_block.user_id WHERE penpals_fav.fav_user_name = useronline.uname AND penpals_fav.user_id = colname which comes back with a 1 in every record again and of course when I add the AND penpals_privmsgs_block.blocked_id IS NULL it comes up empty as every row has a 1 in it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL query - 3 tables - 3rd one conatins records to not display
The distinct is needed for usersonline as it holds multiple instanses of the user's name, which I only want once. To answer your question the user name comes up many times and the 1 still exist in ever record of the returned results, which of course is not true. -- Original Message --- From: gerald_clark [EMAIL PROTECTED] To: vernon [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Mon, 23 Jun 2003 11:13:53 -0500 Subject: Re: SQL query - 3 tables - 3rd one conatins records to not display And what happens if you leave off the 'distinct' ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query question
Rolf, You need to separate your functions. You are adding complexity to your world by storing irrelvant infromation in your database. Critical Data Handling (in a proper world) is ALWAYS handled separately from display. So in your example, You are storing all the html display formatting in your database. Bad idea. Not only does it complicate your SQL like you have found out... but selecting something that your looking for becomes unreadable. You should do the following. 1) Add a simple index to your table... i.e. an ever increasing number... that will always be unique to the line. Either that, or a short name that describes the line that you're looking for 2) Alter your database table to only store the critical information. Such as... ftp://cc278355-a.groni1.gr.home.nl/c64/games/--- The URL that alters.. --- The Filename --- The URLDesc --- The desc Then In the ASP, you say Select * from table1 WHERE (Either unique ID = X) or ( ShortName = 'GameName1' ); Then when you get the recordset out you handle it in asp with... % // Start ASPN insert data inline...% HTML FORMATTING HERE %=RS.(URL)% MORE HTML STUFF HERE LIKE P and FONT and ALIGN %=URLDESC% Etc Etc That would be proper programming form... Did that make sense? -Original Message- From: Rolf C [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 4:57 PM To: [EMAIL PROTECTED] Subject: SQL query question Hello all, I am a totally newby to MYSQL but here i go. I want to create an ASP page that shows an image (screendump of game) a game name a game description and an url. I created a database with the following table: filename, urldesc, desc Now i have to create an SQL query that will put this information in a webpage: this is what i got. SELECT ' img border=0 src=' filename '.gif /p td a href=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.zip ' urldesc '/a td p ' desc ' /p td ' FROM table1; The zip file for the download and the gif file for the image have the same filename exept the extention. It nearly works but it won't show all the text in the description. Any ideas? _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- 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 question
Well, from what limited info I have, it looks like your image tag is not closed properly. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Rolf C [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 2:57 PM To: [EMAIL PROTECTED] Subject: SQL query question Hello all, I am a totally newby to MYSQL but here i go. I want to create an ASP page that shows an image (screendump of game) a game name a game description and an url. I created a database with the following table: filename, urldesc, desc Now i have to create an SQL query that will put this information in a webpage: this is what i got. SELECT ' img border=0 src=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.gif /p td a href=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.zip ' urldesc '/a td p ' desc ' /p td ' FROM table1; The zip file for the download and the gif file for the image have the same filename exept the extention. It nearly works but it won't show all the text in the description. Any ideas? _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- 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
not really for mhsql list, more for php list but,,, select your database after connection. use echo statement to look at you queries. check your punctuation Roger -Original Message- From: Karl James [mailto:[EMAIL PROTECTED] Sent: Friday, March 21, 2003 6:51 PM To: [EMAIL PROTECTED] Subject: sql,query sql,query You have written the following: My code: http://nopaste.php-q.net/8594 My site: http://66.12.3.67/webdb/webdb13/assignment_1a.php My goal: http://66.12.3.67/webdb/webdb19/assign01/index.php == Can anyone help me get this resolved please? Thanks Karl ultimatefootballleague.com/index.php [EMAIL PROTECTED] ultimatefootballleague.com/index.php [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL QUERY
At 14:40 +0100 3/17/03, [EMAIL PROTECTED] wrote: Problem with the following SQL Query: How do I combine DISTINCT with the SELECT * ? Is it SELECT DISTINCT * FROM...? This doesn´t work in my project. SELECT DISTINCT * FROM ... should work. What is your exact query, and what error message do you see? -- Paul DuBois http://www.kitebird.com/ sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
Thank you all for your help. I think that is all I need to do is select it by row. -Original Message- From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 2:02 PM To: Christopher Lyon; MySQL Mailinglist Subject: Re: sql query using select and row functions On 1/28/03 12:26 PM, Christopher Lyon [EMAIL PROTECTED] wrote: I do have a timestamp field would I be better off using that? Granted it might not be the same amount of rows every time but how would that work? Select * from tbl_name where TIMESTAMP ( ) -3 hours; Well, that is not what you asked for you just wanted to get the last N records inserted into the database which is different from what you asked now. Your original question is best answered with having an autoincrement field in your table and do a: SELECT field FROM table WHERE whatever = don'tknow ORDER BY autoincrement_field DESC LIMIT N; Using the timestamp field to retrive all records inserted within the last n years/months/weeks/days/hours/minutes/second requires some string conversion of the timestamp. Your best bet in that case is to look into the Date and Time Functions: Check comments also! http://www.mysql.com/doc/en/DATETIME.html http://www.mysql.com/doc/en/Date_and_time_functions.html After reading through the excellent documentation it should be rather straight forward how to do it. Hth/h SQL, QUERY, TABLE P.s. I also strongly recommend to keep the discussion on the list for your benefit (there are much better experts on the mysql list than me) but also for the benefit of others with a similar problem (although there is a rather big lag from posting to appearing on the list). The list archives also getting way to little attention. -Original Message- From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 11:50 AM To: Christopher Lyon; MySQL Mailinglist Subject: Re: sql query using select and row functions On 1/28/03 8:26 AM, Christopher Lyon [EMAIL PROTECTED] wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? The easiest way is probably to add an ORDER BY field DESC into your SQL statement. If you use an autoincrement field you could use that otherwise add a field and make it TIMESTAMP. Hth/h - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
Why not just reverse your order by clause and use Limit 5? Mike Hillyer -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 9:26 AM To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql query using select and row functions
On 1/28/03 8:26 AM, Christopher Lyon [EMAIL PROTECTED] wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? The easiest way is probably to add an ORDER BY field DESC into your SQL statement. If you use an autoincrement field you could use that otherwise add a field and make it TIMESTAMP. Hth/h - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
Try ordering the records backwards, e.g ORDER BY id DESC and then limit 0, 5 HTH JFernando * sql * -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: January 28, 2003 11:26 To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
Do you mean the last five rows in the database or the last five rows entered into the database? Either way, Last five rows select [someColumn] from [someTable] ORDER BY [someColumn] DESC LIMIT 0, 5 If the total number of rows is known select [someColumn] from [someTable] ORDER BY [someColumn] LIMIT (totalNum - 5), -1 Or if you have a timestamp and you need the last five entered select [someColumn] from [someTable] ORDER BY timestamp DESC LIMIT 0, 5 I hope this helps. -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
I would think they would be the same no? It turns out in the database that they are the same. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 12:23 PM To: Christopher Lyon; [EMAIL PROTECTED] Subject: RE: sql query using select and row functions Do you mean the last five rows in the database or the last five rows entered into the database? Either way, Last five rows select [someColumn] from [someTable] ORDER BY [someColumn] DESC LIMIT 0, 5 If the total number of rows is known select [someColumn] from [someTable] ORDER BY [someColumn] LIMIT (totalNum - 5), -1 Or if you have a timestamp and you need the last five entered select [someColumn] from [someTable] ORDER BY timestamp DESC LIMIT 0, 5 I hope this helps. -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
In a relational database your data can be stored differently than the way the data is entered. ... If all is the same, one of the three examples should work for you. -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 2:28 PM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: sql query using select and row functions I would think they would be the same no? It turns out in the database that they are the same. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 12:23 PM To: Christopher Lyon; [EMAIL PROTECTED] Subject: RE: sql query using select and row functions Do you mean the last five rows in the database or the last five rows entered into the database? Either way, Last five rows select [someColumn] from [someTable] ORDER BY [someColumn] DESC LIMIT 0, 5 If the total number of rows is known select [someColumn] from [someTable] ORDER BY [someColumn] LIMIT (totalNum - 5), -1 Or if you have a timestamp and you need the last five entered select [someColumn] from [someTable] ORDER BY timestamp DESC LIMIT 0, 5 I hope this helps. -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql query using select and row functions
On 1/28/03 12:26 PM, Christopher Lyon [EMAIL PROTECTED] wrote: I do have a timestamp field would I be better off using that? Granted it might not be the same amount of rows every time but how would that work? Select * from tbl_name where TIMESTAMP ( ) -3 hours; Well, that is not what you asked for you just wanted to get the last N records inserted into the database which is different from what you asked now. Your original question is best answered with having an autoincrement field in your table and do a: SELECT field FROM table WHERE whatever = don'tknow ORDER BY autoincrement_field DESC LIMIT N; Using the timestamp field to retrive all records inserted within the last n years/months/weeks/days/hours/minutes/second requires some string conversion of the timestamp. Your best bet in that case is to look into the Date and Time Functions: Check comments also! http://www.mysql.com/doc/en/DATETIME.html http://www.mysql.com/doc/en/Date_and_time_functions.html After reading through the excellent documentation it should be rather straight forward how to do it. Hth/h SQL, QUERY, TABLE P.s. I also strongly recommend to keep the discussion on the list for your benefit (there are much better experts on the mysql list than me) but also for the benefit of others with a similar problem (although there is a rather big lag from posting to appearing on the list). The list archives also getting way to little attention. -Original Message- From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 11:50 AM To: Christopher Lyon; MySQL Mailinglist Subject: Re: sql query using select and row functions On 1/28/03 8:26 AM, Christopher Lyon [EMAIL PROTECTED] wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? The easiest way is probably to add an ORDER BY field DESC into your SQL statement. If you use an autoincrement field you could use that otherwise add a field and make it TIMESTAMP. Hth/h - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql query using select and row functions
On Tue, 2003-01-28 at 12:26, Christopher Lyon wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? SELECT * FROM blah-blah ORDER BY whatever DESC LIMIT 0,5 -- __ / \\ @ __ __@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / _/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: sql query LOAD DATA INFILE question
On Tuesday 17 December 2002 11:08, moka at hol dot gr wrote: I am looking at the following situation: I am reading some files arriving every minute and parsing them and creating a set of files ready to be inserted into tables. on the fly. While I am waiting for the next burst of files, I want to insert these into the tables, then erase the files. Normally LOAD DATA INFILE LOCAL works fine here. The problem is that the machine holding these tables is a different one. The question is, is it possible to run LOAD DATA INFILE to do the inserts on the remote machine, or is it better to first ftp the files over, then run LOAD DATA INFILE there? I assume LOAD INFILE is faster than via mysql -u user -ppasswd DBfile.sql where file.sql contains sql INSERT query From the LOAD DATA INFILE documentation it is not clear to me how this can be done, if it can be done. If you use LOAD DATA LOCAL INFILE, file must be located on the client box. So, you can load data to the remore server. ftp is faster, more reliable, but LOAD DATA LOCAL is easier to use and cheaper. Besides, to use LOAD DATA you should have FILE privilege. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Query
While not pretty the following would do it SUBSTRING_INDEX(SUBSTRING_INDEX(value, ',', 3), ',', -1) John W Higgins [EMAIL PROTECTED] -Original Message- From: Paul van Brouwershaven [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 16, 2002 5:46 AM To: [EMAIL PROTECTED] Subject: SQL Query Hi, I have a colum with this values : ,1,4,5,66,247,7, ,1,3,5,62,767,6, ,1,5,5,11 ,1,9,5,36,7677,9, ,1,40,55,66,444,3, I want to get whis values : (second field) 4 3 5 9 40 I have tried this : REPLACE(SUBSTRING_INDEX(value, ',', 3), ',','') But the following is returned : 14 13 15 19 140 I have alse tried the following query : select mid(path,locate(2,path,,)+1,(locate(locate(2,path,,)+1,path,,)-loc ate(2,path,,)-1)) from path_table Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Query
Gurhan, The outlined method is unnecessary if you are using Mysql. The same output can be achieved by using, select @a:=substring_index(val,',',3), substring_index(@a,',',-1) as value_i_want from test; Richard. - Original Message - From: Gurhan Ozen [EMAIL PROTECTED] To: Paul van Brouwershaven [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, November 16, 2002 4:50 PM Subject: Re: SQL Query First of all, don't do this in mysql . If you got a dump of the database, using cut utility u can easily extract the second field in the delimited by the comma.. and then split them into different columns in the table when you want to insert them into the mysql database. If you are still looking for something to do in mysql, you can do it with a little work around.. You will have to create a different table with my solution, and you can create it as temporary table.. CREATE TABLE your_new_table SELECT SUBSTRING_INDEX(a1,',',3) AS cut_value FROM your_original_table; This will create a new table and if you do a select * on it you will see: +---+ | cut_value | +---+ | ,1,4 | | ,1,3 | | ,1,5 | | ,1,9 | | ,1,40 | +---+ 5 rows in set (0.23 sec) and in this table, you can do: SELECT SUBSTRING_INDEX(cut_value,',',-1) AS value_i_want FROM your_new_table; And it will give you: +--+ | value_i_want | +--+ | 4| | 3| | 5| | 9| | 40 | +--+ 5 rows in set (0.00 sec) Hope this helps.. Gurhan On Sat, 2002-11-16 at 08:45, Paul van Brouwershaven wrote: Hi, I have a colum with this values : ,1,4,5,66,247,7, ,1,3,5,62,767,6, ,1,5,5,11 ,1,9,5,36,7677,9, ,1,40,55,66,444,3, I want to get whis values : (second field) 4 3 5 9 40 I have tried this : REPLACE(SUBSTRING_INDEX(value, ',', 3), ',','') But the following is returned : 14 13 15 19 140 I have alse tried the following query : select mid(path,locate(2,path,,)+1,(locate(locate(2,path,,)+1,path,,)-loc ate(2,path,,)-1)) from path_table Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Query
Why not just split that field up into multiple fields. Seems ridiculous to have multiple values in a single field in a RDMS -Peter -Original Message- From: Paul van Brouwershaven [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 16, 2002 14:46 To: [EMAIL PROTECTED] Subject: SQL Query Hi, I have a colum with this values : ,1,4,5,66,247,7, ,1,3,5,62,767,6, ,1,5,5,11 ,1,9,5,36,7677,9, ,1,40,55,66,444,3, I want to get whis values : (second field) 4 3 5 9 40 I have tried this : REPLACE(SUBSTRING_INDEX(value, ',', 3), ',','') But the following is returned : 14 13 15 19 140 I have alse tried the following query : select mid(path,locate(2,path,,)+1,(locate(locate(2,path,,)+1,pat h,,)-loc ate(2,path,,)-1)) from path_table Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-peter.thoenen=bondsteel2.areur.army.mil@lis ts.mysql.com Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Query
I'ts a dump of an other database with more than 4 million records -Original Message- From: Thoenen, Peter Mr. EPS [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 16, 2002 2:54 PM To: 'Paul van Brouwershaven'; [EMAIL PROTECTED] Subject: RE: SQL Query Why not just split that field up into multiple fields. Seems ridiculous to have multiple values in a single field in a RDMS -Peter -Original Message- From: Paul van Brouwershaven [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 16, 2002 14:46 To: [EMAIL PROTECTED] Subject: SQL Query Hi, I have a colum with this values : ,1,4,5,66,247,7, ,1,3,5,62,767,6, ,1,5,5,11 ,1,9,5,36,7677,9, ,1,40,55,66,444,3, I want to get whis values : (second field) 4 3 5 9 40 I have tried this : REPLACE(SUBSTRING_INDEX(value, ',', 3), ',','') But the following is returned : 14 13 15 19 140 I have alse tried the following query : select mid(path,locate(2,path,,)+1,(locate(locate(2,path,,)+1,pat h,,)-loc ate(2,path,,)-1)) from path_table Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-peter.thoenen=bondsteel2.areur.army.mil@lis ts.mysql.com Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Query
- Original Message - From: Paul van Brouwershaven [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 16, 2002 8:58 AM Subject: RE: SQL Query I'ts a dump of an other database with more than 4 million records Yeah, but that still doesn't mean that you can't use the earlier suggestion. You should pull the records, split the data based on the comma and then insert them individually into new fields and then run your query on the new table. Good Luck! Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Query
The number of values is also not the same, this can be 1 till +/-30 values -Original Message- From: Dennis Salguero [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 16, 2002 1:46 PM To: Paul van Brouwershaven; [EMAIL PROTECTED] Subject: Re: SQL Query - Original Message - From: Paul van Brouwershaven [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 16, 2002 8:58 AM Subject: RE: SQL Query I'ts a dump of an other database with more than 4 million records Yeah, but that still doesn't mean that you can't use the earlier suggestion. You should pull the records, split the data based on the comma and then insert them individually into new fields and then run your query on the new table. Good Luck! Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Query
Sorry it's default -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 16, 2002 5:23 PM To: Paul van Brouwershaven Subject: Re: SQL Query Please do NOT mark posted messages 'request reply'. - - Original Message - From: Paul van Brouwershaven [EMAIL PROTECTED] To: 'Dennis Salguero' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, November 16, 2002 8:17 AM Subject: RE: SQL Query The number of values is also not the same, this can be 1 till +/-30 values -Original Message- From: Dennis Salguero [mailto:[EMAIL PROTECTED]] Sent: Saturday, November 16, 2002 1:46 PM To: Paul van Brouwershaven; [EMAIL PROTECTED] Subject: Re: SQL Query - Original Message - From: Paul van Brouwershaven [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 16, 2002 8:58 AM Subject: RE: SQL Query I'ts a dump of an other database with more than 4 million records Yeah, but that still doesn't mean that you can't use the earlier suggestion. You should pull the records, split the data based on the comma and then insert them individually into new fields and then run your query on the new table. Good Luck! Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Query
First of all, don't do this in mysql . If you got a dump of the database, using cut utility u can easily extract the second field in the delimited by the comma.. and then split them into different columns in the table when you want to insert them into the mysql database. If you are still looking for something to do in mysql, you can do it with a little work around.. You will have to create a different table with my solution, and you can create it as temporary table.. CREATE TABLE your_new_table SELECT SUBSTRING_INDEX(a1,',',3) AS cut_value FROM your_original_table; This will create a new table and if you do a select * on it you will see: +---+ | cut_value | +---+ | ,1,4 | | ,1,3 | | ,1,5 | | ,1,9 | | ,1,40 | +---+ 5 rows in set (0.23 sec) and in this table, you can do: SELECT SUBSTRING_INDEX(cut_value,',',-1) AS value_i_want FROM your_new_table; And it will give you: +--+ | value_i_want | +--+ | 4| | 3| | 5| | 9| | 40 | +--+ 5 rows in set (0.00 sec) Hope this helps.. Gurhan On Sat, 2002-11-16 at 08:45, Paul van Brouwershaven wrote: Hi, I have a colum with this values : ,1,4,5,66,247,7, ,1,3,5,62,767,6, ,1,5,5,11 ,1,9,5,36,7677,9, ,1,40,55,66,444,3, I want to get whis values : (second field) 4 3 5 9 40 I have tried this : REPLACE(SUBSTRING_INDEX(value, ',', 3), ',','') But the following is returned : 14 13 15 19 140 I have alse tried the following query : select mid(path,locate(2,path,,)+1,(locate(locate(2,path,,)+1,path,,)-loc ate(2,path,,)-1)) from path_table Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Query
Hi, If the lenght of what wish to retrieve is fixed to 2 length,you can try something like this: select MID(YOUR_FIELD,3,IF(RIGHT(LPAD(YOUR_FIELD,4,','),1)=',',1,2)) from YOUR_TABLE; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Paul van Brouwershaven [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 16, 2002 3:45 PM Subject: SQL Query Hi, I have a colum with this values : ,1,4,5,66,247,7, ,1,3,5,62,767,6, ,1,5,5,11 ,1,9,5,36,7677,9, ,1,40,55,66,444,3, I want to get whis values : (second field) 4 3 5 9 40 I have tried this : REPLACE(SUBSTRING_INDEX(value, ',', 3), ',','') But the following is returned : 14 13 15 19 140 I have alse tried the following query : select mid(path,locate(2,path,,)+1,(locate(locate(2,path,,)+1,path,,)-loc ate(2,path,,)-1)) from path_table Regards, Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Query
Is this line correct? MarketData INNER JOIN Contacts on MarketData.CustID=Contacts.ContactID WHERE Or should it be: MarketData INNER JOIN Contacts on MarketData.CustID=Contacts.CustID WHERE JFernando ** sql ** -Original Message- From: [EMAIL PROTECTED] [mailto:Sam4Software;aol.com] Sent: November 7, 2002 16:18 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: SQL Query Hi, I have the following SQL query, that returns the correct records on Access, but when I use it with MySQL, it returns duplicate records, and it skips the required records. SearchSQL=select MarketData.CustID,Contacts.ContactID,Contacts.CustID,MarketData.Nickname,Mar ke tData.Occupation,MarketData.Country ,County,MarketData.Forename,MarketData.Surname,MarketData.Email FROM MarketData INNER JOIN Contacts on MarketData.CustID=Contacts.ContactID WHERE Contacts.CustID=6115 Regards, Sam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Query
On Thu, 2002-11-07 at 16:17, [EMAIL PROTECTED] wrote: Hi, I have the following SQL query, that returns the correct records on Access, but when I use it with MySQL, it returns duplicate records, and it skips the required records. SearchSQL=select MarketData.CustID,Contacts.ContactID,Contacts.CustID,MarketData.Nickname,Marke tData.Occupation,MarketData.Country ,County,MarketData.Forename,MarketData.Surname,MarketData.Email FROM MarketData INNER JOIN Contacts on MarketData.CustID=Contacts.ContactID WHERE Contacts.CustID=6115 I personally would try: SearchSQL=select MarketData.CustID,Contacts.ContactID,Contacts.CustID,MarketData.Nickname, MarketData.Occupation,MarketData.Country,County,MarketData.Forename, MarketData.Surname,MarketData.Email FROM MarketData INNER JOIN Contacts WHERE MarketData.CustID=Contacts.ContactID AND Contacts.CustID=6115 John Coder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Query Help
On Fri, Oct 04, 2002 at 12:36:30PM -0700, David McInnis wrote: Can someone please help me with the following? Normally I would do this with a nested select, but since this is not available in MySQL I think I need help. Here is what I have: An order table with sales tax total and an orderdetail table with ordered, itemid and qty. What I need to do is form a sql query that will allow me to pull get the tax amount on all orders where product id is 1, 2 or 3 for example. The problem that I have is when I do a straight join on select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. I'm not certain if I understand what you're after... If you want total tax per order, try select sum(tax) as tax, orders.id from orders, orderdetail where orders.id = orderdetail.orderid and productid in (1,2,3) group by orderid; Otherwise, please clarify what you want in your desired result set. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
