Query that returns two character records from MySQL table
Today I will show you how to get records of 2 characters that are mixed in with multiple character records. This particular example I experienced from my work. All 52 states in US each has 2 names: full name and abbreviation name. For example: California is a full name and CA is its abbreviation. However in Canada a state has only full name. For example: Manitoba. The issue here is that when you have a state table in your database that contains the states of…
MySQL Error Code 1215: “Cannot add foreign key constraint”
Why am I unable to create a constraint? The error message doesn’t help much. You just get the following line when you run this SQL statement: ALTER TABLE user_class ADD FOREIGN KEY (user_id) REFERENCES user(id); Error Code: 1215 Cannot add foreign key constraint If you try running some of these SQL statements from mysql ( https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html#foreign-keys-adding ), still same error occurred without a reason. You may have to double check a few things within related tables and their fields. 1) The table or index the constraint…
Get multiple records in MySQL Database by number of occurrences
If you have problem remembering all abbreviations of U.S. States, use the query below to get data from MySQL Database. In MySQL DB, you must have a state table that include id, state_full_name, state_abbreviation and all records. You must have a table of all users containing some records. The example below are from my own DB, the names of the 2 tables are tblstates and tbluserdetails. select s.State_name, c.state_name, count(c.state_name) as NumberOccurrences from tblstates s, tbluserdetails c where s.State_Abrv = c.state_name group by c.state_name having…
How to find duplicate email address using mysql query
Some point in the future, you’ll have to get rid of some duplicated email records in your database table. How will you do that? Well, here is a handy query for finding duplicates of any field in a table. Suppose you want to find all email addresses in a table that exist more than once, use the query below: select email_address, count(email_address) as NumberOccurrences from table_user_info group by email_address having ( count(email_address) > 1 ); This query will give you the results below: email_address NumberOccurrences…
MySQL Installation
After installing MySQL, go to MySQL directory on your pc, open the file my.ini and change the data directory where you’ll store all your data in the future. By default, it’ll define your pc’s Documents directory as shown below. datadir=”C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/” However you can point it to Data directory under MySQL dir.