The Following is the schema
+---------+---------+
| Employee Table |
+---------+---------+
| EmpId | Name |
+---------+---------+
| 1 | John |
| 2 | Lisa |
| 3 | Mike |
| | |
+---------+---------+
+---------+-----------------+
| Family Table |
+---------+-----------------+
| EmpId | Relationship |
+---------+-----------------+
| 1 | Father |
| 1 | Mother |
| 1 | Wife |
| 2 | Husband |
| 2 | Child |
+---------+-----------------+
+---------+---------+
| Loan Table |
+---------+--------+
| LoanId | EmpId |
+---------+--------+
| L1 | 1 |
| L2 | 1 |
| L3 | 2 |
| L4 | 2 |
| L5 | 3 |
+---------+--------+
I have tried Joins but it gives redundant rows.
Now the desired output will be
+---------+---------+--------------+---------+
| EmpId | Name | RelationShip | Loan |
+---------+---------+--------------+---------+
| 1 | John | Father | L1 |
| - | - | Mother | L2 |
| - | - | Wife | - |
| 2 | Lisa | Husband | L3 |
| - | - | Child | L4 |
| 3 | Mike | - | L5 |
| | | | |
+---------+---------+--------------+---------+
It looks like you are trying to assign the loans "sequentially" to rows in the family table. The approach to solve this is to first get the right rows, and then to get the loans assigned to rows.
The right rows (and first three columns) are:
select f.EmpId, e.Name, f.Relationship
from family f join
Employee e
on f.empid = e.empid;
Note that this does not put hyphens in the columns for repeated values, it puts in the actual values. Although you can arrange for the hyphens in SQL, it is a bad idea. SQL results are in the form of tables, which are unordered sets with values for each column and each row. When you start putting hyphens in, you are depending on the order.
Now the problem is joining in the loans. This is actually pretty easy, by using row_number()
to add a join
key:
select f.EmpId, e.Name, f.Relationship, l.LoanId
from Employee e left join
(select f.*, row_number() over (partition by f.EmpId order by (select NULL)) as seqnum
from family f
) f
on f.empid = e.empid left join
(select l.*, row_number() over (partition by l.EmpId order by (select NULL)) as seqnum
from Loan l
) l
on f.EmpId = l.EmpId and f.seqnum = l.seqnum;
Note that this does not guarantee the order of assignment of loans for a given employee. Your data does not seem to have enough information to handle a more consistent assignment.
The approach outlined below allows to easily "concatenate" more tables to the result set. It is not limited to two tables.
I'll use table variables to illustrate the solution. In real life these tables would be real tables, of course, not variables, but I'll stick with variables to make this sample script easy to run and try.
declare @TEmployee table (EmpId int, Name varchar(50));
declare @TFamily table (EmpId int, Relationship varchar(50));
declare @TLoan table (EmpId int, LoanId varchar(50));
insert into @TEmployee values (1, 'John');
insert into @TEmployee values (2, 'Lisa');
insert into @TEmployee values (3, 'Mike');
insert into @TFamily values (1, 'Father');
insert into @TFamily values (1, 'Mother');
insert into @TFamily values (1, 'Wife');
insert into @TFamily values (2, 'Husband');
insert into @TFamily values (2, 'Child');
insert into @TLoan values (1, 'L1');
insert into @TLoan values (1, 'L2');
insert into @TLoan values (2, 'L3');
insert into @TLoan values (2, 'L4');
insert into @TLoan values (3, 'L5');
We'll need a table of numbers.
SQL, Auxiliary table of numbers
http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
http://dataeducation.com/you-require-a-numbers-table/
Again, in real life you'll have a proper table of numbers, but for this example I'll use the following:
declare @TNumbers table (Number int);
insert into @TNumbers values (1);
insert into @TNumbers values (2);
insert into @TNumbers values (3);
insert into @TNumbers values (4);
insert into @TNumbers values (5);
The main idea behind my approach is to make a helper table that would contain correct number of rows for each EmpId
at first and then use this table to get results efficiently.
We'll start with counting number of relationships and loans for each EmpId
:
WITH
CTE_Rows
AS
(
SELECT Relationships.EmpId, COUNT(*) AS EmpRows
FROM @TFamily AS Relationships
GROUP BY Relationships.EmpId
UNION ALL
SELECT Loans.EmpId, COUNT(*) AS EmpRows
FROM @TLoan AS Loans
GROUP BY Loans.EmpId
)
Then we calculate the maximum number of rows for each EmpId
:
,CTE_MaxRows
AS
(
SELECT
CTE_Rows.empid
,MAX(CTE_Rows.EmpRows) AS MaxEmpRows
FROM CTE_Rows
GROUP BY CTE_Rows.empid
)
The CTE above has one row for each EmpId
: EmpId
itself and a maximum number of relationships or loans for this EmpId
. Now we need to expand this table and generate the given number of rows for each EmpId
. Here I'm using the Numbers
table for it:
,CTE_RowNumbers
AS
(
SELECT
CTE_MaxRows.empid
,Numbers.Number AS rn
FROM
CTE_MaxRows
CROSS JOIN @TNumbers AS Numbers
WHERE
Numbers.Number <= CTE_MaxRows.MaxEmpRows
)
Then we need to add row numbers to all tables with data, which we'll use for joining later. You can order the row numbers using other columns in your tables. For this example there is not much choice.
,CTE_Relationships
AS
(
SELECT
Relationships.EmpId
,ROW_NUMBER() OVER (PARTITION BY Relationships.EmpId ORDER BY Relationships.Relationship) AS rn
,Relationships.Relationship
FROM @TFamily AS Relationships
)
,CTE_Loans
AS
(
SELECT
Loans.EmpId
,ROW_NUMBER() OVER (PARTITION BY Loans.EmpId ORDER BY Loans.LoanId) AS rn
,Loans.LoanId
FROM @TLoan AS Loans
)
Now we are ready to join all this together. CTE_RowNumbers
has exact number of rows that we need, so simple LEFT JOIN
is enough:
,CTE_Data
AS
(
SELECT
CTE_RowNumbers.empid
,CTE_Relationships.Relationship
,CTE_Loans.LoanId
FROM
CTE_RowNumbers
LEFT JOIN CTE_Relationships ON CTE_Relationships.EmpId = CTE_RowNumbers.EmpId AND CTE_Relationships.rn = CTE_RowNumbers.rn
LEFT JOIN CTE_Loans ON CTE_Loans.EmpId = CTE_RowNumbers.EmpId AND CTE_Loans.rn = CTE_RowNumbers.rn
)
We are almost done. It is possible that the main Employee
table has some EmpIds
that don't have any related data, like EmpId = 3
in your sample data. To get these EmpIds
in the result set I'll left join the CTE_Data
to the main table and replace NULLs
with dashes:
SELECT
Employees.EmpId
,Employees.Name
,ISNULL(CTE_Data.Relationship, '-') AS Relationship
,ISNULL(CTE_Data.LoanId, '-') AS LoanId
FROM
@TEmployee AS Employees
LEFT JOIN CTE_Data ON CTE_Data.EmpId = Employees.EmpId
ORDER BY Employees.EmpId, Relationship, LoanId;
To get the full script just put all code blocks from this post together in the same order as they appear here.
This is the result set:
EmpId Name Relationship LoanId
1 John Father L1
1 John Mother L2
1 John Wife -
2 Lisa Child L3
2 Lisa Husband L4
3 Mike - L5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With