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 |

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:

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

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

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|

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

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

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

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 |

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«

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

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

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

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

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

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«

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

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

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

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 -

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

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

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,

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

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

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

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

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

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

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

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