Tag Archives: SQL

Select count from multiple tables

Posted on July 13, 2010 by Theis Mackeprang

Sometimes a parent table have more than one table with multiple references to a field in the parent table. Then the question is how to count references from more than one table in a single query. I have found 5 different ways to achieve this result, and therefore I have run a few tests, to see which was better.

To test this I made this small PHP script, that creates a parent table and child table. Then a loop populates the tables with 1,000 parent rows, approximately 15 children of type 1 and approximately 75 children of type 2. This is done before each query, to make sure that all queries have the same conditions.

Continue reading →
Posted in MySQL, PHP | Tagged , , , , , | 2 Comments

Foreign keys and MySQL (errno: 150)

Posted on July 12, 2010 by Theis Mackeprang

Today I tried to execute the SQL

mysql> ALTER TABLE parent_child ADD FOREIGN KEY (parent_id)
	REFERENCES parent(id) ON DELETE CASCADE;

in MySQL, and got the error

Can't create table 'db.#sql-aa1_1' (errno: 150)

I tried to look up error number 150 on the internet, but found many reasons why error no. 150 could occur; none was my case. Common mistakes that causes this error are:

Continue reading →
Posted in MySQL | Tagged , , , | Leave a comment