"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 man
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) NU
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/mysqlquer
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 scri
Try show global status like 'com_select';
Donna
"Jim Lyons"
<[EMAIL PROTECTED]
.com
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_s
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 <[EMA
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) o
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
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
[
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
<[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
"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
>
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
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
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
g
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
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.universi
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
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:
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(disti
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 iss
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'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. ;)
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
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
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/1
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.
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]
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
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 endtim
31 matches
Mail list logo
