mysql - find grandparents/grandchildren

Another interesting set of queries are parent, grandparent queries.

To find all the grand children nodes do this.

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null;

To find all the grand parents with more than 2 grand children do this.

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null group by a.parent_id having count(a.parent_id) > 2;

To produce the full family tree of a grandchild node do this

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id;

To find all nodes whos grandparent(and possibly parent) is unknown do this;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id where a.parent_id is null;

Here is my full test sql.. try it out

create table kids (id integer, parent_id integer);
describe kids;
insert into kids values (1,null);
insert into kids values (3,1);
insert into kids values (4,2);
insert into kids values (5,2);
insert into kids values (6,4);
insert into kids values (7,2);
insert into kids values (8,7);
insert into kids values (9,7);
insert into kids values (10,7);
insert into kids values (11,3);
select * from kids;
select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null;

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null group by a.parent_id having count(a.parent_id) > 2;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id where a.parent_id is null;

My sql -- finding duplicates in a column

Here is how to find all entries that have duplicates in a mysql database

select email, count(email) from emails group by email having count(email) > 1;

So what is happening. To me it appears that the SQL boys have decided that selects can act in two independent ways:
1) "normal mode" - give me all the results
2) "aggregate mode" - give me groups of results represented by the first group member

It appears to me that this "aggregate mode" is triggered if either the "group by" or an aggregate function is present in the statement.

To be more specific;
1) When the "GROUP BY" is not present but an aggregate function is used then it is assumed that the data is one large group and only a single row is returned. This row appears to have the data from the first row of the normal select and the results of any aggregate function applied access the entire normal select results.
2) However if the "GROUP BY" is present then the functions operate on the individual groups. The resulting row(s) will use the normal selects first row for the normal data in the group and will apply any functions across the entire group.

This statement here will demonstrate the second rule in action more clearly.

select email, count(email), num, sum(num), avg(num), min(num), max(num) from emails group by email;

Here is the full test sql which brought me to my conclusions;

create table emails (email text, num integer);
describe emails;
insert into emails values("a@a.com", 1);
insert into emails values("b@a.com", 2);
insert into emails values("b@a.com", 3);
insert into emails values("c@a.com", 4);
insert into emails values("d@a.com", 5);
insert into emails values("d@a.com", 6);
insert into emails values("d@a.com", 7);
insert into emails values("b@a.com", 8);
select * from emails;
select distinct email from emails;
select email from emails group by email;
select email, count(email) from emails;
select email, count(email), num, sum(num), avg(num), min(num), max(num) from emails group by email;
select email, count(email) from emails group by email having count(email) > 1;

There are several functions that operate on the "group"
sum(), count(), avg(), min(), max(), etc

Refer here for more info;
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Mysql grant syntax

As unbelievable as it is I forget the grant syntax way to easily...

GRANT ALL ON database.* TO username@'localhost' IDENTIFIED BY 'password';

Finding all orphaned records where there are multiple parent tables and columns

Finding all orphaned records where there are multiple parent tables and columns;

select id from addresses where id not in (select * from ((select address_id as 'id' from orders) union (select shipping_address_id as 'id' from orders) union (select address_id as 'id' from customers) union (select shipping_address_id as 'id' from customers) order by id) as ui);

mysql stray query find and kill

show processlist;
show full processlist;
KILL QUERY <id>;

finding the size of tables in mysql

show table status

Executing sql from rails console

All ways useful...

ActiveRecord::Base.connection.execute("SELECT * FROM users")
ActiveRecord::Base.connection.execute("select * from customers").fetch_row
results = ActiveRecord::Base.connection.execute("select * from customers")

while row = results.fetch_row do
# process row here
end

mysql bug?

Mysql converting strings to numbers... be careful;

SELECT id FROM table_name WHERE (id = '2 asdfasdf  20');