Sql Server & SSRS Tips

Some time NOT IN condition in where clause gives you unexpected and null result in mysql. So to avoid this use "where column name is not null" in subquery like the bold text in example below. SELECT * FROM PatientsMigration  WHERE LTRIM(RTRIM(nhinumber)) NOT  IN  (SELECT LTRIM(RTRIM(nhinumber))  FROM patients WHERE nhinumber IS NOT null)

Mysql SQL Error (1267): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' If you face this error due to different collations on the tables you are joining, So you need to set collation like this in joins. Select * FROM data_temp d inner JOIN offices o ON d.School_Name COLLATE UTF8MB4_GENERAL_CI =o.name COLLATE UTF8MB4_GENERAL_CI  

-- To export large CSV file into MySQL database, first you need to create that table (empty). -- Remove (comment) "strict_trans_tables" sql_mode from your mysql configuration (my.cnf in linux and      my.ini in windows). --  Run MySQL command line client from the start menu. --  The console will open, provide MySQL password. --  select your database          use <name_of_your_database>; --  Execute below command:           LOAD DATA INFILE 'D:\\Data.csv' IGNORE INTO TABLE your_table_name fields terminated                     BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'  LINES TERMINATED BY '\r\n'; --  IGNORE keyword in above statement will ignore the errors and rows in csv that have errors and            export remaining rows in table. -- If your csv don't have any errors and mis...

 To load a large SQL file into the database (or as database). -- In Windows Run MySQL command line client from the start menu: -- console will open, provide MySQL password -- select your database use <name_of_your_database>; -- run below command provide your SQL file path source <path_of_your_.sql> For example: source F:\iConsult Work\Tipp - Trade Information Portal\3years_TradeStat_FullData.sql -- In linux upload that SQL file in public_html from cpanel, then copy the path of that file from cron job -- go/run terminal from cpanel, then connect the database with 'mysql -u user_name -p' command. -- then select your database with 'use your_database' command -- and then execute the below command after changing the path after source keyword. For example: source /home/dhalabga/public_html/tiger_data_backup_tiger_force_backup.sql To export / load large table data into file(csv) -- This will export large table data into CSV, but before this, you have to set secure_...

 Break comma-separated string/column/parameter into multiple rows in MYSQL. with recursive     cs_string as ( select 'a,b,c,d' as items),     cs_rowID as ( select 1 as n union select n + 1 from cs_rowID, cs_string         where n <= length(items) - length(replace(items, ',', ''))) select distinct substring_index(substring_index(items, ',', n), ',', -1)  group_name from cs_rowID, cs_string Output:

 mySQL search query on multiple columns with a single parameter. set @search='32' ; select DISTINCT p.id as product_id, p.name as title, p.sku, p.description,     p.material,c.name as color, ct.name as category from  products p inner join color_product cp on p.id=cp.product_id inner join colors c on cp.color_id=c.id inner join categories ct on p.category_id=ct.id where p.name  LIKE CONCAT("%",@search,"%") OR   p.sku  LIKE CONCAT("%",@search,"%") OR       p.description  LIKE CONCAT("%",@search,"%") OR       p.material  LIKE CONCAT("%",@search,"%") OR       c.name   LIKE CONCAT("%",@search,"%") OR       ct.name   LIKE CONCAT("%",@search,"%") ;