Re: Simple Query Question
Hi Ian, Why do you think something's wrong? Here is my test data and the results of your query: --- mysql SELECT * FROM wp_views; +-+-++---+ | blog_id | post_id | date | views | +-+-++---+ | 1 | 1 | 2009-12-16 | 2 | | 1 | 1 | 2009-12-17 | 3 | | 1 | 2 | 2009-12-16 | 4 | | 1 | 2 | 2009-12-17 | 5 | | 2 | 1 | 2009-12-16 | 6 | | 2 | 1 | 2009-12-17 | 7 | | 2 | 2 | 2009-12-16 | 8 | | 2 | 2 | 2009-12-17 | 9 | | 1 | 1 | 2009-12-18 | 1 | | 1 | 2 | 2009-12-18 | 1 | | 2 | 1 | 2009-12-18 | 1 | | 2 | 2 | 2009-12-18 | 1 | +-+-++---+ 12 rows in set (0.00 sec) mysql SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10; +-+-+---+ | blog_id | post_id | views | +-+-+---+ | 2 | 2 |17 | | 2 | 1 |13 | | 1 | 2 | 9 | | 1 | 1 | 5 | +-+-+---+ 4 rows in set (0.00 sec) --- Seems OK to me... Are you getting different results? Take care, Aleksandar Ian wrote: Hi, I am sure there is a simple solution to this problem, I just cant find it :) I have got a table that records views for an article for each blog per day. So the structure is as follows: CREATE TABLE `wp_views` ( `blog_id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `date` date NOT NULL, `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Now thats fine and I can pull top blogs per day and thats all fine, but what I am after is pulling the top articles for a time period and where I am running into problems is where two blogs have the same post_id's the views get sum()'d for the day and I cant figure out (read end of year mind block) how to get around it. Here is my current query (for last 7 days): SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date = 2009-12-17 AND date = 2009-12-10) GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10 Any ideas as to whats wrong. I know its something simple, I just cant put my finger on it. Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: DROP DATABASE
Hi, Does drop database command removes all the users permissions related with that databases or those user permissions has to be revoke manually. DROP DATABASE does not remove the privileges. You have to remove that separately. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: DROP DATABASE
Hi, What will be the impact if i don't remove the users privileges. Does mysql will restart successfully or not. OR should i remove the users privileges before dropping the database. It will restart sucessfully. The only impact I can see is that next time a database with the same name is created, the not-removed users will have access to it. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query headaches
Hi, The error I get while trying executing is Unknown column 'products.product_id' in 'on clause'. Since I am selecting all columns from that table (products), I can't really see why there's a fuss about it! # SELECT products.*, manufactors.*, # IF(( # SELECT i.supplier_id FROM products.items_new i # JOIN products.item_status ON (item_status.product_id = products.product_id item_status.supplier_id = i.supplier_id item_status.group_id = 1) # WHERE i.product_id = products.product_id i.item_stock 0 # ), # ... Well, `JOIN` has nothing to do with the columns you are selecting - it rather looks in the tables you are selecting `FROM`. In your case, you are selecting `FROM products.items_new i` and joining it to `products.item_status`. These are the only two tables you can use in the JOIN. You can not use a table from another SELECT (which you are doing here). This kind of reference is supported in the WHERE clause only. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: insert/replace question...
Hi, REPLACE is a special INSERT/UPDATE combination where you dont specify a filter, it uses the primary key. If no existing record exists, it INSERTs a new one, otherwise it UPDATEs an existing one. Just a quick note - REPLACE does not do any UPDATE. It is a combination of DELETE (if the record exists) and INSERT. Also, it does not have to be the primary key - any UNIQUE index will do. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions on PRIMARY KEY
Hi, WHERE col1 something AND col2 something etc... AND PRIMARYKEY 0; [...skip...] I need an explanation of whether what I did is an optimization or not? Or should i be looking into something else to actually optimize the query. The best way to optimize it is would be to add an index for some or all the columns from the WHERE clause. Once you do that you will not need the `AND PRIMARYKEY 0` part and the query will truly be using the index to improve the performance. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on EXPLAIN in rating queries
Hi, I am trying to JOIN 2 tables TBL1 and TBL2 on TBL1.fld_id = TBL2.fld_id . And finally I filter out the results that i need in the where clause using where TBL1.fld_col = 100; Running an EXPLAIN shows that it is an impossible where condition. This may be because there may be no rows with fld_col = 100. But in future there could be rows with this value in fld_col. So how should I rate this query? Should I consider this query as a bad one just because it has an impossible where currently? No, it's not a bad query - at the moment it's very fast as it returns no data :) As for the future, try running EXPLAIN with an existing value instead of `100`. In this particular case, you will probably want an index on `fld_col` for it to run smoothly. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order desc problem
Hi, the order comes out of sequence showing 10.11.12.13 etc before the number 2--- Can anyone help me out That's because you are sorting the result on a string (char/varchar) column. Try using CAST to convert it to int or something similar: ORDER BY cast(column as unsigned) Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Value of a referenced field
Hello Andreas,
You could try with this:
insert into table1 (authorid, lastname, firstname)
values (nextval('s_authors'), 'Meyers', 'Scott');
INSERT INTO table1(authorid, lastname, firstname)
VALUES (null, 'Meyers', 'Scott');
insert into table2 (authorid, title, subtitle)
values (currval('s_authors'), 'Effektiv C++ Programmieren',
'50 Wege zur Verbesserung Ihrer Programme und Entwuerfe');
INSERT INTO table2 (authorid, title, subtitle)
VALUES (last_insert_id(), 'Effektiv C++ Programmieren',
'50 Wege zur Verbesserung Ihrer Programme und Entwuerfe');
Best regards,
Aleksandar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1005 at line 12: Can't create table
Hi, C:\mysql\binperror 150 Error code 150: Unknown error 150 = Foreign key constraint is incorrectly formed Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Postponing Integrity Checking...
Hi, How do I execute the following UPDATE statements such that I can ensure that all integrity constraints are maintained upon the completion of the last one? UPDATE test_parent SET id = 6 WHERE id = 1; UPDATE test_child SET parent_id = 6 WHERE parent_id = 1; And would specifying ON UPDATE CASCADE help? That's what it's meant to do, right? Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE
Hi, Error: ERROR 1005: Can't create table './tamiyausa/user_shipping_info.frm' (errno: 150) C:\mysql\binperror 150 Error code 150: Unknown error 150 = Foreign key constraint is incorrectly formed Look like your foreign keys are not properly defined. Do both tables exist? And the fields you are referencing? Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
Hi, I want to select from the table sum of logins for each day. Would this help: mysql select date_format(your_date_column, %Y-%m-%d), count(*) - from your_table - group by date_format(your_date_column, %Y-%m-%d); Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: large SQL statements
Hi, Is there a more practical way to execute a statement of this size, or another program that will handle remote server backups differently? You might use mysql.exe client (found in 'c:\mysql\bin' folder): mysql.exe -u your_username -p c:\backup_file.sql Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select on indexed columns
Hi, Any way to make this faster ? Try to create an index on both fields: create index idsex_index on sex (id, sex) Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Select on indexed columns
Hi, Any way to make this faster ? Try to create an index on both fields: create index idsex_index on sex (id, sex) Tried that; same results... Could you post the result of the EXPLAIN command on that query? Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting by more than 1 column
Hi, I told him I didn't think it was possible to sort two different fields one acending and one descending. But, of course, it is possible :) http://www.mysql.com/doc/en/SELECT.html SELECT ... ORDER BY city, county, price DESC Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 problem with select and stored procs
Hi, Any ideas why it's looking for the table .1? Could you post your query? Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Enum default values
Hi,
I will now have to supply a field list to the function in
addition. Ah well ;-)
Maybe this help:
insert into `property` values ('', 'Riverside View', default);
Take care,
Aleksandar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax? [Select within Insert]
Hi,
Then: INSERT INTO Extra_Credit (Student_ID, Points) SELECT MAX(Student_ID)
from Students,
(1) ...VALUE ('25');
or
(2) ... '25' as Points;
I think this is your query:
INSERT INTO Extra_Credit(Student_ID, Points) SELECT MAX(Student_ID), '25' from Students
Take care,
Aleksandar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Arbitrary interval for DATE_ADD()
Hi, SELECT DATE_ADD(NOW(),tbl_name.interval) AS date FROM tbl_name; As you know, the syntax is: DATE_ADD(date,INTERVAL expr type) The 'expr' can be a column, but I don't think either 'INERVAL' or 'type' support columns - they are not strings. So, IMHO, this is the closest you can get: SELECT DATE_ADD(NOW(), INTERVAL tbl_name.interval MONTH) AS date FROM tbl_name; where tbl_name.interval = '1'. It shouldn't be too hard to convert all intervals to months (or whatever you want to use)... Hope it helps. Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Hi, I have already tried the 'rtfm', but it just didn't help. But it's right there :) 3.5.2 The Row Holding the Maximum of a Certain Column ..and I want to get this with a single query: +-++--+ | key | desc| value | +-++--+ | 2 | book| 7 | | 6 | pen | 7 | +-++--+ select key, desc, value from your_table t1 where value = (select max(value) from your_table where desc = t1.desc) Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Need help with a query..
Hi, select key, desc, value from your_table t1 where value = (select max(value) from your_table where desc = t1.desc) Anyway, when i execute this query, i get an error near 'select max(value)'... :( It's because the subselects are supported from version 4.1. If you use older MySQL version then it's not possible to do it with a single query :( Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
Hi, I imagine there has to be a better way! Yes :) Take a look at the LAST_INSERT_ID() function. Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate combination
Hi, I have a table with peoples names in 3 different languages. Fields are like: id, surname_english, name_english, surname_original, name_original, surname_greek, name_greek. What I want is to check if a person has been entered twice in that table. The key is ID but I can't have any other field unique, as names and surnames are not unique. I only want the combination of two fields of the same language to be unique. How can I check this? Any possible solution? You can always define an UNIQUE index on the required fields. Something like this: mysql select * from nametest; +++---++---+ | id | name_1 | surname_1 | name_2 | surname_2 | +++---++---+ | 1 | A | A | E | T | | 2 | B | B | S | F | | 3 | C | C | E | T | +++---++---+ 3 rows in set (0.00 sec) mysql alter table nametest add unique index sn1(name_1, surname_1); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql alter table nametest add unique index sn2(name_2, surname_2); ERROR 1062: Duplicate entry 'E-T' for key 3 The creation of the first index went OK as there are no duplicate entries. The second one reported a duplicate entry. Once created these indices will not allow you to enter duplicates. Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where clause structures
Hi, - WHERE CompanyDetails.CompanySuspended='0' AND - CompanyDNSZones.ZoneName='megalan.co.za' AND - CompanyDNSZones.ZoneServices LIKE '%HasMail%' OR - CompanyDNSZones.ZoneServices LIKE '%HasMailingList%'; I explictly tell MySQL in my where clause to only return results where CompanyDNSZones.ZoneName='megalan.co.za', so where does it fall out returning lists.megalan.co.za as well? It's that OR that makes the mess. Try with: - WHERE CompanyDetails.CompanySuspended='0' AND - CompanyDNSZones.ZoneName='megalan.co.za' AND - (CompanyDNSZones.ZoneServices LIKE '%HasMail%' OR - CompanyDNSZones.ZoneServices LIKE '%HasMailingList%'); Regards, Sasa »mysql, select, 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: join with tabla.col=max(tablea.col)
Hi, === Question No.1 === mysql select *,max(cb) from testb left join testa using (ca) group by testb.ca; +--++--+--+-+ | ca | time | ca | cb | max(cb) | +--++--+--+-+ |2 | 20020705145347 |2 |2 | 9 | |3 | 20020705145349 |3 |3 | 3 | +--++--+--+-+ This query selects all the fields from testb (ca, time) AND testa (ca, cb) because of the LEFT JOIN you used. You should try specifying the just fields you need: mysql select testb.ca, testb.time, max(cb) from testb left join testa using (ca ) group by testa.ca; +--++-+ | ca | time | max(cb) | +--++-+ |2 | 20020705155526 | 9 | |3 | 20020705155534 | 3 | +--++-+ 2 rows in set (0.01 sec) === Question No.2 === mysql select *,max(cb) as mm from testb left join testa using (ca) where testa.cb=mm group by testb.ca; ERROR 1054: Unknown column 'mm' in 'where clause' mysql select *,max(cb) as mm from testb left join testa using (ca) where testa.cb=max(cb) group by testb.ca; ERROR : Invalid use of group function You should be using the HAVING clause, linke this: mysql select testb.ca, testb.time, max(cb) from testb left join testa using (ca ) group by testa.ca having testb.ca=max(cb); +--++-+ | ca | time | max(cb) | +--++-+ |3 | 20020705155534 | 3 | +--++-+ 1 row in set (0.00 sec) Regards, Sasa »mysql, select, 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: how to query for the primary key?
Hi, This seems like a dumb question--sorry. Looking at my table it shows 'MUL' instead of 'PRI' like the other tables. Did I forget to code unit_id as primary? Thanks, Justin mysql show index from property_units; Regards, Sasa »mysql, select, 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[2]: query for search on mysql database
Hi, I want an OR test. If someone searches on last name and enters nothing in the other fields, I want to find the record(s). Similarly, if they enter a first name and no other data, I want to find the record(s). The part I realize I am missing is to first test to see which fields have been filled in. Need some pointers on how to start that. Thanks! Mike - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: MikeParton [EMAIL PROTECTED] Sent: Tuesday, May 14, 2002 3:49 PM Subject: RE: query for search on mysql database * MikeParton I have a similar query in a PHP script. I want to allow users to use fields in a page to search for the records. BUT, I want them to be able to enter the first few characters. I would think my query, below, would do it (the entire search works when the WHERE statement has first='$first' OR last='$last' OR). BUT, when I search using any field (or simply click my submit button) it returns ALL records in the database. Where is my SQL flawed? SELECT id, first, last, email, phone, message, time FROM visitors WHERE id='$id' OR (first LIKE '$first%') OR (last LIKE '$last%') OR (email LIKE '$email%') OR (phone LIKE '$phone%') OR (message LIKE '$message%') OR (time LIKE '$time%') ORDER BY id DESC; If any of your $-variables are empty, the criteria will be ... LIKE '%', and this will match all rows, and return all rows, because you use OR. Change it to AND, and it should work as expected. If OR is what you want, you should only check the fields where the user actually have entered something... but you should probably use AND... If a user enters a first name and a single letter in the last field, he would probably expect to get persons with the entered first name and a last name starting with the provided letter, not all persons with that first name and all persons with a last name starting with the single letter. :) -- Roger - 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 One workaround is to test the values rigth there: SELECT id, first, last, email, phone, message, time FROM visitors WHERE id='$id' OR (($first '') and (first LIKE '$first%')) OR (($last '') and (last LIKE '$last%')) OR ... I haven't tested how fast this works, but it shouldn't slow down the query... Regards, Sasa »mysql, select, 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: instead of subquery?
Hi, Hi. I have this table (A) IDJOB ¦ IDKIT 4 ¦ 19 4 ¦ 19 2 ¦ 19 2 ¦ 5 I need to extract IDKIT with IDJOB2 if and only if IDKIT IDKIT when IDJOB=2 In this case my query should return null With a subquery I can do this and it works SELECT idkit from A where A.id_job2 and A.id_kit not in (select A.id_kit from A where A.id_job=2) Maybe you could try this query: mysql select distinct a1.idjob - from a a1 left join a a2 on a1.idkit = a2.idkit and a1.idjob a2.idjob - where a1.idjob 2 and (a2.idjob 2 or a2.idjob is null); Regards, Sasa »mysql, select, 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: calculated fields
Hi, SELECT product, price * 1.22 AS price_with_vat WHERE price_with_vat 1000; mysql select price * 1.22 as pricevat from pricelist; +--+ | pricevat | +--+ |12.81 | +--+ 1 row in set (0.02 sec) mysql select price * 1.22 as pricevat from pricelist having pricevat 10; Empty set (0.00 sec) mysql select price * 1.22 as pricevat from pricelist having pricevat 10; +--+ | pricevat | +--+ |12.81 | +--+ 1 row in set (0.00 sec) Regards, Sasa »mysql, select, 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: alternative to intersect in mySQL
Hi, I know that intersect does not work yet with mySQL. I am selecting data from two tables. Results of one select would be 1, 2, 3, 4 . Results of other select 1, 2. Want to print 1, 2. Are there any nice workarounds using PHP or SQL? You could use a temporary table to store the result of the first query and join the second query with it afterwards. Nice enough? :) Regards, Sasa »mysql, select, 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: Help needed on query
Hi, Now I need a query which will take the values from column 'query', treat them as patterns for matching and return row(s) which match the given string 'blahblah'. In this example, the matched data is obviously in second row. (bla% matches blahblah) Do I make any sense? Is this possible? Any help will be greatly appreciated. To be honest I didn't belive it would work, but... here it is :) mysql select * from blah; +--+--+---+ | uid | username | query | +--+--+---+ |1 | someuser | %qu1% | |2 | anotheru | bla% | +--+--+---+ 2 rows in set (0.00 sec) mysql select * from blah where 'blahblah' like query; +--+--+---+ | uid | username | query | +--+--+---+ |2 | anotheru | bla% | +--+--+---+ 1 row in set (0.00 sec) Regards, Sasa mysql, select, 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: ignore the first sting in order
Hi, Table example: _The Rock Scary Movie X-Files With order I want the output Scary Movie _The Rock X-Files select * from movies order by trim(leading from replace(movetitle, '_', ' ')); Regards, Sasa »mysql, select, 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: ignore the first sting in order
Hi, Table example: _The Rock Scary Movie X-Files With order I want the output Scary Movie _The Rock X-Files After reading your message again, look like this is what you've been searching for. Sorry :( select * from movies order by substring_index(movietitle, '_', -1); And if you have underscores in the rest of the title, it won't work well... Regards, Sasa »mysql, select, 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[3]: group by timestamp field
Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hi, Field1 (varchar) , Field 2 (timestamp) . I want to do : select count(*) , field1 , group by field1 . That's ok , but i'd like to get results like : 2002-01-01 3 2002-01-02 4 . How can i do that considering the timestamp field which stores date like (20020314184748) , thus containg the hour , second , etc ? Try something like: select substring(field2, 1, 8), count(*) from yourtable group by substring(field2, 1, 8); Regards, Sasa - 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: install on Red Hat Linux 7.2 with rpm
Hi, installing MySQL-Max-3.23.49-1.i386.rpm on Linux Red Hat 7.2 (server install option) kernel 2.4 while logged in as root, from /usr/local/mysql on an old PC Hi. I am brand new to Linux, and I can't get MySQL installed. The client part goes fine, but when I do rpm -ivh MySQL-Max-3.23.49-1.i386.rpm I get this Preparing...# [100%] 1:MySQL-Max # [100%] Giving mysqld a couple of seconds to restart /var/tmp/rpm-tmp.18185: /etc/rc.d/init.d/mysql: No such file or directory I keep wondering if I am missing some common utility because this is a fresh Linux install, and I haven't added much. But how would that result in a missing file? Did you install MySQL before trying to install MySQL-Max? If I remeber right, these messages appear if you try to install Max without installing MySQL first... Regards, Sasa - 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-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to login after password setup?
Hi, I tried to set the root password in the mysql db; update user set Password='password' where User='root'; MySQL encrypts passwords, so your update statement is useless. Try using GRANT... :) Regards, Sasa - 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: LEFT JOIN trouble. Please help.
Hi, I have two tables and I want create third one with LEFT JOIN First table table1 containts field AA as a primary key (AA is NOT NULL). Second table table2 containts field AA as a primary key (AA is NOT NULL) too. Ex: CREATE TABLE a (PRIMARY KEY(AA)) SELECT table1.AA FROM table2 LEFT JOIN table1 ON table2.AA=table1.AA WHERE ...; The problem is, that mysql creates new table with field AA, but it's not NOT NULL (it's allow NULL) = I cannot create primary key on this field. Is it bug or I'm wrong ??? Your nulls are created by LEFT JOIN (it returns null if there is no matching values for table2.AA in table1).Maybe you should try: CREATE TABLE a (PRIMARY KEY(AA)) SELECT table1.AA FROM table2, table1 WHERE table2.AA=table1.AA AND ...; or: CREATE TABLE a (PRIMARY KEY(AA)) SELECT table1.AA FROM table2 LEFT JOIN table1 ON table2.AA=table1.AA WHERE table1.AA IS NOT NULL AND ...; Regards, Sasa - 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 QUERY Problem
Hi, Table 1: names Id | name 15 | George 16 | Suzy Table 2 : scores_1 Id | score 15 | 85 15 | 60 15 | 70 15 | 95 Table 3 : scores_2 Id | score 15 | 50 15 | 55 15 | 60 15 | 45 What I want to end up with is a selection that would pick up George and his highest score on score_1 and score_2 (i.e. George 95 60) mysql select n.name, max(s1.score), max(s2.score) - from names n, scores_1 s1, scores_2 s2 - where n.id = s1.id and n.id = s2.id and - n.id = 15 - group by n.name; ++---+---+ | name | max(s1.score) | max(s2.score) | ++---+---+ | George |95 |60 | ++---+---+ 1 row in set (0.00 sec) Pozdrav, Sasa - 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[2]: can i do this with sql?
Hi, i actually need the id's with no row returned as if they were there (but with default values)... they do not need to be inserted into the table, just returned as if they were in there. The only way I can think of would be to create a table with all the values (ids) you need, and then use LEFT JOIN to retrieve data... Table: IDS TABLE: IDSDATA id id data year 1 1 2 01 2 2 2 01 3 6 2 01 4 7 2 01 5 8 2 01 6 11 2 01 7 8 9 10 11 12 Then your query would look like this: SELECT IDS.ID, IDSDATA.DATA, IDSDATA.YEAR FROM IDS LEFT JOIN IDSDATA ON IDS.ID = IDSDATA.ID ORDER BY IDS.ID Regards, Sasa - 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: How do I count using two tables
Hi, I've got two tables one is News items News_IDNewsetc... 1 Today in .etc 2 Hello world .etc 3 Blar di blaretc 4 And now time for something else . . 100The last thing and the other is comments on the news items (as well as other things on the web site) TypeComment_IDComment etc... News1 What about today then... News1 This is still today... News4 This parot is dead.. NotNews 4 Hello.. What I am trying to get is one SELECT statment that will give me News_IDNews Comment_Count etc... 1 Today in ... 2 2 Hello world. 0 3 Blar di blar0 4 And now ...etc 1 select news.newsid, news.news, count(comment.comment_id) from news left join comment on news.newsid = comment.comment_id and comment.type = 'News' group by news.newsid, news.news; Sasa - 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: Need help with mysqldump
Hi, I'm running MySQL ver 3.23.38 on Win98 platform and am trying to learn to do backups. I'm trying the mysqldump command but I'm getting the error: Access denied for user '@localhost' How do I specify a user for mysqldump? mysqldump --user=username ... Try mysqldump --help for more :) Regards, Sasa - 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: is it a many-to-many?
Hi, Table1: product Table2: measures 1) ID 1) ID 2) weight 2) symbol 3) ID_weight_measure 4) height 5) ID_height_measure OK. Is there a way to obtain with a single select statement both (or any...this is only an example) the joins ID_weight_measure with the related symbol, and the ID_height_measure with its symbol? I don't know if it's a many-to-many relationship, and even if it is, I don't know how to implement it. I hope in your help. select p.id_weight_measure, m1.symbol, p.id_height_measure, m2.symbol from product p, measures m1, measures m2 where p.id_weight_measure = m1.id and p.id_height_measure = m2.id Regards, Sasa - 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: innodb file size usage
Hi, how much of the 600MB actually is used at the moment I'm sure there'a a better way to do it, but this one works: mysql show table status like 'your_innodb_table' \G ... Comment: InnoDB free: 3739648 kB Best Regards, Sasa - 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: Using ALTER to modify type of a Primary Key; URGENT
Hi, A primary key was set to 'tinyint(4)' limiting the number of records to 128. I've tried: alter table my_data modify phyid mediumint unsigned DEFAULT 0; ERROR 1121: Column 'phyid' is used with UNIQUE or INDEX but is not defined as NOT NULL Try: alter table my_data modify phyid mediumint unsigned NOT NULL DEFAULT 0; Sasa - 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: Weird Error Inserting
Hi,
Unable to insert [
insert into match (league_id,round_id,team_id_1,team_id_2) VALUES ('1','3','2','3')
]: You have an error in your SQL syntax near 'match
(league_id,round_id,team_id_1,team_id_2) VALUES ('1','3','2','3')' at line 1.
MATCH is a keyword... :) How did you manage to create the
table in the first place? :)
Best Regards,
Sasa
-
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: surely an easy quick one
Hi, idname group_name -- 1bill support 100 2bill support 101 3bill support 102 4bendevelopment 201 5bendevelopment 103 6bendevelopment 204 7bobsupport 104 8bobsupport 102 9bobsupport 107 7patsupport 102 8patsupport 202 9patsupport 105 I need to get a count of how many individual people are in the table, that belong to a certain group. select count(distinct name) from table where group_name = 'support'; Best Regards, Sasa - 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[2]: surely an easy quick one
Hi, SELECT COUNT(DISTINCT name) from tester WHERE team = 'support' Could 2 queries do the trick? insert into temptable select count(name) from tester where team = 'support' group by team; select count(*) from temptable; Best Regards, Sasa P.S. create table temptable(tempfield integer); - 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
