GET HIERARCHICAL VALUES PRESENT IN SAME COLUMN OF A TABLE

Lets discuss about the parent and child relationship present in the same column of a table. That means both exists in the same column and there is always a identifier which says whether a record belongs to parent value or it belongs to child value. See the below data .As it is clearly seen from the data, parent and child record can be easily identified from column c1. Here , we need to apply self join to get parent and child values in the same row. See the below Query.…

Count the number of nulls using left join

Today, I will discuss about How to calculate the number of nulls that gets populated with left join or %age of matched data or count of not nulls using left join. CASE 1:Calculate the number of nulls that gets populated from right hand table using left join.Assume , I have two employee table EMP_TBL1 and EMP_TBL2. See the data for the same. EMP_TBL1 EMP_TBL2 below is the code used to get number of nulls that comes from right side table using left join. select count(*)-count(y_empid) from ( select x.emp_id ,y.emp_id…

get position of all delimiter values in a string

Today, I will explain about “How to get position and values separated by delimiter in a string”. Delimiter may be comma,pipe,colon etc. STEP1 : Get position of all delimiters in a string. I am explaining with SQL-server as Database. ;with T as ( select 0 as row_id, charindex(‘,’, @string) pos union all select pos + 1, charindex(‘,’, @string, pos + 1) from T where pos > 0)select * from T For example , consider a string “Harsimran,Kaur,91-123-456-7890” . So, it has first name and last name and dummy mobile number.…

Query to get all records not matching in all tables.

Today, I will discuss about the scenario/Problem which i faced in current project. Problem Statement : You have two Employee tables having data mentioned below. Emp-1 EmpId EmpName 1 Shivani 2 Gaurav 3 Radhe 4 Rahul 5 Anil Emp-2 EmpId EmpName 1 Shivani 6 Ashok 3 Radhe 7 Vikram 4 Rahul Output : EmpId EmpName 2 Gaurav 5 Anil 6 Ashok 7 Vikram Solution : Here, you need to use except or minus based on the database you are using and union clause. See the below query for the same.…