RE: Query Help
"Ben Wiechman" wrote on 02/10/2009 01:30:14 PM: > Thanks for the input! That is close to what I need, however not exactly. It > will give me the last time a user logged into the host in question but I > want to prune users who have since logged into a different host. Basically > find out how many users are logged into a given host or who are not > currently logged in but have not logged into a different host since they > logged out of the target. Figure out the last time each user logged in to any host: SELECT login, MAX(datetime)as lastlogindate FROM Log GROUP BY login So use that query as a derived table to get the rest of the info (untested SQL): SELECT Userinfo.Username, Userinfo.GroupName, Log.hostname, Log.datetime FROM Userinfo INNER JOIN (SELECT login, MAX(datetime)as lastlogindate FROM Log GROUP BY login) AS lastlogin ON Userinfo.login=lastlogin.login INNER JOIN Log ON lastlogin.login=Log.login AND lastlogin.lastlogindate=Log.datetime Hope that helps. Donna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: db setup - correction
PJ wrote on 02/10/2009 12:44:04 PM: > -- - > -- Table `books` > -- - > CREATE TABLE IF NOT EXISTS `books` ( > `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT , > `title` VARCHAR(148) NULL , > `sub_title` VARCHAR(90) NULL , > `descr` TINYTEXT NULL , > `comment` TEXT NULL , > `bk_cover` VARCHAR(32) NULL , > `publish_date` YEAR NULL , > `ISBN` BIGINT(13) NULL , > `language_id` INT NULL , > PRIMARY KEY (`id`) , > INDEX `fk_books_language` (`language_id` ASC) , > CONSTRAINT `fk_books_language` > FOREIGN KEY (`language_id` ) > REFERENCES `biblane`.`language` (`id` ) > ON DELETE NO ACTION > ON UPDATE NO ACTION) > ENGINE = InnoDB; > May I make one sugggestion? I noticed that the books.id column is defined as SMALLINT UNSIGNED. Unless your database is going to stay quite small, that is really going to limit the number of books. This column is used as a FK in a number of your other tables and if you later on have to change the data type to make it bigger, you'll have to change all the related tables. If I remember correctly, I had to drop all the FK constraints that referenced this column, do the alter tables, and then recreate the FK constraints. Save yourself the hassle and make it at least an Integer, if not a BIGINT (unsigned). http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Donna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Try looking at the information_schema.KEY_COLUMN_USAGE table (where referenced_table_schema is not null). It will show you the FK relationships. You could then create a tree that you could use to find the hierarchy. For that, I suggest looking at http://www.artfulsoftware.com/infotree/mysqlquerytree.php. The information_schema table is already sort of an edge-list, although each node is made up of the tuple (table_schema, table_name, column_name) or (referenced_table_schema, referenced_table_name, referenced_column_name). Donna news wrote on 02/03/2009 05:38:34 PM: > Andy Shellam wrote: > > Am I missing something here? (It is late after a long day, I admit!) > > Only something I forgot to mention. > > All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's > response to a foreign key violation is to spit out an error message to the > effect of "I'm sorry, Dave, I can't let you do that." > > The problem is, the target platform doesn't use foreign keys for performance > reasons. I want to use foreign keys in development as a bug-trappingmethod -- > I'd rather see an FK violation error in development than get an angry email > from a customer asking why there's a part listed that doesn't seem to have a > manufacturer. > > The plan was to write a code-generator that would generate all the database > code for me, then I could deal with the page templates and display logic > myself (thus eliminating ~80% of the boring, repetitive work). I want the > generated code to handle foreign keys itself, rather than relying on > the database. > > As I said above, if foreign key constraints didn't slow things down markedly, > I'd use them in production. Based on the (admittedly limited) testing I've > done, application-side FK enforcement is considerably faster than using ON > DELETE CASCADE and letting MySQL deal with the foreign keys. > > I don't like writing database code by hand (it all follows a standard > template), so I figured I'd write a program to do it for me. "Work > smarter not > harder" and all that :) > > Thanks, > -- > Phil. > usene...@philpem.me.uk > http://www.philpem.me.uk/ > If mail bounces, replace "08" with the last two digits of the current year. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql? > unsub=ddevaudre...@intellicare.com > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > CONFIDENTIALITY NOTICE:This email is intended solely for the person > or entity to which it is addressed and may contain confidential > and/or protected health information. Any duplication, > dissemination, action taken in reliance upon, or other use of this > information by persons or entities other than the intended recipient > is prohibited and may violate applicable laws. If this email has > been received in error, please notify the sender and delete the > information from your system. The views expressed in this email are > those of the sender and may not necessarily represent the views of > IntelliCare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is deleting the .FRM, .MYD and .MYI files the same as dropping table?
Daevid Vincent wrote on 01/15/2009 09:57:19 PM:
> you misunderstand me. I have three servers (dev, test, prod) that all
> have maybe 3 databases EACH that have all these eventum* tables in them.
> don't ask. a simple "trickle" won't do. I'm writing a script to loop
> through them all.
>
The script below will create the Drop Table command for all tables like
'eventum%'.
select CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
from information_schema.TABLES
where TABLE_NAME like 'eventum%' and TABLE_TYPE <> 'VIEW'
Donna D.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: com_* status variables seem to reset in mysql 5
Try show global status like 'com_select'; Donna "Jim Lyons" <[EMAIL PROTECTED] .com> To "MySQL List" 11/12/2008 05:24 PM cc Subject com_* status variables seem to reset in mysql 5 I have been trying to compute query cache utilization in mysql 5 but cannot because the com_select status variable is always 1 when I start a new mysql session. This probably holds for all the com_* variables and maybe others, but I've only been working with com_select. They're supposed to be cumulative and reset only when you explicitly reset status or bounce the server. Here's an example, showing the tail end of a test mysql session showing the value of com_select when I exited, and the value a few seconds later when I began a new mysql session. This was on my own test server, no one else was on to reset status. It repeats every time I try it: ### BEGIN SESSION mysql> show status like 'com_select'; -- show status like 'com_select' -- +---+---+ | Variable_name | Value | +---+---+ | Com_select| 4 | +---+---+ 1 row in set (0.01 sec) mysql> quit Bye > mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 94774 Server version: 5.0.45-community-log MySQL Community Edition (GPL) Reading history-file /home/jlyons/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show status like 'com_select'; -- show status like 'com_select' -- +---+---+ | Variable_name | Value | +---+---+ | Com_select| 1 | +---+---+ 1 row in set (0.01 sec) mysql> ### END SESSION Note how com_select is 4 in the first session, then is reset to 1. I tried this on Linux RHEL, Linux RH 5 community (as shown here) and a Windows mysql 5 platform. Heres' the script I ran: drop table if exists t; create table t (x serial); # put some data in insert into t values (null); insert into t values (null); insert into t values (null); insert into t values (null); # create some selects select * from t where x = 1; select * from t where x = 2; select * from t where x = 3; select * from t where x = 4; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; show status like 'qcache_hits'; show status like 'com_select'; When I ran the same script on a Windows mysql 4 version, the value of com_select persisted over the login, which is what it should. Is this a bug in mysql 5? Is something set incorrectly in my config file that would cause this (I can't find anything)? Thanks for any help. -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trouble with group by and similar tables
Eben <[EMAIL PROTECTED]> wrote on 06/05/2008 02:44:42 PM: > I have the following tables: > > table1 > --- > id1 > some_field > > table2 > --- > id > id1 > score > > table3 > --- > id > id1 > score > > I then have the following query: > SELECT table1.id,SUM(table2.score) as table2_score > FROM table1, table2 > WHERE table1.some_field = 'value' > AND table2.id1 = table1.id > GROUP BY table1.id > > This works fine and returns each record in table1, grouped by id, with > the sum of scores from table2. However, when I do this query: > > SELECT table1.id,SUM(table2.score) as table2_score, SUM(table3.score) as > table3_score > FROM table1, table2, table3 > WHERE table1.some_field = 'value' > AND table2.id1 = table1.id > AND table3.id1 = table1.id > GROUP BY table1.id > > The sum'd score values go crazy, reflecting #s that aren't logical. Is > the issue that table2 and table3 are identical table structures, or that > I simply don't understand how the group by is really working here...? > > Any advice is appreciated, > Eben > Try taking away the sum and the group by and just select * from your query. You'll see the problem is with the join, not the group by. There are probably several solutions. Here's one way (untested). This will only work if your version of MySQL supports derived tables, which I think is 4.1 or higher but I'm not sure. SELECT table1.id, t2sum.table2_score, t3sum.table3_score FROM table1 INNER JOIN (SELECT id1, SUM(score)as table2_score FROM table2 GROUP BY id1) as t2sum ON table1.id=t2sum.id1 INNER JOIN (SELECT id1, SUM(score)as table3_score FROM table3 GROUP BY id1) as t3sum ON table1.id=t3sum.id1 Donna
Re: select does too much work to find rows where primary key does not match
How about using a left outer join. Find all the rows in bar without a matching row in foo: To verify: select * from bar left outer join foo on bar.phone=foo.phone where foo.phone is null Then delete bar.* from bar left outer join foo on bar.phone=foo.phone where foo.phone is null Phil <[EMAIL PROTECTED]> wrote on 04/15/2008 05:32:38 PM: > I would have thought your not = though is matching a lot more rows every > time.. > > I would look into using where not exists as a subselect > > delete from bar where not exists (select 'y' from foo where foo.phone = > bar.phone); > > something like that. > > On Tue, Apr 15, 2008 at 5:00 PM, Patrick J. McEvoy <[EMAIL PROTECTED]> > wrote: > > > I have two MyISAM tables; each uses 'phone' as a primary key. Finding rows > > where the primary keys match is efficient: > > > > mysql> explain select bar.phone from foo,bar where foo.phone=bar.phone; > > > > ++-+---++---+- > +-+---+---+-+ > > | id | select_type | table | type | possible_keys | key | key_len | > > ref | rows | Extra | > > > > ++-+---++---+- > +-+---+---+-+ > > | 1 | SIMPLE | bar | index | PRIMARY | PRIMARY | 10 | > > NULL | 77446 | Using index | > > | 1 | SIMPLE | foo | eq_ref | PRIMARY | PRIMARY | 10 | > > ssa.bar.phone | 1 | Using index | > > > > ++-+---++---+- > +-+---+---+-+ > > 2 rows in set (0.00 sec) > > > > > > Finding rows in one table that do not match a row in the other table is > > wildly inefficient: > > > > mysql> explain select bar.phone from foo,bar where foo.phone!=bar.phone; > > > > ++-+---+---+---+- > +-+--+-+--+ > > | id | select_type | table | type | possible_keys | key | key_len | > > ref | rows| Extra| > > > > ++-+---+---+---+- > +-+--+-+--+ > > | 1 | SIMPLE | bar | index | NULL | PRIMARY | 10 | > > NULL | 77446 | Using index | > > | 1 | SIMPLE | foo | index | NULL | PRIMARY | 10 | > > NULL | 3855468 | Using where; Using index | > > > > ++-+---+---+---+- > +-+--+-+--+ > > 2 rows in set (0.00 sec) > > > > (This is the same for 'NOT', '!=', or '<>'.) > > > > The amount of work should be identical in both cases: grab a row, look up > > by primary key in the other table, proceed. > > > > My real goal is to delete rows in the smaller table if there is no match > > in the larger table: > > > >delete from bar using foo,bar where not bar.phone=foo.phone; > > > > but it runs for hours. I suppose I could SELECT INTO a new table and > > rename the tables, but that seems dorky. > > > > Is there any way to force SELECT/DELETE to look up the primary key rather > > than scan the entire index? > > > > Thanks. > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > Help build our city at http://free-dc.myminicity.com ! > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > CONFIDENTIALITY NOTICE:This email is intended solely for the person > or entity to which it is addressed and may contain confidential > and/or protected health information. Any duplication, > dissemination, action taken in reliance upon, or other use of this > information by persons or entities other than the intended recipient > is prohibited and may violate applicable laws. If this email has > been received in error, please notify the sender and delete the > information from your system. The views expressed in this email are > those of the sender and may not necessarily represent the views of > IntelliCare.
Re: Completeness rate of records
Olaf Stein <[EMAIL PROTECTED]> wrote on 03/27/2008
01:16:43 PM:
> Hey all,
>
> I have a table with 40 columns. If for a record a value is not available
the
> column is set to NULL. Is there a quick way of finding out how many
records
> have a value (NOT NULL) for 90% (or lets say 35 columns) of the columns.
>
> Thanks
> Olaf
>
Try something like:
Select
sum(case when column1 is not null then 1 else 0 end) as
column1NotNullCount,
sum(case when column2 is not null then 1 else 0 end) as
column2NotNullCount,
...
from table
You can use the concat function to create the individual column statements
so you don't have to type 35 selects items:
select concat('sum(case when ', column_name, ' is not null then 1 else 0
end) as ', column_name, 'NotNullCount,')
from information_schema.columns
where table_schema='YourDBNameHere'
and table_name= 'YourTableNameHere'
Donna
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Actually, this works too: SELECT a.username, a.first_name, a.last_name, Count(b.username) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; __ Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard <[EMAIL PROTECTED]> 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : > Richard, > > >Can I do something like this : > >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count > >FROM login_table b WHERE a.username = b.username) FROM user_list a > > Try ... > > SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count > FROM user_list a > JOIN login_table b ON a.username = b.username > GROUP BY a.username,a.first_name,a.lastname; > > PB > > - > > Richard wrote: >> Hello, >> >> This time I'm rearly not sure if this is possible to do. I've got two >> queries that I would like to bring together to make only one query ... >> >> I've got a list of users >> >> And also a login table >> >> I would like to list all users and show the number of times they have >> logged in. >> >> So to get the list of users I would do : >> >> SELECT username, first_name, last_name FROM user_list >> >> And to count the number of connections I would do >> >> SELECT COUNT(*) AS count FROM login_table WHERE username = >> $result['username'] >> >> Can I do something like this : >> >> SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS >> count FROM login_table b WHERE a.username = b.username) FROM user_list a >> >> I know that the above query can not work but It's just to give a >> better idea about what I'm trying to do . :) >> >> If I do a join, I will the username repeated for each login. >> >> Thanks in advance, >> >> Richard >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard <[EMAIL PROTECTED]> 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : > Richard, > > >Can I do something like this : > >SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count > >FROM login_table b WHERE a.username = b.username) FROM user_list a > > Try ... > > SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count > FROM user_list a > JOIN login_table b ON a.username = b.username > GROUP BY a.username,a.first_name,a.lastname; > > PB > > - > > Richard wrote: >> Hello, >> >> This time I'm rearly not sure if this is possible to do. I've got two >> queries that I would like to bring together to make only one query ... >> >> I've got a list of users >> >> And also a login table >> >> I would like to list all users and show the number of times they have >> logged in. >> >> So to get the list of users I would do : >> >> SELECT username, first_name, last_name FROM user_list >> >> And to count the number of connections I would do >> >> SELECT COUNT(*) AS count FROM login_table WHERE username = >> $result['username'] >> >> Can I do something like this : >> >> SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS >> count FROM login_table b WHERE a.username = b.username) FROM user_list a >> >> I know that the above query can not work but It's just to give a >> better idea about what I'm trying to do . :) >> >> If I do a join, I will the username repeated for each login. >> >> Thanks in advance, >> >> Richard >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Send INSERT statement from MS SQL SERVER to MySQL
Mário Gamito <[EMAIL PROTECTED]> wrote on 02/12/2008 01:00:25 AM: > Hi, > > Is it possible to send an INSERT statement from a Windows server running > MS SQL SERVER 2005 to a Linux box running MySQL ? > > If so, how ? Do I need any special tools ? > > Any help would be appreciated. > > Warm Regards, > Mário Gamito We use MS SQL Server 2000 and MySQL and move data using DTS and an ODBC connection to MySQL. You can also try to set up SQL Server transactional replication to an ODBC data source. I experimented with this a while back and couldn't make it work, but it might be easier in MSSQL 2005. Donna
Re: return integer for positive values
<[EMAIL PROTECTED]> wrote on 04/05/2007 02:46:43 PM: > > I have a table with a list of pollutants. I have a table of > locations, site names, counties, etc. I can join the these tables > together and get a list of of all the pollutants at a site. But, > what I am really wanting is a list of all the pollutants with a > integer field, zero for pollutant not here, 1 for pollutant here. > > So that instead of the list I get now: > benzaldehyde > freon > formaldehyde > > I would get: > > lead 0 > acetone 0 > benzaldehyde 1 > butane 0 > freon 1 > formaldehyde 1 Simplyfying to these "pseudo" tables: Site (SiteId int not null auto_increment, SiteName varchar(100)) Pollutant (PollutantId int not null auto_increment, PollutantName varchar(100)) rlSitePollutant (SiteId, PollutantId primarykey(SiteId, PollutantId)) Then to list all sites, all pollutants and whether they exist at a site: Select PollutantName, Case when sp.PollutantId is Null then 0 else 1 END as ExistsAtSite, SiteName from Pollutant p left outer join SitePollutant sp on p.PollutantId=rlsp.PollutantId inner join Site s on rl.SiteId=s.SiteId The case statement can be very handy: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Hope that helps. Donna
RE: Is there a professional quality mySQL GUI for Linux?
"Tim Lucia" <[EMAIL PROTECTED]> wrote on 03/15/2007 07:47:29 AM: > I trade between SQLYog and SQL Exporer plugin for Eclipse. The former only > shows 1 result set at a time (boo) while the latter shows more than one > (yeah!) The former doesn't let you sort columns from your own query, only > the table preview. The latter doesn't let you sort the columns. > > Neither one is perfect. > > Tim > SQLYog 5.25 was just released and it now allows multiple result sets (one per query tab). There is also a beta release of a new monitoring tool. Donna
Restore Question
We're in the process of changing our InnoDB databases to file-per-table. I started last night with our test server. It went pretty smoothly, except for one stupid mistake on my part. I backed up all databases, deleted he data and log files, re-created the MySQL database from the script, then restored all the user databases. Everything is fine, except of course I'm missing all the users. So my questions are: 1. I had to create the new mysql database in order to get the server to start without error ([ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist).After creating a new mysql db from the script, should I have first restored the old mysql database, then the user databases? 2. Somewhere I saw that I still needed to create the InnoDB shared tablespace. What does InnoDB use this for? For the production system, I need to estimate what size to start with. 3. I restored the original mysql database as mysql_old, so I have access to the old grant tables. Any suggestions for how to recover the user permissions without redoing all the steps? Thanks, Donna
Re: Strange query.
Oh, sorry. I set up a test table and then to send the query to the list, I changed the table names and column names to match yours...but I missed some. I think this one will work. SELECT SUM(CASE when e.sid is null then 0 else 1 end) as counts, HOURS.hour FROM HOURS LEFT OUTER JOIN (SELECT sid, date_format(timestamp, '%H')as hr FROM event) as e ON HOURS.hour =e.hr WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 group by HOURS.hour Donna "Paul Halliday" <[EMAIL PROTECTED]> 01/10/2007 10:36 AM To "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> cc Subject Re: Strange query. e.c1? Giving me errors.. On 1/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Try something like this: > > SELECT > SUM(CASE when e.c1 is null then 0 else 1 end) as counts, HOURS.hour > FROM HOUR > LEFT OUTER JOIN (SELET sid, date_format(timestamp, '%H')as hr FROM > event) as e on HOURS.hour =e.hr > WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 > 04:00:00' AND sid=1 > group by HOURS.hour > > Donna > > > > "Paul Halliday" <[EMAIL PROTECTED]> > 01/10/2007 09:48 AM > > To > "Brent Baisley" <[EMAIL PROTECTED]> > cc > mysql@lists.mysql.com > Subject > Re: Strange query. > > > > > > > That query doesn't return empty values. Just to clarify what I want as > the result: > > My initial query was this, > > mysql> select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event > WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 > 04:00:00' AND sid=1 GROUP BY hour; > +--+--+ > | count(*) | hour | > +--+--+ > |4 | 04 | > |5 | 06 | > |5 | 07 | > |1 | 08 | > |7 | 09 | > | 12 | 10 | > | 73 | 12 | > | 31 | 13 | > | 50 | 14 | > +--+--+ > 9 rows in set (0.03 sec) > > What I am looking for is 0's for every empty result and up to the end > of the day. > > Thanks. > > On 1/10/07, Brent Baisley <[EMAIL PROTECTED]> wrote: > > You can't join on the result of calculations in the field selection. The > result is not associated with any table. So the problem > > isn't so much with the date_format statement, but that you are joining > on HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT. > > I would think you would be getting an error when you run your SELECT. > > Your group by can use the result of a calculation. So you may actually > have two problems, since you are grouping on HOURS.hour, the > > timestamp, the 'hour' the alias name for the calculation result. > > I'm not sure why you don't just pull the hour from the timestamp either. > > > > SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS > > LEFT JOIN event ON HOURS.hour=HOUR(timestamp) > > WHERE timestamp BETWEEN '2007-01-09 04:00:00' > > AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour > > > > - Original Message - > > From: "Paul Halliday" <[EMAIL PROTECTED]> > > To: > > Sent: Wednesday, January 10, 2007 8:39 AM > > Subject: Strange query. > > > > > > > Hi, > > > > > > I am trying to deal with empty values so that I can graph data over a > > > 24 hour period without gaps. > > > > > > I created a table called HOURS which simply has 0->23 and I am trying > > > to do a join on this to produce the desired results. I think that the > > > DATE_FORMAT in the query is screwing things up. > > > > > > The query looks something like this: > > > > > > SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT > > > JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09 > > > 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour; > > > > > > Any help would be appreciated. > > > > > > Thanks. > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > > CONFIDENTIALITY NOTICE:This email is intended solely for the person or > entity to which it is addressed and may contain confidential and/or > protected health information. Any duplication, dissemination, action > taken in reliance upon, or other use of this information by persons or > entities other than the intended recipient is prohibited and may violate > applicable laws. If this email has been received in error, please notify > the sender and delete the information from your system. The views > expressed in this email are those of the sender and may not necessarily > represent the views of IntelliCare. > > > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is
Re: Strange query.
Try something like this: SELECT SUM(CASE when e.c1 is null then 0 else 1 end) as counts, HOURS.hour FROM HOUR LEFT OUTER JOIN (SELET sid, date_format(timestamp, '%H')as hr FROM event) as e on HOURS.hour =e.hr WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 group by HOURS.hour Donna "Paul Halliday" <[EMAIL PROTECTED]> 01/10/2007 09:48 AM To "Brent Baisley" <[EMAIL PROTECTED]> cc mysql@lists.mysql.com Subject Re: Strange query. That query doesn't return empty values. Just to clarify what I want as the result: My initial query was this, mysql> select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 GROUP BY hour; +--+--+ | count(*) | hour | +--+--+ |4 | 04 | |5 | 06 | |5 | 07 | |1 | 08 | |7 | 09 | | 12 | 10 | | 73 | 12 | | 31 | 13 | | 50 | 14 | +--+--+ 9 rows in set (0.03 sec) What I am looking for is 0's for every empty result and up to the end of the day. Thanks. On 1/10/07, Brent Baisley <[EMAIL PROTECTED]> wrote: > You can't join on the result of calculations in the field selection. The result is not associated with any table. So the problem > isn't so much with the date_format statement, but that you are joining on HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT. > I would think you would be getting an error when you run your SELECT. > Your group by can use the result of a calculation. So you may actually have two problems, since you are grouping on HOURS.hour, the > timestamp, the 'hour' the alias name for the calculation result. > I'm not sure why you don't just pull the hour from the timestamp either. > > SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS > LEFT JOIN event ON HOURS.hour=HOUR(timestamp) > WHERE timestamp BETWEEN '2007-01-09 04:00:00' > AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour > > - Original Message - > From: "Paul Halliday" <[EMAIL PROTECTED]> > To: > Sent: Wednesday, January 10, 2007 8:39 AM > Subject: Strange query. > > > > Hi, > > > > I am trying to deal with empty values so that I can graph data over a > > 24 hour period without gaps. > > > > I created a table called HOURS which simply has 0->23 and I am trying > > to do a join on this to produce the desired results. I think that the > > DATE_FORMAT in the query is screwing things up. > > > > The query looks something like this: > > > > SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT > > JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09 > > 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour; > > > > Any help would be appreciated. > > > > Thanks. > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Varchar limit warning
You need to set the sql_mode to STRICT_TRANS_TABLES or STRICT_ALL_TABLES. We set this for the server in the my.cnf file. Be careful, though, because there are some third-party GUI clients that don't read the my.cnf file and thus don't set the sql_mode to what you expect. In that case, set it yourself in the client: set SESSION sql_mode='STRICT_TRANS_TABLES'; select @@session.sql_mode; You can read about sql_mode here. http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Donna Olaf Stein <[EMAIL PROTECTED]> 01/05/2007 12:37 PM To MySql cc Subject Varchar limit warning Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
RE: group by/select issue..
select s1.universityID, s1.actionID, ut.svn_dir_name (or other columns of your choice) from from SvnTBL as s1 inner join universityTBL ut on s1.univeristyID=ut.ID left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; I think you should put indexes, if you don't already have them, on SvnTBL.universityID and universityTBL.id. You also shouldn't need the group by you have in your first query below, but I can't tell you if that is hurting performance. Try putting EXPLAIN in front of the query and it'll give you some details of the query plan. Donna "bruce" <[EMAIL PROTECTED]> 01/04/2007 01:45 PM Please respond to <[EMAIL PROTECTED]> To <[EMAIL PROTECTED]> cc Subject RE: group by/select issue.. thanks for the derived tbl approach. it solved my 1st problem/issue. the final query that i used is: select distinct s1.universityID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.universityID is null; this works, in that i get the unique universityID data... i now have two additional questions... 1) in the SvnTBL, how can i also get the actionID value? if i attempt to do something like: select distinct s1.universityID, s1.actionID from SvnTBL as s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ONs1.universityID=s2.universityID where s2.universityID is null group by universityID; the query eventually returns with what appears to be the correct information. i get a distinct universityID/actionID, but the query takes ~65 secs to run... the tbl only has ~2900 rows... 2) also, if i want to do a join with another tbl, where i also want to have the select pull information from the joined tbl, is there a 'better' way to handle this... the 2nd tbl is: mysql> describe universityTBL; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | name | varchar(75) | NO | UNI | NULL|| | svn_dir_name | varchar(50) | NO | | NULL|| | city | varchar(20) | YES | | NULL|| | stateVAL | varchar(5) | NO | | NULL|| | userID| int(10) | NO | | 0 || | ID| int(10) | NO | PRI | NULL| auto_increment | | parsefilename | varchar(50) | NO | | NULL|| | statusID | int(1) | NO | | 1 || +---+-+--+-+-++ 8 rows in set (0.01 sec) the join would take place on SvnTBL.universityID=universityTBL.ID thanks for helping me to see what's going on... my initial approach is to simply do the unique select on only the SvnTBL, and then have an iterative loop through the resulting data, where i then query the universityTBL each time... however, this results in the app having to hit the db a number of times... thoughts/comments/ thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 10:01 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com; 'Peter Bradley' Subject: RE: group by/select issue.. Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna "bruce" <[EMAIL PROTECTED]> 01/04/2007 12:49 PM Please respond to <[EMAIL PROTECTED]> To "'Peter Bradley'" <[EMAIL PROTECTED]> cc "'Chris White'" <[EMAIL PROTECTED]>, Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql> describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0
RE: group by/select issue..
Use a derived table (untested query): select distinct universityID from SvnTBL s1 left outer join (select universityID from SvnTBL where actionID =3) as s2 ON s1.universityID=s2.universityID where s2.university ID is NULL I'm not sure if derived tables are in all versions of MySQL, I use MySQL 5.0. If your tables are big, you'll probably need to add an index on universityID. Hope that helps. Donna "bruce" <[EMAIL PROTECTED]> 01/04/2007 12:49 PM Please respond to <[EMAIL PROTECTED]> To "'Peter Bradley'" <[EMAIL PROTECTED]> cc "'Chris White'" <[EMAIL PROTECTED]>, Subject RE: group by/select issue.. hi peter i must be missing something. the following is my actual schema. i have a test tbl with ~2900 rows... only a few of the rows have an actionID=3. each universityID can have multiple actionIDs mysql> describe SvnTBL; +--+--+--+-+++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+---++ | universityID | int | NO | | 0 || | actionID | int | NO | | 0 || | statusID | int | NO | | 0 || | _date| timestamp| YES | | CURRENT_TIMESTAMP | | | ID | int | NO | PRI | NULL | auto_increment | | semseterID | int | NO | | 0 || +--+---+--+-+---++ 6 rows in set (0.09 sec) when i do: select distinct universityID, from SvnTBL where actionID !=3; i get return of 2879 rows, which is the same thing i get when i do: select distinct universityID, from SvnTBL; when i do: mysql> select universityID, actionID from SvnTBL -> where actionID =3; +--+--+ | universityID | actionID | +--+--+ |1 |3 | |2 |3 | |3 |3 | +--+--+ 3 rows in set (0.00 sec) which tells me that i have 3 'groups' (on universityID) that have actionID=3. however, each of these universityID, can also have actionID=(1,2) as well. so how can a query be created to return the universityID (groups) that don't have an actionID=3... when i tried, SELECT DISTINCT universityID FROM SvnTBL WHERE actionID != 3 i got the same as if i did: SELECT DISTINCT universityID FROM SvnTBL; thanks.. -Original Message- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 9:32 AM To: [EMAIL PROTECTED] Cc: 'Chris White'; mysql@lists.mysql.com Subject: Re: group by/select issue.. Bruce, Try: SELECT DISTINCT NAME FROM DOG WHERE STATUS != 3 Should do the trick. You obviously don't want the STATUS field. If you include it, you'll get more than one line per name. Similarly for ID. If you want to include the STATUS or ID fields, then you obviously want more than one line (otherwise what would you expect to go in there?). HTH Peter Ysgrifennodd bruce: > hi chris... > > your query, > >>SELECT name FROM dog WHERE status = 3 GROUP BY name<< > > will actually give the items where status=3 > > however, i can't get the resulting issues by doing 'status!=3', because the > tbl has multiple status for a given name, so the query will still return the > other status that aren't equal to '3' for the given name... > > > > -Original Message- > From: Chris White [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 04, 2007 9:07 AM > To: [EMAIL PROTECTED] > Cc: mysql@lists.mysql.com > Subject: Re: group by/select issue.. > > > bruce wrote: > >> i'm trying to figure out how to create a select query that groups >> the tbl around 'name' such that if i want all names that do not >> have a status=3, i'd get a single row for 'sue' and 'bob' >> > > I'm not sure why `SELECT name FROM dog WHERE status = 3 GROUP BY name;` > wouldn't give you what you'd want (or that's possibly what you're > looking for?). If that's the answer then "wee", if not I'll throw my > lost flag in the air. > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender
Re: SELECT...GROUP BY WITHIN GROUP BY
I'm not sure that this is exactly what you want, but I think you can use the WITH ROLLUP modifier: select district, town, street, surname, count(surname) from test5 group by district asc, town asc, street asc, surname asc WITH ROLLUP Here's a link to the MySQL documentation on WITH ROLLUP http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html Donna "Kerry Frater" <[EMAIL PROTECTED]> 11/28/2006 02:31 PM Please respond to <[EMAIL PROTECTED]> To cc Subject SELECT...GROUP BY WITHIN GROUP BY Hope I have the right group. I am working out how to get groups within groups. e.g. I have a table with 4 columns C1,C2,C3 & C4 I am looking to select data so that I can get C1 group item C2 Group item C3 Group Item C4 detail End of C3 Group Item count/totals of C3 End of C2 Group Item count/totals of C2, C3 End of C1 Group item count/totals of C1, C2, C3 to describe the gorups let us say the 4 columns are district,town,street,surname. A full "report" would be all the surnames in surname order within street At the end of each "street" I would also get the number of surnames in that "street" within town At the end of each "town" I would also get the number of "streets" and "surnames" within the town within district At the end of each "district" I would also get the number of "towns", "streets" and "surnames" within the district At the end of selecting all I get the number of "districts", "towns", "streets" and "surnames" Thanks Kerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Many-Many relation, matching all
Yes, it's true that the query won't work if you have duplicate aid,bid rows. I probably shouldn't have assumed that there would be a PK or unique constraint on aid,bid. So if that isn't the case, you can add a distinct: SELECT AID FROM AhasB WHERE BID in (1,2) GROUP BY AID HAVING count(distinct BID) =2 Donna Peter Brawley <[EMAIL PROTECTED]> 11/28/2006 10:53 AM Please respond to [EMAIL PROTECTED] To James Northcott / Chief Systems <[EMAIL PROTECTED]>, "mysql@lists.mysql.com" cc Subject Re: Many-Many relation, matching all James Northcott / Chief Systems wrote: >SELECT AID >FROM AhasB WHERE BID in (1,2) >GROUP BY AID >HAVING count(BID) =2 Not quite, since that will catch aid's with two bid=1 rows or bid=2 rows: SELECT * FROM t; +--+--+ | i| j| +--+--+ |1 |4 | |1 |5 | |3 |5 | |3 |5 | +--+--+ SELECT i FROM t WHERE j in (4,5) GROUP BY i HAVING count(j) =2; +--+ | i| +--+ |1 | |3 | +--+ SELECT i,GROUP_CONCAT(j) AS list FROM t GROUP BY i HAVING list='4,5'; +--+--+ | i| list | +--+--+ |1 | 4,5 | +--+--+ PB - > Peter Brawley wrote: >> >I want to find all A's such that >> >they have exactly B's 1 and 2 >> >SELECT A.ID, group_concat(BID ORDER BY BID) as Bs >> >FROM A INNER JOIN AhasB ON A.ID=AID >> >GROUP BY A.ID >> >HAVING Bs='1,2' >> >> Why the join? Doesn't your ahasb bridge table already incorporate the >> join logic? If your requirement is to retrieve all aid's with exactly >> one instance of bid=1, exactly one with bid=2, and no other bid's, >> why not just ... >> >> SELECT aid,GROUP_CONCAT(bid) AS list >> FROM ahasb >> GROUP BY aid >> HAVING list='1,2'; >> >> PB >> > I actually need some of the other columns from A, but you're correct, > this does work. I did discover though that the ORDER BY in the > group_concat is important, since MySQL doesn't always pick the same > order for the list. > > [EMAIL PROTECTED] wrote: >> I think this will work: >> >> SELECT AID >> FROM AhasB WHERE BID in (1,2) >> GROUP BY AID >> HAVING count(BID) =2 >> >> >> Donna >> > > Thank you, this is actually very helpful. The where clause uses the > index I have in the table to screen out many more rows early in the > query, and it also very nicely avoids the string compare on the > group_concat. I also wasn't aware that you could use an aggregate > function in the HAVING clause without it appearing in the SELECT clause. > > Thanks again, this solves the problem quite elegantly, and I probably > never would have thought of it. > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.14.19/555 - Release Date: 11/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Many-Many relation, matching all
I think this will work: SELECT AID FROM AhasB WHERE BID in (1,2) GROUP BY AID HAVING count(BID) =2 Donna James Northcott / Chief Systems <[EMAIL PROTECTED]> 11/27/2006 04:35 PM To mysql@lists.mysql.com cc Subject Many-Many relation, matching all Hello, I'm having a conceptual issue with many-to-many relations. I have the following structure: Table A ID (int primary key) ... descriptive columns ... Table B ID (int primary key) ... descriptive columns ... Table AhasB AID (references A.ID) BID (references B.ID) So, each A can have any number of B's, and each B can be had by any number of A's. I want to find all A's such that they have exactly B's 1 and 2. So far, the only working solution I have looks like: SELECT A.ID, group_concat(BID ORDER BY BID) as Bs FROM A INNER JOIN AhasB ON A.ID=AID GROUP BY A.ID HAVING Bs='1,2' This does work fine, but it seems very clunky - in particular, it's annoying to have to always remember to add the group_concat to the SELECT clause so that I can filter based on it in the HAVING clause, and it also doesn't scale particularly well, since HAVING isn't applied until the final stage of the query, so many rows are included in the result set that it would seem I ought to be able to filter earlier. Any ideas on how I can do this better/more efficiently? Also, does anybody have a name for what I'm trying to do? I'm finding it hard to even Google for information, since a can't seem to describe what I want concisely enough for a search. Thanks in advance, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Stored Procedure Security Question
When creating a stored procedure, you can set the sql security characteristic to either definer or invoker. As an example, I have a stored procedure that does a select from a table, and an application user (appuser) that calls the stored procedure. If the sql security is set to invoker, then I have to give appuser both select and execute privileges. If the sql security is set to definer, then the definer needs select privileges and appuser only needs execute. What I'd like to be able to do is to give appuser the execute privilege and not have to give any privileges on the underlying tables to the definer. Is this possible? We do almost 100% of our work through stored procedures. It would be a lot easier to manage just the execute privilege. Are there reasons why this is not a good idea? This is how we manage security with our other DBMS and it's worked quite well, but it doesn't have the definer/invoker characteristic for stored procs either. Any suggestions about how to manage users/privileges would be appreciated. Donna
Re: AW: Count of children
I've found this website to be extremely helpful: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html Donna DeVaudreuil André Hänsel <[EMAIL PROTECTED]> 09/27/2006 10:55 AM To <[EMAIL PROTECTED]> cc Subject AW: Count of children I will use any model that is suitable. ;) I am somewhat familiar with both tree models but I can't come up with a method to get the count of all sub- and sub-sub-nodes in either of them. > -Ursprüngliche Nachricht- > Von: Peter Brawley [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 27. September 2006 16:49 > An: André Hänsel > Cc: mysql@lists.mysql.com > Betreff: Re: Count of children > > André, > > >I want the count of all sub-entries for a specific entry. > > Depends on the model you are using--edge list or nested sets? > > PB > > - > > André Hänsel wrote: > > I have a table with id and parent_id. > > I want the count of all sub-entries for a specific entry. > > > > I found several documents about working with graphs/trees > in MySQL but I > > could not find a solution for my problem. > > > > I can imagine two possibilities, but one is memory > intensive and the other > > one creates load on updates. > > The first is, that I select all entries and then use a > procedural language > > to determine recursively whether an node is a sub-node of > the specific node. > > The second is, that I store the sub-node count with each > node and when I do > > an insert, I walk the tree upwards and increment the node-counts. > > > > Is there a smart solution/best practice for my problem? > > > > Now I can't think of another sentence starting with an i. ;-) > > > > Best regards, > > André > > > > > > > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.407 / Virus Database: 268.12.9/457 - Release > Date: 9/26/2006 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Restore Questions
We're using MySQL 5.0.22 on CentOS 3.7 (running on VMWare). We use InnoDB tables and also use views, stored procedures, and functions. I had to move our development databases to a new server this week by doing a backup with mysqldump and then restoring the databases on the new server. I ran into a weird problem, in one database the views were restored as tables! I also got this error: ERROR 1418 (HY000) at line 206: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) Looking at the dump file, in general the sequence of statements is: 1. create tables 2. insert into tables 3. create tables that are really views in the original db 4. create functions 5. create stored procedures 6. drop the tables that should be views and re-create them as actual views In my case, #4 caused the error, #5 succeeded (all procs were created), and then it's like #6 doesn't happen. I fixed this by adding deterministic to the offending function, but it brings up some questions. Why does mysqldump first create views as tables, then drop and re-create them as views? Why did the stored procs get created after the error, but not the views? I also tried to create another function without specifying deterministic, and was unable to...so how did I create that function in the first place? Note that when we built out the new server, we used the same my.cnf file so the two should have been configured the same. Thank you. Donna DeVaudreuil
Re: Tables/sum
How about: select sum(t1.column1 + t2.column2 +t3.column3) as Columnsum from Table1 t1 inner join Table2 t2 on t1.id=t2.id inner join Table3 t3 on t2.id=t3.id where t1.id=n group by t1.id This is a rough cut that assumes the id value in the join exists in all three tables. If it's missing in any one of the tables, then the query will return null. If that is not okay, then you'd have to do something with outer joins...without knowing what you're trying to find it's hard to be more specific. Donna "Peter South" <[EMAIL PROTECTED]> 08/17/2006 05:05 PM To cc Subject Tables/sum Can anyone tell me how to add up values in different tables? For example Table1.Column1 + Table2.Column2 + Table1.Column3 Where id(row) = n Thanks Peter -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Query Question
Here's a single query version of Douglas's solution: select @id:=6; select distinct t.testid, (select max(testid) from t where testid <@id) as previousId, (select min(testid) from t where testid > @id) as nextId from t where [EMAIL PROTECTED] Donna Douglas Sims <[EMAIL PROTECTED]> 08/14/2006 10:47 AM To Michael DePhillips <[EMAIL PROTECTED]> cc Dan Julson <[EMAIL PROTECTED]>, mysql@lists.mysql.com Subject Re: Query Question I think this will do it, although it takes three queries. I'm assuming the id values are unique, even if there can be gaps (that's what you might get with an AUTO_INCREMENT field). If the values are not guaranteed to be unique then this may not give what you want (if there are multiple rows with the same value you're looking for, e.g. 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not three fives.) SELECT @id:=5; SELECT * FROM t WHERE id<(SELECT MAX(id) FROM t WHERE id<@id) ORDER BY id DESC LIMIT 1; SELECT * FROM t WHERE [EMAIL PROTECTED] LIMIT 1; SELECT * FROM t WHERE id>(SELECT MIN(id) FROM t WHERE id>@id) ORDER BY id ASC LIMIT 1; But as to putting that in one statement... it might be better just to do it as three. Douglas Sims [EMAIL PROTECTED] On Aug 14, 2006, at 9:32 AM, Michael DePhillips wrote: > Hi Dan, > > Thanks for the prompt reply, > > As I described it yes, you are correct, however, the id may not > always be one(1) value away. So the number one needs, somehow, to > be replaced with a way to get the "next largest value " and the > "previous less than" value. > > Sorry for the lack of precision in my prior post. > > Regards, > Michael > > > Dan Julson wrote: > >> Michael, >> >> I would think this is what you want. >> >> Select ID from T1 where ID BETWEEN ( - 1) and (> in question> + 1) >> If you want distinct values, place the distinct keyword in front >> of ID (i.e. Select DISTINCT ID... >> >> This should do it for you. >> -Dan >> >> Hi, >> >> Does anyone have a clever way of returning; a requested value >> with one >> value less than that value, and one value greater than that value >> with >> one query. >> >> For example T1 contains >> >> ID >> 1234 >> 1235 >> 1236 >> 1238 >> >> select ID from T1 where ID = 1235 and ID<1235 and ID >1235 LIMIT 3 >> (obviously this doesn't work) I would want to return >> >> 1234 >> 1235 >> 1236 >> >> or; >> select ID from T1 where ID = 1237 and ID<1237 and ID >1237 LIMIT 3 I >> would want >> >> 1236 >> 1238 >> >> I would be surprised if there was no way of doing this.but then >> again, I'm often surprised >> >> Thanks >> Michael >> >> -- >> Michael DePhillips >> www.star.bnl.gov >> >> > > > -- > Michael DePhillips > www.star.bnl.gov > > > -- > 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] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: WHERE problem, or is it a problem?
Use the HAVING clause: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid HAVING count(*) >=31 ORDER BY numberofans DESC, part.memberid; "Peter Lauri" <[EMAIL PROTECTED]> 07/26/2006 09:58 AM To cc Subject WHERE problem, or is it a problem? Best group member, I have this query on MySQL version 4.0.27: SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid) LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid) WHERE pdfauth.id IS NULL GROUP BY part.memberid ORDER BY numberofans DESC, part.memberid; This works fine, However, I only want the results where COUNT(*)>=31. So I tried: SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans FROM tblparticipants part LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid ) LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid = pdfauth.memberid) WHERE pdfauth.id IS NULL AND COUNT( * ) >=31 GROUP BY part.memberid ORDER BY numberofans DESC , part.memberid But then MySQL answered with: # - Invalid use of group function What is the problem here? Why can I not do a WHERE COUNT(*)>=31? Is there any other way to just select the COUNT(*)>=31? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: {Spam?} Limiting results from joins
How about:
select * from products p
inner join manufactors m on p.manufactor_id=m.manufactor_id
inner join items i on p.product_id=i.product_id and i.item_updated=1
Donna
"Kim Christensen" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/12/2006 06:15 AM
Please respond to
[EMAIL PROTECTED]
To
mysql@lists.mysql.com
cc
Subject
{Spam?} Limiting results from joins
Hey list;
Consider this statement:
SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id
= m.manufactor_id && p.product_id = i.product_id;
Now, each unique product_id from "products" can have more than one
entry in the "items" table, but I only want to fetch the one which
fullfills a certain criteria. In this case, I want the statement only
to JOIN the row if the column "item_updated" from the "items" table
equals "1".
Is this the proper way to solve this:
SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id
= m.manufactor_id && p.product_id = i.product_id && i.item_id =
(SELECT item_id FROM items i2 WHERE i2.item_updated = 1);
I find the above solution VERY slow, almost as if I have missed out on
a very fundamental part of the logic - but it does get the work done.
How could I speed this up, or solve it in another statement?
Regards
--
Kim Christensen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information. Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may violate
applicable laws. If this email has been received in error, please notify
the sender and delete the information from your system. The views
expressed in this email are those of the sender and may not necessarily
represent the views of IntelliCare.
Transfer users and permissions between servers
Is there an easy way to transfer users and their permissions from one server to another? We are moving databases to a new server and have a lot of users to move as well. Thanks in advance! Donna
Re: {Spam?} MySQL and dates puzzle
I think there are four cases to consider (hopefully the "picture" will
come through okay).
starttime|--|endtime The time span in consideration
*-* Case 1: ta has mtg that
starts before starttime, mtg ends between starttime and endtime 1
*--* Case 2: mtg starts before
starttime, mtg ends after endtime 2
*-* Case 3: mtg starts and mtg
ends between startime and endtime 3
*---*Case 4: mtg starts between
starttime and endtime, mtg ends after endtime 4
However the four can be reduced to two:
a. A meeting starts before the starttime and ends after the starttime
(cases 1 and 2)
or
b. A meeting starts between the starttime and the endtime (cases 3 and
4).
So to check if the TA is busy between 17:30 and 18:10
SELECT count(TAID) as total
FROM tbl_schedule
where TAID = 1
and ((starts <'2005-10-27 17:30' and ends >'2005-10-27 17:30')
or (starts >= '2005-10-27 17:30' and starts < '2005-10-27 18:10'))
Regards,
Donna
"C.F. Scheidecker Antunes" <[EMAIL PROTECTED]>
10/27/2005 05:31 PM
To
mysql@lists.mysql.com
cc
Subject
{Spam?} MySQL and dates puzzle
Hello all,
I have a TA table to record TA UNAVAILABLE times.
This table is quite simple. It has a TAID number, a start date and an
end date.
tbl_schedule {
TAID integer,
starts datetime,
ends datetime }
A valid entry would be a TA whose id is 1 and between 17:00 and 18:00 he
is busy.
So:
1,'2005-10-27 17:00:00','2005-10-27 18:00:00'
Each ta can have more than one entry per day. He might be a busy TA and
have a lot
of meetings scheduled. The meetings do not have to be 1 hour length,
they can be 5 or 10 minutes.
So something like this would also be valid:
1,'2005-10-27 17:05:00','2005-10-27 17:10:00'
Now, I need to check, given a start and end dates, if that would overlap
with some record already present
in the database.
If I want to know if the TA is busy between 17:30 and 18:10 I could I
issue something like this:
SELECT count(TAID) as total FROM tbl_schedule where (TAID = 1) AND
(('2005-10-27 17:30' BETWEEN starts AND ends) OR ('2005-10-27 18:10'
BETWEEN starts AND ends))
It would return a number not zero as total if the dates are between the
registered database. However, this does not work properly.
Here's a case when it does not work:
Say I want to check between 14:00 and 20:00. The TA is busy from 17:00
and 18:00, hence if I try to schedule a meeting that will go
from 14:00 to 20:00 with the statement above it would return 0 as total.
This is not good because I need to know that given 14:00 to 20:00 that
would
not overlap with any previous engagement on the database. Since the TA
is busy from 17:00 to 18:00 I must know that I cannot schedule anything
like
that.
Can anyone help me on this issue? How can I check given a start and end
datetime that it does not overlap with what is in the database?
Thanks,
C.F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information. Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may violate
applicable laws. If this email has been received in error, please notify
the sender and delete the information from your system. The views
expressed in this email are those of the sender and may not necessarily
represent the views of IntelliCare.
