Re: SELECT WHERE IN help
If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a table. I think in this case I need to change my design . On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote: I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
On 21/09/2010 16:44, Tompkins Neil wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? No, that isn't possible. Why do you want a duplicate record to be retrieved? There may be a better way of doing it. Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT WHERE IN help
Hi Neil, all! Tompkins Neil wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? This is a case where you may safely use natural language and logic. The command is SELECT all fields FROM the records in mytable FOR WHICH THE FOLLOWING CONDITION IS TRUE: the field record_id has a value which is IN the list 3, 4, 5, 6, 7, 3 The condition can only evaluate to true or false (ignoring NULL values and the unknown truth value for now), and for that evaluation it does not matter whether a matching value appears in your list only once or repeatedly. To achieve your desired effect, you might use a generator to create a UNION statement. Roughly, the approach would be (+= means appending to a string): value = first value of the list; statement = SELECT * FROM my_table WHERE record_id = $value; while (there are more values in the list) do value = next value of the list; statement += UNION SELECT * FROM my_table WHERE record_id = $value; done; statement += ;; execute statement; Obviously, this will create a huge statement if the value list is long, and it doesn't seem to be efficient, so I don't recommend this technique in general. Before going that route, you should question your assumptions: Why is it necessary to return the same record twice? Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SELECT WHERE IN help
-Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, September 21, 2010 11:48 AM To: Johan De Meersman Cc: [MySQL] Subject: Re: SELECT WHERE IN help Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a table. I think in this case I need to change my design . [JS] You can accomplish your goal by using a sub-select to create a table that has 3 in it twice, and then JOIN it to the original table. As for why you would want to do this, that's another story. It sounds like you went down the wrong road. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote: I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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: Select where the column names are not fully known
Your best bet would be to dynamically build the field string using whatever you are coding the front end on. A simple configuration paramter would tell you how many fields there are. Now, if you don't need to retain your current table structure, I would recommend switching it to a name/value pairing table. Essentially orienting your data vertical, in rows, instead of horizontal in columns. One way would be to have 3 columns: type,seqno,val. Type would be vectore, scalar, etc. Seqno would just be the number you assign and the val would be the val of that item. This allows you to easily select all values of a particular type. This also allows you to index everything, so searching on all vectors greater than x would be very fast. It also automatically adjusts to the contract. - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 11, 2007 5:00 AM Subject: Select where the column names are not fully known We have a logging package that logs data into a table. Part of the data that is logged are elements of a vector. A logging application automatically takes out the elemets of the vector and automatically creates column names based on it's name and the element. This data will be mixed in with lots of other scalar data, but the elements will be created next to each other. For example, I may have a table with column names like this: datetime scalar1 scalar2 scalar3 vector_1 vector_2 vector_3 vector_4 scalar4 scalar5 etc etc. I reality there is a lot more data than this. I neet to be able to select only the items of the vector - like this: select vector_1,vector_2,vector_4,vector_4 from mytable where datetime=somedatetime. So far so good. However the problem is that the vector size can vary from contract to contract. I don't want to have to change the select command for each contract we do. The format of the column name is always itemname_x where x starts at 0. So I'd like to do something like select vector_* from mytable where datetime=somedatetime. but of course this doesn't work. Anyone any ideas how I can do this? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select where the column names are not fully known
I don't know if this can be done purely in SQL. Programmatically, you could do something like SHOW COLUMNS FROM tablename LIKE vector%; That would give you the names (and types, which you can ignore) of the desired columns. Then you can programmatically build up the list of columns. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 11, 2007 5:01 AM To: mysql@lists.mysql.com Subject: Select where the column names are not fully known We have a logging package that logs data into a table. Part of the data that is logged are elements of a vector. A logging application automatically takes out the elemets of the vector and automatically creates column names based on it's name and the element. This data will be mixed in with lots of other scalar data, but the elements will be created next to each other. For example, I may have a table with column names like this: datetime scalar1 scalar2 scalar3 vector_1 vector_2 vector_3 vector_4 scalar4 scalar5 etc etc. I reality there is a lot more data than this. I neet to be able to select only the items of the vector - like this: select vector_1,vector_2,vector_4,vector_4 from mytable where datetime=somedatetime. So far so good. However the problem is that the vector size can vary from contract to contract. I don't want to have to change the select command for each contract we do. The format of the column name is always itemname_x where x starts at 0. So I'd like to do something like select vector_* from mytable where datetime=somedatetime. but of course this doesn't work. Anyone any ideas how I can do this? Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select ... where bigint_col = 'big_value' doesn't work?
-Original Message- From: Ephraim Dan [mailto:[EMAIL PROTECTED] Sent: 27 February 2005 15:44 To: mysql@lists.mysql.com Subject: select ... where bigint_col = 'big_value' doesn't work? Hi, We are migrating to mysql 4.1 (from 4.0). We have lots of BIGINT columns, and lots of application code that have queries like the following: select ... where bigint_col = '64-bit int value' I personally have never bothered using the quotes around int numbers but I know someone who had to change every single MySQL query to fix the problem (taking out the quotes). I personally like the old MySQL 4.0xx In 4.0, this worked fine. In 4.1 it does not work. The query only works if you take the single-quotes away. I could not find an explanation in the docs. Is this a bug or expected behavior? Is there a workaround besides changing all our code? Thanks, Ephraim Dan Chris -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.5.0 - Release Date: 25/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
AM Thomas wrote: Hi there, I'll answer your questions below, but I should tell you that it looks like even three or four joins seem to make this too slow. Theoretically, the user could select up to 15 or so GoalNumber values, 12 for Grade, and about 20 possibilities for Subject - clearly it wouldn't be useful to specify that many items in a search, but that's the theoretical maximum; 4 or 5 values isn't unreasonable, though. Four ANDed goal numbers plus a subject and a grade slowed the search (on the shared commercial web host I'm using) into the 3 minute range, and that's with a regular join, not a left join. This is the SELECT that took about 3 minutes (3 trials, simplifying slightly each time, simplest given here): You've removed necessary conditions on the JOINs, so you are getting lots of extra rows. Furthermore, the logic isn't right, so I think this query will, in all likelihood, retrieve incorrect rows. I'll explain. For a given resource id, select r.id from resources as r join resource_goals as g0 on (r.id=g0.ResourceID) (adding the WHERE clause below), this retrieves every row in copy 0 with the right subject and grade, *regardless of goal* join resource_goals as g1 on (r.id=g1.ResourceID and g1.GoalNumber=1) and pairs it with every row in copy 1 with GoalNumber=1, *regardless of Subject or Grade* join resource_goals as g2 on (r.id=g2.ResourceID and g2.GoalNumber=2) and pairs it with every row in copy 2 with GoalNumber=2, *regardless of Subject or Grade* join resource_goals as g3 on (r.id=g3.ResourceID and g3.GoalNumber=3) and pairs it with every row in copy 3 with GoalNumber=3, *regardless of Subject or Grade* join resource_goals as g4 on (r.id=g4.ResourceID and g4.GoalNumber=4) and pairs it with every row in copy 4 with GoalNumber=4, *regardless of Subject or Grade* where ((g0.Subject='Social_Studies') and (g0.Grade='4th')) group by r.id; and finally, we pick one of those many rows to display (the effect of the GROUP BY r.id). Do you see why that is both more than and different from what you want? Consider a resource with the following rows in resource_goals: ++---+++--+ | ResourceID | Grade | Subject| GoalNumber | NumericGrade | ++---+++--+ | 14 | 4th | Social_Studies | 7 |4 | | 14 | 1st | English| 1 |1 | | 14 | 2nd | English| 2 |2 | | 14 | 3rd | English| 3 |3 | | 14 | 5th | History| 4 |5 | ++---+++--+ ResourceID 14 would be returned by your query, but isn't what you want. I'd guess you haven't come across a case like this because you have few, if any, cross-subject resources, but I assume they are a possibility, since you have Subject part of the resource_goals table, rather than part of the resources table. The fastest time was 2 min 48 sec. Last time (simplest query) was 3 min 2 sec. If we join each row in resources to a single row in each copy of resource_goals using an index, this should be reasonably fast, but I expect you are getting multiple matching rows in each copy, as it stands now. The total resulting rows per id is the product of the matches in each copy. If just 3 rows match your current conditions per copy, that would be 3^5 = 243 rows per resource id, where we expect only 1! In other words, I expect some of the slow down is due to the overhead of retrieving many times the number of desired rows. The rest is probably lack of a suitable index. As I understand it, you are looking for a resource for 4th grade Social Studies which meets goals 1 through 4. In terms of your tables, that corresponds to having 4 rows in resources_goals, *all* of which have grade=4th and Subject='Social_Studies'. That is, we need to look in *4* copies of resources_goals (not 5). For a given resource id, we want exactly one row from each copy, namely, the row with the correct resource id, correct subject, correct grade, and desired goal number. I think this should do: SELECT r.id FROM resources as r JOIN resource_goals as g1 ON r.id = g1.ResourceID AND g1.Subject = 'Social_Studies' AND g1.Grade = '4th' AND g1.GoalNumber = 1 JOIN resource_goals as g2 ON r.id = g2.ResourceID AND g2.Subject = 'Social_Studies' AND g2.Grade = '4th' AND g2.GoalNumber = 2 JOIN resource_goals as g3 ON r.id = g3.ResourceID AND g3.Subject = 'Social_Studies' AND g3.Grade = '4th' AND g3.GoalNumber = 3 JOIN resource_goals as g4 ON r.id = g4.ResourceID AND g4.Subject = 'Social_Studies' AND g4.Grade = '4th' AND g4.GoalNumber = 4; That should return 1 row per resource, so long as there are no duplicates rows in
Re: select where multiple joined records match
Such bounty of comments! Thank you, Michael Stassen. I see how my logic was faulty, and that a more correct solution would indeed be faster. Thanks for pointing that out and not making me feel like too much of an idiot :) I'll try the revised solution. I am clueful about join making more rows/record, but didn't realize that it would be *that* dramatic. As for the NumericGrade field, I'm basically getting the text grade (e.g. 8th) from someone's MS Access CSV export; I figured I'd leave it in place, and use the numeric grade for sorting. I'd never run into speed issues before, so I was just trying to save myself coding time by sticking with what I had already. I wouldn't need the special lookup tables for grade and subject; if I were to use numeric fields, I could just do a lookup in Perl. Next time, when I try to do this better from the start, I probably will. At the moment, though, I'm trying to avoid changing the Perl code as much as I can (it's much recycled from an earlier project). Will look into indexing - that's probably covered in my old O'Reilly MySQL/mSQL book. I am curious about how much faster numeric field comparisons would be to string field comparisons for the Grade field; Would it make enough of a difference to this problem for me to go mucking with this Perl code? This is a CGI Web app, so it's not lightning fast anyway, but then there are a lot of comparisons going on. Currently it looks like it will have about 300 resource records and about six thousand resource_goal records; I should test this myself... I do have a lot of multi-subject and multi-grade resources; my testing was just not very good, I think. Oh, and I think I see the error of my ways with regard to my TINYTEXT fields. Probably would do well to shrink those. Easy to change, too. Thanks! You've given excellent explanations here. I feel like I should buy your book now, if you have one! I'm wrestling with CSS issues on IE 4.0 for the Mac at the moment, but will return to SQL issues soon, I hope. More later probably, AM On Mon, 21 Feb 2005 14:30:59 -0500, Michael Stassen [EMAIL PROTECTED] wrote: As I understand it, you are looking for a resource for 4th grade Social Studies which meets goals 1 through 4. In terms of your tables, that corresponds to having 4 rows in resources_goals, *all* of which have grade=4th and Subject='Social_Studies'. That is, we need to look in *4* copies of resources_goals (not 5). For a given resource id, we want exactly one row from each copy, namely, the row with the correct resource id, correct subject, correct grade, and desired goal number. I think this should do: SELECT r.id FROM resources as r JOIN resource_goals as g1 ON r.id = g1.ResourceID AND g1.Subject = 'Social_Studies' AND g1.Grade = '4th' AND g1.GoalNumber = 1 JOIN resource_goals as g2 ON r.id = g2.ResourceID AND g2.Subject = 'Social_Studies' AND g2.Grade = '4th' AND g2.GoalNumber = 2 JOIN resource_goals as g3 ON r.id = g3.ResourceID AND g3.Subject = 'Social_Studies' AND g3.Grade = '4th' AND g3.GoalNumber = 3 JOIN resource_goals as g4 ON r.id = g4.ResourceID AND g4.Subject = 'Social_Studies' AND g4.Grade = '4th' AND g4.GoalNumber = 4; -- Virtue of the Small / (919) 929-8687 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
I am guessing the long duration is caused by having to do complete table scans. How big is your dataset? What about creating another index in resource_goals that includes GoalNumber and ResourceID? Perhaps even Subject, Grade, and NumericGrade As I learned just a couple days ago, making sure your JOIN conditions and WHERE clause can refer to an index can speed up queries 1000x or more. Try tacking an EXPLAIN before your select and see how many rows MySQL things are being examined... optimally these should be very low, which indexes may be able to help with. Thanks, Mathew AM Thomas wrote: Hi there, I'll answer your questions below, but I should tell you that it looks like even three or four joins seem to make this too slow. Theoretically, the user could select up to 15 or so GoalNumber values, 12 for Grade, and about 20 possibilities for Subject - clearly it wouldn't be useful to specify that many items in a search, but that's the theoretical maximum; 4 or 5 values isn't unreasonable, though. Four ANDed goal numbers plus a subject and a grade slowed the search (on the shared commercial web host I'm using) into the 3 minute range, and that's with a regular join, not a left join. This is the SELECT that took about 3 minutes (3 trials, simplifying slightly each time, simplest given here): select r.id from resources as r join resource_goals as g0 on (r.id=g0.ResourceID) join resource_goals as g1 on (r.id=g1.ResourceID and g1.GoalNumber=1) join resource_goals as g2 on (r.id=g2.ResourceID and g2.GoalNumber=2) join resource_goals as g3 on (r.id=g3.ResourceID and g3.GoalNumber=3) join resource_goals as g4 on (r.id=g4.ResourceID and g4.GoalNumber=4) where ((g0.Subject='Social_Studies') and (g0.Grade='4th')) group by r.id; The fastest time was 2 min 48 sec. Last time (simplest query) was 3 min 2 sec. I'm really running out of time on this project, so I just went ahead and made the user interface such that users can only select one subject, grade, and/or goal number at a time. It's probably a sound decision from a usability perspective, so I'm not too sad. If I decide to make this work in the future, I'd probably have to just do a SELECT for each ANDed field, get the list of resource id's for each SELECT, then find the intersection of the lists in Perl. If I could speed this up with some kind of indexing, I'd love to know about it. The GROUP BY phrase is because I wanted just one row per resource. It seemed like I'd get a row for each condition/resource (didn't test it with the final ). I'm actually doing SELECT * FROM... in my code, and not using the resource_goals information in my output (that's a separate view at present, generated by different Perl code). It seems to work fine without the NOT NULL parts, you're right. I was wondering about that, but was sleepy enough at the time that I didn't trust my thinking. Sorry about not including my table defs :-( . I guess I was just hoping for a general approach, and didn't realize that anyone would be interested enough to read all that detail and provide and exact solution for me. Of course, now I realize that it would have simplified our discussion. Anyway, late but not never, and for help to whoever finds this in the list archives someday, here are my table defs (you're right - the goal number is a TINYINT): (Below is an abridged version of the resources table ; it also contains about 60 more TINYINT fields which are essentially used as booleans, some of which I hope to eliminate. Yes, I could have used SET or something, but I didn't for various reasons.) CREATE TABLE resources ( id INT UNSIGNED PRIMARY KEY, Title TEXT, ResourceType_THJHArticle TINYINT, ResourceType_NIEArticle TINYINT, DataEntryName TINYTEXT, Date DATETIME, Notes TEXT, Made_Keywords TEXT); CREATE TABLE resource_goals ( goal_id INT UNSIGNED PRIMARY KEY, ResourceID INT, Grade TINYTEXT, Subject TINYTEXT, GoalNumber TINYINT, NumericGrade TINYINT); Thanks a bunch for your help; I'm finding this more interesting than I thought I would. On Wed, 16 Feb 2005 11:08:20 -0500, Michael Stassen [EMAIL PROTECTED] wrote: AM Thomas wrote: Guarded exclamations of success! This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plain 'join' instead of 'left join', which seems to make it much faster. It was pretty slow at first. Yes, LEFT JOIN does extra work, and it wasn't needed here. mysql select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from resources as r join resource_goals as ga on r.id=ga.ResourceID and ga.Grade='4th' and ga.GoalNumber='1' join resource_goals as gb on r.id=gb.ResourceID and gb.Grade='4th' and
Re: select where multiple joined records match
Jeremy Cole wrote: Hi, Thanks, but unless I'm missing something, that will return the same as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? How about this: SELECT resources.id, resources.title FROM resources LEFT JOIN goals AS goal_a ON resources.id=goal_a.resource_id AND goal_a.subject=English AND goal_a.grade=1 LEFT JOIN goals AS goal_b ON resources.id=goal_b.resource_id AND goal_b.subject=English AND goal_b.grade=2 WHERE goal_a.id IS NOT NULL AND goal_b.id IS NOT NULL snip There's no need for a LEFT JOIN here. The difference between LEFT JOIN and JOIN is that LEFT JOIN creates extra NULL rows for the table on the right whenever it doesn't have a match for the table on the left. We don't need that here. Indeed, you throw those NULL rows away with your WHERE clause. It's more efficient not to create them in the first place if they're not needed. In general, any time you write ...LEFT JOIN table_on_right ... WHERE table_on_right.some_col IS NOT NULL you should probably just use a JOIN instead. Hence, this query would be better as SELECT resources.id, resources.title FROM resources JOIN goals AS goal_a ON resources.id = goal_a.resource_id AND goal_a.subject = English AND goal_a.grade = 1 JOIN goals AS goal_b ON resources.id = goal_b.resource_id AND goal_b.subject = English AND goal_b.grade = 2 Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
AM Thomas wrote: Guarded exclamations of success! This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plain 'join' instead of 'left join', which seems to make it much faster. It was pretty slow at first. Yes, LEFT JOIN does extra work, and it wasn't needed here. mysql select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from resources as r join resource_goals as ga on r.id=ga.ResourceID and ga.Grade='4th' and ga.GoalNumber='1' join resource_goals as gb on r.id=gb.ResourceID and gb.Grade='4th' and gb.GoalNumber='2' where ga.goal_id IS NOT NULL and gb.goal_id IS NOT NULL group by r.id; There are some strange things here, I think. * You've never shown us your table definitions, but I would have expected GoalNumber to be an integer, not a string. If so, you shouldn't quote the numbers you compare it to. * I think it unlikely that you have rows with values in Grade, GoalNumber, and ResourceID which have NULL for goal_id. Isn't goal_id the primary key? If I'm right, you don't need your WHERE clause. * Why have you added GROUP BY r.id? Was that an attempt to fix something? If we've got the query right, there should be no need for grouping. If you don't get the result you want without the GROUP BY, then you should let us know, because that would mean we've missed something. Putting those together, I'd expect SELECT r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber FROM resources AS r JOIN resource_goals AS ga ON r.id = ga.ResourceID AND ga.Grade = '4th' AND ga.GoalNumber = 1 JOIN resource_goals AS gb ON r.id = gb.ResourceID AND gb.Grade = '4th' AND gb.GoalNumber = 2 to do the job. Does it? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Hi there, I'll answer your questions below, but I should tell you that it looks like even three or four joins seem to make this too slow. Theoretically, the user could select up to 15 or so GoalNumber values, 12 for Grade, and about 20 possibilities for Subject - clearly it wouldn't be useful to specify that many items in a search, but that's the theoretical maximum; 4 or 5 values isn't unreasonable, though. Four ANDed goal numbers plus a subject and a grade slowed the search (on the shared commercial web host I'm using) into the 3 minute range, and that's with a regular join, not a left join. This is the SELECT that took about 3 minutes (3 trials, simplifying slightly each time, simplest given here): select r.id from resources as r join resource_goals as g0 on (r.id=g0.ResourceID) join resource_goals as g1 on (r.id=g1.ResourceID and g1.GoalNumber=1) join resource_goals as g2 on (r.id=g2.ResourceID and g2.GoalNumber=2) join resource_goals as g3 on (r.id=g3.ResourceID and g3.GoalNumber=3) join resource_goals as g4 on (r.id=g4.ResourceID and g4.GoalNumber=4) where ((g0.Subject='Social_Studies') and (g0.Grade='4th')) group by r.id; The fastest time was 2 min 48 sec. Last time (simplest query) was 3 min 2 sec. I'm really running out of time on this project, so I just went ahead and made the user interface such that users can only select one subject, grade, and/or goal number at a time. It's probably a sound decision from a usability perspective, so I'm not too sad. If I decide to make this work in the future, I'd probably have to just do a SELECT for each ANDed field, get the list of resource id's for each SELECT, then find the intersection of the lists in Perl. If I could speed this up with some kind of indexing, I'd love to know about it. The GROUP BY phrase is because I wanted just one row per resource. It seemed like I'd get a row for each condition/resource (didn't test it with the final ). I'm actually doing SELECT * FROM... in my code, and not using the resource_goals information in my output (that's a separate view at present, generated by different Perl code). It seems to work fine without the NOT NULL parts, you're right. I was wondering about that, but was sleepy enough at the time that I didn't trust my thinking. Sorry about not including my table defs :-( . I guess I was just hoping for a general approach, and didn't realize that anyone would be interested enough to read all that detail and provide and exact solution for me. Of course, now I realize that it would have simplified our discussion. Anyway, late but not never, and for help to whoever finds this in the list archives someday, here are my table defs (you're right - the goal number is a TINYINT): (Below is an abridged version of the resources table ; it also contains about 60 more TINYINT fields which are essentially used as booleans, some of which I hope to eliminate. Yes, I could have used SET or something, but I didn't for various reasons.) CREATE TABLE resources ( id INT UNSIGNED PRIMARY KEY, Title TEXT, ResourceType_THJHArticle TINYINT, ResourceType_NIEArticle TINYINT, DataEntryName TINYTEXT, Date DATETIME, Notes TEXT, Made_Keywords TEXT); CREATE TABLE resource_goals ( goal_id INT UNSIGNED PRIMARY KEY, ResourceID INT, Grade TINYTEXT, Subject TINYTEXT, GoalNumber TINYINT, NumericGrade TINYINT); Thanks a bunch for your help; I'm finding this more interesting than I thought I would. On Wed, 16 Feb 2005 11:08:20 -0500, Michael Stassen [EMAIL PROTECTED] wrote: AM Thomas wrote: Guarded exclamations of success! This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plain 'join' instead of 'left join', which seems to make it much faster. It was pretty slow at first. Yes, LEFT JOIN does extra work, and it wasn't needed here. mysql select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from resources as r join resource_goals as ga on r.id=ga.ResourceID and ga.Grade='4th' and ga.GoalNumber='1' join resource_goals as gb on r.id=gb.ResourceID and gb.Grade='4th' and gb.GoalNumber='2' where ga.goal_id IS NOT NULL and gb.goal_id IS NOT NULL group by r.id; There are some strange things here, I think. * You've never shown us your table definitions, but I would have expected GoalNumber to be an integer, not a string. If so, you shouldn't quote the numbers you compare it to. * I think it unlikely that you have rows with values in Grade, GoalNumber, and ResourceID which have NULL for goal_id. Isn't goal_id the primary key? If I'm right, you don't need your WHERE clause. * Why have you added GROUP BY r.id? Was that an attempt to fix something? If
RE: select where multiple joined records match
Try this Select * from resources, goals where resources.ID = goals.RESOURCE_ID and (SUBJECT=English and GRADE=1) OR (SUBJECT=English and GRADE=2); -Original Message- From: AM Thomas [mailto:[EMAIL PROTECTED] Sent: Sunday, February 13, 2005 7:23 AM To: mysql@lists.mysql.com Subject: select where multiple joined records match I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. My MySQL is version 4.0.23a, if that makes a difference. Here's a simplified version of my problem. I have two tables, resources and goals. resources table: ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes goals table: ID RESOURCE_ID GRADE SUBJECT 1 11 English 2 11 Soc 3 12 English 4 21 English 5 23 Soc 6 32 English 7 41 English Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do: Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT=English) and ((GRADE=1) and (GRADE=2))); I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match. My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on. I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL mSQL book isn't doing the trick. Surely this has come up before - thanks for any guidance. - AM Thomas -- Virtue of the Small / (919) 929-8687 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Thanks, but unless I'm missing something, that will return the same as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? - AM On Mon, 14 Feb 2005 12:30:44 -0600, Gordon [EMAIL PROTECTED] wrote: Try this Select * from resources, goals where resources.ID = goals.RESOURCE_ID and (SUBJECT=English and GRADE=1) OR (SUBJECT=English and GRADE=2); -Original Message- From: AM Thomas [mailto:[EMAIL PROTECTED] Sent: Sunday, February 13, 2005 7:23 AM To: mysql@lists.mysql.com Subject: select where multiple joined records match I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. My MySQL is version 4.0.23a, if that makes a difference. Here's a simplified version of my problem. I have two tables, resources and goals. resources table: ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes goals table: ID RESOURCE_ID GRADE SUBJECT 1 11 English 2 11 Soc 3 12 English 4 21 English 5 23 Soc 6 32 English 7 41 English Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do: Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT=English) and ((GRADE=1) and (GRADE=2))); I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match. My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on. I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL mSQL book isn't doing the trick. Surely this has come up before - thanks for any guidance. - AM Thomas -- Virtue of the Small / (919) 929-8687 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Hi, Thanks, but unless I'm missing something, that will return the same as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? How about this: SELECT resources.id, resources.title FROM resources LEFT JOIN goals AS goal_a ON resources.id=goal_a.resource_id AND goal_a.subject=English AND goal_a.grade=1 LEFT JOIN goals AS goal_b ON resources.id=goal_b.resource_id AND goal_b.subject=English AND goal_b.grade=2 WHERE goal_a.id IS NOT NULL AND goal_b.id IS NOT NULL Alternately: SELECT resources.id, resources.title, COUNT(*) as nr FROM resources LEFT JOIN goals ON resources.id=goals.resource_id AND goals.subject=English WHERE goals.grade IN (1, 2) GROUP BY resources.id HAVING nr = 2 (The above is untested, since you didn't provide your example table in SQL, and I am unwilling to re-format it into CREATE TABLE/INSERT statemnts to test things. The concepts are solid.) Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Guarded exclamations of success! This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plain 'join' instead of 'left join', which seems to make it much faster. It was pretty slow at first. mysql select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from resources as r join resource_goals as ga on r.id=ga.ResourceID and ga.Grade='4th' and ga.GoalNumber='1' join resource_goals as gb on r.id=gb.ResourceID and gb.Grade='4th' and gb.GoalNumber='2' where ga.goal_id IS NOT NULL and gb.goal_id IS NOT NULL group by r.id; - AM On Mon, 14 Feb 2005 11:07:48 -0800, Jeremy Cole [EMAIL PROTECTED] wrote: Hi, Thanks, but unless I'm missing something, that will return the same as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? How about this: SELECT resources.id, resources.title FROM resources LEFT JOIN goals AS goal_a ON resources.id=goal_a.resource_id AND goal_a.subject=English AND goal_a.grade=1 LEFT JOIN goals AS goal_b ON resources.id=goal_b.resource_id AND goal_b.subject=English AND goal_b.grade=2 WHERE goal_a.id IS NOT NULL AND goal_b.id IS NOT NULL Alternately: SELECT resources.id, resources.title, COUNT(*) as nr FROM resources LEFT JOIN goals ON resources.id=goals.resource_id AND goals.subject=English WHERE goals.grade IN (1, 2) GROUP BY resources.id HAVING nr = 2 (The above is untested, since you didn't provide your example table in SQL, and I am unwilling to re-format it into CREATE TABLE/INSERT statemnts to test things. The concepts are solid.) Regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Have a look at the manual page for EXISTS, you appear to need something like SELECT * FROM resources AS r WHERE EXISTS ( SELECT resource_id FROM goals AS g WHERE g.resource_id = r.id AND grade=1 AND subject='English' ) AND EXISTS ( SELECT resource_id FROM goals AS g WHERE g.resource_id = r.id AND grade=2 AND subject'English' ) PB - AM Thomas wrote: I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. My MySQL is version 4.0.23a, if that makes a difference. Here's a simplified version of my problem. I have two tables, resources and goals. resources table: ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes goals table: ID RESOURCE_ID GRADE SUBJECT 1 11 English 2 11 Soc 3 12 English 4 21 English 5 23 Soc 6 32 English 7 41 English Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do: Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT=English) and ((GRADE=1) and (GRADE=2))); I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match. My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on. I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL mSQL book isn't doing the trick. Surely this has come up before - thanks for any guidance. - AM Thomas -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 2/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
AM Thomas wrote: I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. My MySQL is version 4.0.23a, if that makes a difference. Here's a simplified version of my problem. I have two tables, resources and goals. resources table: ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes goals table: ID RESOURCE_ID GRADE SUBJECT 1 11 English 2 11 Soc 3 12 English 4 21 English 5 23 Soc 6 32 English 7 41 English Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do: Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT=English) and ((GRADE=1) and (GRADE=2))); I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match. SELECT r.TITLE FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID) WHERE g.SUBJECT = 'English' AND (g.GRADE = 1 OR g.GRADE = 2) GROUP BY r.TITLE HAVING COUNT(*) = 2; This can be generalized. Put the OR-separated list of grades to be matched in the WHERE clause, and change the row count in the HAVING clause to be the number of grades required. My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on. No problem. SELECT r.TITLE FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID) WHERE g.SUBJECT = 'English' AND g.additional_field = 'whatever' AND (g.GRADE = 1 OR g.GRADE = 2) GROUP BY r.TITLE HAVING COUNT(*) = 2; I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL mSQL book isn't doing the trick. Surely this has come up before - thanks for any guidance. - AM Thomas Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
Except that he/she is using 4.0, which doesn't support subqueries. Michael Peter Brawley wrote: Have a look at the manual page for EXISTS, you appear to need something like SELECT * FROM resources AS r WHERE EXISTS ( SELECT resource_id FROM goals AS g WHERE g.resource_id = r.id AND grade=1 AND subject='English' ) AND EXISTS ( SELECT resource_id FROM goals AS g WHERE g.resource_id = r.id AND grade=2 AND subject'English' ) PB - AM Thomas wrote: I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. My MySQL is version 4.0.23a, if that makes a difference. Here's a simplified version of my problem. I have two tables, resources and goals. resources table: ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes goals table: ID RESOURCE_ID GRADE SUBJECT 1 11 English 2 11 Soc 3 12 English 4 21 English 5 23 Soc 6 32 English 7 41 English Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do: Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT=English) and ((GRADE=1) and (GRADE=2))); I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match. My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on. I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL mSQL book isn't doing the trick. Surely this has come up before - thanks for any guidance. - AM Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT WHERE problem
You need to change your INNER JOIN to a LEFT JOIN SELECT trucks.id, sum(history.time_sec) as total_seconds FROM trucks LEFT JOIN history ON trucks.id = history_truckid GROUP BY trucks.id ORDER BY total_seconds desc One other issue ---IMHO, the SQL engine is being too kind when it allows you to execute a query like SELECT trucks.* GROUP BY . In practically EVERY OTHER SQL-based product you will use, you will be required to list _all_ non-aggregated columns in your GROUP BY statement or you will get an error. Listing every column you want to group on is considered proper SQL format and I highly recommend the practice. If you still want to see everything from your trucks table (like in your original query) you can do this: CREATE TEMPORARY TABLE tmpTruckIDs SELECT trucks.id, sum(history.time_sec) as total_seconds FROM trucks LEFT JOIN history ON trucks.id = history_truckid GROUP BY trucks.id ORDER BY total_seconds desc; SELECT trucks*, tmpTruckIDs.total_seconds FROM trucks INNER JOIN tmpTruckIDs ON tmpTruckIDs.id = trucks.id; DROP TABLE tmpTruckIDs; HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ren Fournier [EMAIL PROTECTED] wrote on 08/09/2004 03:56:58 PM: I am having a problem building a SELECT statement that joins two tables with a WHERE condition. SELECT trucks.* FROM trucks, history WHERE trucks.account_id = '100' AND trucks.status = 'Active' AND history.truck_id = trucks.id This is the tricky bit GROUP BY trucks.id ORDER BY history.time_sec DESC Simply put (or as simply as I can put it :-) , this SELECT should return all trucks in order of their activity (history.time_sec). The problem is when a truck is new to the system and does not have a single record in the history table (and therefore no value for history.time_sec). In that case, the truck is excluded from the SELECTed rowsbut I want it returned, just at the bottom of the list (least active). Any ideas how this can be done? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT WHERE problem
Thanks, the LEFT JOIN worked. I do have a question though, why is it considered best practice to list all non-aggregated columns ( I assume you mean columns from trucks.*) in the GROUP BY statement? I ask because I am interested in fast, secure, standards-compliant code, I'm just not always sure what that is. :-) ...Ren --- Ren Fournier, www.renefournier.com On Aug 9, 2004, at 2:14 PM, [EMAIL PROTECTED] wrote: You need to change your INNER JOIN to a LEFT JOIN SELECT trucks.id, sum(history.time_sec) as total_seconds FROM trucks LEFT JOIN history ON trucks.id = history_truckid GROUP BY trucks.id ORDER BY total_seconds desc One other issue ---IMHO, the SQL engine is being too kind when it allows you to execute a query like SELECT trucks.* GROUP BY . In practically EVERY OTHER SQL-based product you will use, you will be required to list _all_ non-aggregated columns in your GROUP BY statement or you will get an error. Listing every column you want to group on is considered proper SQL format and I highly recommend the practice. If you still want to see everything from your trucks table (like in your original query) you can do this: CREATE TEMPORARY TABLE tmpTruckIDs SELECT trucks.id, sum(history.time_sec) as total_seconds FROM trucks LEFT JOIN history ON trucks.id = history_truckid GROUP BY trucks.id ORDER BY total_seconds desc; SELECT trucks*, tmpTruckIDs.total_seconds FROM trucks INNER JOIN tmpTruckIDs ON tmpTruckIDs.id = trucks.id; DROP TABLE tmpTruckIDs; HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ren Fournier [EMAIL PROTECTED] wrote on 08/09/2004 03:56:58 PM: I am having a problem building a SELECT statement that joins two tables with a WHERE condition. SELECT trucks.* FROM trucks, history WHERE trucks.account_id = '100' AND trucks.status = 'Active' AND history.truck_id = trucks.id This is the tricky bit GROUP BY trucks.id ORDER BY history.time_sec DESC Simply put (or as simply as I can put it :-) , this SELECT should return all trucks in order of their activity (history.time_sec). The problem is when a truck is new to the system and does not have a single record in the history table (and therefore no value for history.time_sec). In that case, the truck is excluded from the SELECTed rowsbut I want it returned, just at the bottom of the list (least active). Any ideas how this can be done? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT WHERE problem
Because not doing so violates the SQL standard. Allowing you to included non aggregated columns in the SELECT list is a non standard MySQL extension to the SQL language. You will get an error in other products, such as oracle, where you will get a xxx is not a group by expression error. Information on this feature is here: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html On Mon, 9 Aug 2004 17:22:17 -0600, Ren Fournier [EMAIL PROTECTED] wrote: Thanks, the LEFT JOIN worked. I do have a question though, why is it considered best practice to list all non-aggregated columns ( I assume you mean columns from trucks.*) in the GROUP BY statement? I ask because I am interested in fast, secure, standards-compliant code, I'm just not always sure what that is. :-) Ren --- Ren Fournier, www.renefournier.com On Aug 9, 2004, at 2:14 PM, [EMAIL PROTECTED] wrote: You need to change your INNER JOIN to a LEFT JOIN SELECT trucks.id, sum(history.time_sec) as total_seconds FROM trucks LEFT JOIN history ON trucks.id = history_truckid GROUP BY trucks.id ORDER BY total_seconds desc One other issue ---IMHO, the SQL engine is being too kind when it allows you to execute a query like SELECT trucks.* GROUP BY . In practically EVERY OTHER SQL-based product you will use, you will be required to list _all_ non-aggregated columns in your GROUP BY statement or you will get an error. Listing every column you want to group on is considered proper SQL format and I highly recommend the practice. If you still want to see everything from your trucks table (like in your original query) you can do this: CREATE TEMPORARY TABLE tmpTruckIDs SELECT trucks.id, sum(history.time_sec) as total_seconds FROM trucks LEFT JOIN history ON trucks.id = history_truckid GROUP BY trucks.id ORDER BY total_seconds desc; SELECT trucks*, tmpTruckIDs.total_seconds FROM trucks INNER JOIN tmpTruckIDs ON tmpTruckIDs.id = trucks.id; DROP TABLE tmpTruckIDs; HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ren Fournier [EMAIL PROTECTED] wrote on 08/09/2004 03:56:58 PM: I am having a problem building a SELECT statement that joins two tables with a WHERE condition. SELECT trucks.* FROM trucks, history WHERE trucks.account_id = '100' AND trucks.status = 'Active' AND history.truck_id = trucks.id This is the tricky bit GROUP BY trucks.id ORDER BY history.time_sec DESC Simply put (or as simply as I can put it :-) , this SELECT should return all trucks in order of their activity (history.time_sec). The problem is when a truck is new to the system and does not have a single record in the history table (and therefore no value for history.time_sec). In that case, the truck is excluded from the SELECTed rowsbut I want it returned, just at the bottom of the list (least active). Any ideas how this can be done? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT * WHERE DATE TODAY
At 15:40 -0800 1/19/03, Melissa Stranzl wrote:
I am trying to get my program to be searchable by
date, but it doesn't work. I get an error message in
perl, that follows the code I included. Any
suggestions are greatly appreciated.
PERL
--
@currenttime= localtime();
$y= $currenttime [5] + 1900;
$m= $currenttime [4];
$dom= $currenttime [3];
$ydoy= $y*1000 + $m + $dom;
print $ydoy;
my $dbh= DBI-connect ('DBI:mysqlPP:myd:localhost',
'pass', 'pass') ||die Could not connect to database:
.DBI-errstr;
my $sth = $dbh-prepare(SELECT * from food WHERE
end_date $ydoy);
$sth-execute($ydoy);
Wouldn't it be simpler to skip all that date manipulation stuff and
simply write your query like this?
SELECT * from food WHERE end_date CURDATE()
while((end_date $ydoy) = $sth-fetchrow_array)
That's illegal, because you're trying to assign a value to an
expression. Also, end_date is a bareword. Anyway, doesn't
your query already rule out records where the end_date values
are not greater than the current date? So you should be able
to just write this as:
while (($start_date, $end_date, $event) = $sth-fetchrow_array)
(I'm assuming that SELECT * returns rows in start_date, end_date, event
column order, which may be incorrect. If you're retrieving arrays,
you *REALLY* should name the columns explicitly in the order you want
in the SELECT statement, and *NOT* use SELECT *, which guarantees nothing
about column order in the result set.)
{
print $start_date\n, $end_date\n, $event\n, $id;
}
die $sth-errstr if $sth-err;
$sth-finish();
$dbh-disconnect();
ERROR MESSAGE:
--
can't modify generic gt()) in list assignment at line
31 (which is the fetchrow_array line)
repeated HERE:
while((end_date $ydoy) = $sth-fetchrow_array)
{
print $start_date\n, $end_date\n, $event\n, $id;
}
Thanks again for your help.
Melissa Stranzl
917-922-7872
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Where !=
So... Select n.uid, n.name from names_table n LEFT JOIN exclude ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL; OK... this works, but I thought I would go a bit further... I have added a field in the exclude-table, cat_id. This shows which catalogue the names have been excluded from. I want to do the operation above, but limit it to only the current catalogue, namely 16. I tried: Select n.uid, n.name from names_table n, exclude e1 LEFT JOIN exclude ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL AND e1.catid=16; I also tried putting some left joins in aswell, but I didn't get them to work. Am I mixing too much into the same query? // Michelle __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Where !=
So... Select n.uid, n.name from names_table n LEFT JOIN exclude ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL; OK... this works, but I thought I would go a bit further... I have added a field in the exclude-table, cat_id. This shows which catalogue the names have been excluded from. I want to do the operation above, but limit it to only the current catalogue, namely 16. I tried: Select n.uid, n.name from names_table n, exclude e1 LEFT JOIN exclude ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL AND e1.catid=16; I also tried putting some left joins in aswell, but I didn't get them to work. Am I mixing too much into the same query? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Where !=
http://www.mysql.com/doc/en/JOIN.html mysql SELECT table1.* FROM table1 -LEFT JOIN table2 ON table1.id=table2.id -WHERE table2.id IS NULL; So... Select n.uid, n.name from names_table n LEFT JOIN exclude ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL; -Original Message- From: Michelle de Beer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 11, 2002 11:22 AM To: mysql list Subject: Select Where != I have two tables. One with names and one for excluding certain names. Exclude-table contains the uid for the name excluded. If I want to see which names has been excluded, this query does the job: Select n.uid, n.name from names_tables n, exclude WHERE n.uid = exclude.n_uid But if I want to select all names, but leave out the ones that are in the exclude-table, I thought this would do it, but no. Select n.uid, n.name from names_tables n, exclude WHERE n.uid != exclude.n_uid It has something to do with the != thingy... Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Where !=
Michelle de Beer wrote: I have two tables. One with names and one for excluding certain names. Exclude-table contains the uid for the name excluded. If I want to see which names has been excluded, this query does the job: Select n.uid, n.name from names_tables n, exclude WHERE n.uid = exclude.n_uid But if I want to select all names, but leave out the ones that are in the exclude-table, I thought this would do it, but no. Select n.uid, n.name from names_tables n, exclude WHERE n.uid != exclude.n_uid It has something to do with the != thingy... Any thoughts? You are trying to do a join in a way you should not :-) Generally it is a bad idea to do a join based on a non-equality. It does not do what you think it does. (Look into how relational databases and joins work.) You should select your excluded values from the exclude table, the subtract irrelevant rows from table n. Are you using MySQL 3.xxx or 4.xxx? - Cs. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Where !=
SELECT n.uid, n.name from names_tables n LEFT OUTER JOIN exclude ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL -Original Message- From: Michelle de Beer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 11, 2002 3:22 PM To: mysql list Subject: Select Where != I have two tables. One with names and one for excluding certain names. Exclude-table contains the uid for the name excluded. If I want to see which names has been excluded, this query does the job: Select n.uid, n.name from names_tables n, exclude WHERE n.uid = exclude.n_uid But if I want to select all names, but leave out the ones that are in the exclude-table, I thought this would do it, but no. Select n.uid, n.name from names_tables n, exclude WHERE n.uid != exclude.n_uid It has something to do with the != thingy... Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select * where [whatever column] LIKE '%something%'
Hi, I don't think it is possible with out specifing the field names. But we can get the soultion in the following way If we have tabel calles 'test' with fields namely field1 and field2 then you can get all the records that conatin 'c' with the following query select * from test where Field1 like '%c%' or Field2 like '%c%' Will this solve your problem? Regards, Sneha At 01:32 PM 10/5/01 +0300, Ladopoulos Theodoros wrote: hello, I would like to ask if it is possible with mysql to do soomething like this: SELECT * FROM [specific table name] WHERE [WHATEVER FIELD(not specified)] LIKE '%sometext%' Is there a variable lets say to refer to the column name? What I actually want to do is to find all records that fields of a specific value without specifing the field name. To search for a string in a table but not say in what field (search in all fields) i want to know if that can be done with a single query. thanks a lot in advance teo [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: select * where [whatever column] LIKE '%something%'
yes that gives the right results but i ask if i can do the same thing without enumerating all fields like this: SELECT * FROM [tablename] WHERE Field1 LIKE '%text%' OR Field2 LIKE '%text%' OR ... What i want is to do the same thing without knowing what are the field names or how many fields this table has. I want to say : SELECT * FROM [tablename] WHERE [whatever field] LIKE '%text%' and if one or more fields in a record contain 'text' then this record is returned on the result.. quite tricky ha? if u have any idea please answer. Thanks a lot for your answer teo [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select where A is not a member of B
SELECT DISTINCT u.userid, IF( ul.listid, true, false) FROM user u LEFT OUTER JOIN userlist ul ON u.userid = ul.userid AND ul.listid = listid; -Original Message- From: David Otton [mailto:[EMAIL PROTECTED]] Sent: Monday, September 10, 2001 5:39 PM To: [EMAIL PROTECTED] Subject:Select where A is not a member of B Hi - I've got an annoying problem here, I've checked books, web, archives, etc, but can't find anything suitable so far. I have 2 data tables (ignoring the other columns, not important) : ++ | user | ++ | userid | ++ ++ | list | ++ | listid | ++ and a joining table : +--+ | userlist | +--+ | listid | | userid | +--+ As you can see, users can belong to many lists, lists can contain many users. I need to find, for each user, whether they are a member of list n : ++--+ | userid | memberoflist | ++--+ | 1 | true | | 2 |false | | 3 | true | | 4 |false | ++--+ It seems simple, but I've been banging my head against this all weekend. When I break it down, I need to find 3 things : Users that are members : SELECT user.userid FROM user, userlist WHERE listid=1 AND user.userid=userlist.userid Users that belong to NO lists : SELECT user.userid FROM user LEFT JOIN userlist ON user.userid=userlist.userid WHERE listid IS NULL Users that belong to some lists, but not the one I'm interested in : this is the one that I'm stuck on. Any thoughts? Suggestions? djo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select where A is not a member of B
[EMAIL PROTECTED] writes: [snipped fscking ML bullshit] database,sql,query,table David Otton writes: Users that belong to some lists, but not the one I'm interested in : this is the one that I'm stuck on. Any thoughts? Suggestions? SELECT DISTINCT userid FROM userlist WHERE listid != thislistid might be what you want. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select where A is not a member of B
Hi. On Mon, Sep 10, 2001 at 02:38:56PM -0700, [EMAIL PROTECTED] wrote: [...] I need to find, for each user, whether they are a member of list n : ++--+ | userid | memberoflist | ++--+ | 1 | true | | 2 |false | | 3 | true | | 4 |false | ++--+ The following should give something like the above (verified): SELECT u.userid, IF( COUNT(ul.listid) 0, 'true', 'false' ) FROM user u LEFT JOIN userlist ul ON u.userid = ul.userid AND ul.listid = 1 GROUP BY u.userid It seems simple, but I've been banging my head against this all weekend. When I break it down, I need to find 3 things : Users that are members : SELECT user.userid FROM user, userlist WHERE listid=1 AND user.userid=userlist.userid Users that belong to NO lists : SELECT user.userid FROM user LEFT JOIN userlist ON user.userid=userlist.userid WHERE listid IS NULL Users that belong to some lists, but not the one I'm interested in : this is the one that I'm stuck on. Any thoughts? Suggestions? This would be (not verified): SELECT DISTINCT u.userid FROM user u, userlist ul LEFT JOIN userlist nl ON nl.userid = ul.userid AND nl.listid = 1 WHERE ul.userid = u.userid AND nl.listid IS NULL That is: First build a list of all users that are members in any group, then look up which users are in list 1 and exclude them, then remove all duplicates (DISTINCT). Bye, Benjamin. PS: Feeding the filter: database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT/WHERE on TIMESTAMP colomn
I figured out the answer. It is: SELECT * FROM users WHERE added (CURRENT_TIMESTAMP() - INTERVAL 24 hour); --- Andrew Tomazos [EMAIL PROTECTED] wrote: I am running MySQL 3.23.39. I have a column called added of type timestamp(14) in a table called users. I set added to NULL on INSERTs in order to set it to the current time. I now want to SELECT all rows added in the last 24 hours. SELECT * FROM users WHERE added ... ? What is the correct way to write this statement? I want to say WHERE added is greater than the currenttime minus 24 hours. I could not figure it out by reading section 6.3.4 Date and Time Functions of the documentation. Anyone know? Thanks in advance, Andrew. = -- Andrew Tomazos Primary: [EMAIL PROTECTED] Auxilary: [EMAIL PROTECTED] __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php = -- Andrew Tomazos Primary: [EMAIL PROTECTED] Auxilary: [EMAIL PROTECTED] __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 'select * where x is null' not working
On 31-Aug-2001 Jeremiah T. Folsom-Kovarik wrote:
Weird things have just started happening in at least one table of my MySQL
3.22.32 database. The symptoms are like this:
mysql describe orders;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
...
| contact_info | smallint(5) unsigned | YES | | NULL| |
| expired | datetime | YES | | NULL| |
+--+--+--+-+-+---+
mysql select contact_info, expired from orders where order_num = '1';
+--+-+
| contact_info | expired |
+--+-+
| NULL | NULL|
+--+-+
^^
this is NULL this is 'NULL', the string.
1 row in set (0.00 sec)
mysql select count(*) from orders where contact_info is null;
+--+
| count(*) |
+--+
| 248 |
+--+
1 row in set (0.02 sec)
// here comes the problematic part //
mysql select count(*) from orders where expired is null;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.00 sec)
Obviously this last result is wrong; there should be at least one (and in
fact about thirty) table entries where expired is null. The thing can select
on and update the 'expired' field, and can show rows where 'expired' is null
if I select on another key, but can't seem to find those same rows when I
try to select them by the 'expired' field. Only that one field is giving me
trouble, and this just started happening today.
I've tried 'isamchk -r orders' to no avail, and I found nothing else that
might do this in the manual or archives.. I can send more info to anybody
that wants it, but does anybody recognize these symptoms right off or know
what might be causing them?
Check :
select * from orders where expired='NULL';
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table 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: 'select * where x is null' not working
Thanks for your quick response! More below.. mysql select contact_info, expired from orders where order_num = '1'; +--+-+ | contact_info | expired | +--+-+ | NULL | NULL| +--+-+ ^^ this is NULL this is 'NULL', the string. Check : select * from orders where expired='NULL'; Regards, -- Don Read Yeah, I noticed that funny spacing (left align vs. right align) too, but it doesn't seem to be the case: mysql select * from orders where expired like '%NULL%'; Empty set (0.00 sec) Plus, all this just started happening yesterday, after everything worked fine for many days. That leads me to believe there is a problem with a corrupt table or something. How about this further clue I just noticed: mysql select count(*) from orders; +--+ | count(*) | +--+ | 499 | +--+ 1 row in set (0.00 sec) mysql select * into outfile 'orders.bak' from orders; Query OK, 382 rows affected (0.07 sec) Weird, huh? -Jt. Jeremiah T. Folsom-Kovarik _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 'select * where x is null' not working
On 31-Aug-2001 Jeremiah T. Folsom-Kovarik wrote: Thanks for your quick response! More below.. snip Plus, all this just started happening yesterday, after everything worked fine for many days. That leads me to believe there is a problem with a corrupt table or something. How about this further clue I just noticed: mysql select count(*) from orders; +--+ | count(*) | +--+ | 499 | +--+ 1 row in set (0.00 sec) mysql select * into outfile 'orders.bak' from orders; Query OK, 382 rows affected (0.07 sec) Weird, huh? -Jt. Jeremiah T. Folsom-Kovarik Just a stab, but try droping rebuilding the key(s). Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table 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: SELECT WHERE fieldtype
You have to select a certain row, there is no way to say all rows that are NOT an int. At 04:33 PM 1/16/2001 -0500, Brian Kaney wrote: Is there a way to SELECT FROM table WHERE field_type IS (NOT) 'something'. For example, 'something' could be TIMESTAMP, INT, FLOAT, AUTO_INCREMENT, etc...I looked through the manual and archives to no avail. - Scott Baker - Webster Internet - Network Technician 503.266.8253 - [EMAIL PROTECTED] "Always bear in mind that your own resolution to success is more important than any other one thing." - Abraham Lincoln - 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
