mysql
mysql two column primary key with auto-increment
if you are using myisam http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups. CREATE TABLE … Read more
MySQL Counting Distinct Values on Multiple Columns
Just list multiple columns in the GROUP BY clause. SELECT computer, user, count(*) AS count FROM login GROUP BY computer, user
Incorrect key file for table ‘/tmp/#sql_3c51_0.MYI’; try to repair it [duplicate]
The problem is caused by the lack of disk space in /tmp folder. The /tmp volume is used in queries that require to create temporary tables. These temporary tables are in MyISAM format even if the query is using only tables with InnoDB. Here are some solutions: optimize the query so it will not create … Read more
Finding all parents in mysql table with single query (Recursive Query)
SELECT T2.id, T2.title,T2.controller,T2.method,T2.url FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 31, @l := 0) vars, menu m WHERE @r <> 0) T1 JOIN menu T2 ON T1._id = T2.id ORDER BY T1.lvl DESC; … Read more
Multiple table joins in rails
To rewrite the SQL query you’ve got in your question, I think it should be like the following (though I’m having a hard time fully visualizing your model relationships, so this is a bit of guesswork): RagaContextApplicantsSong. joins(:raga_contest_applicants => [:raga_content_rounds], :contest_cat). group(‘raga_contest_rounds.contest_cat_id’) …such that the joins method takes care of both of the two joins … Read more
Can I conditionally enforce a uniqueness constraint?
Add another column called something like isactive. The create a unique constraint on (username, isactive). Then you can have both an active and inactive user name at the same time. You will not be able to have two active user names. If you want multiple inactive names, use NULL for the value of isactive. NULL … Read more
Rails 3 query on condition of an association’s count
No need to install a gem to get this to work (though metawhere is cool) Customer.joins(:purchases).group(“customers.id”).having(“count(purchases.id) > ?”,0)
DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
What @Matus and @CeesTimmerman said about MSSQL, works in MySQL 5.1.73 too: delete <alias> from <table> <alias> where <alias>.<field>…
Mysql: How to query a column whose type is bit?
SELECT * FROM table WHERE active = (1)