Question: 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      |
+---------+--------+

```

* Employee Table and Family Table has one to many relationship
* Employee Table and Loan Table has one many Relation ship

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      |
|         |         |              |         |
+---------+---------+--------------+---------+    

```

Answer: 
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.
Vladimir Baranov already wrote a good solution, but it is rather long (and has a small issue: you wanted Husband-L3 and Child-L4, but this solution returns Child-L3 and Husband-L4).

Gordon Linoff wrote a shorter solution, but it is not working correctly.

I could fix Gordon's solution, like this:

```
SELECT e.EmpId, e.Name, f.Relationship, l.LoanId
FROM @TEmployee e
LEFT JOIN (
    SELECT f.*, ROW_NUMBER() OVER (PARTITION BY f.EmpId ORDER BY (SELECT NULL)) AS seqnum
    FROM @TFamily 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 @TLoan l
) l ON l.EmpId = e.EmpId AND (f.seqnum = l.seqnum OR f.seqnum IS NULL)

```

However, I would rather say that the question is incorrect, because it asks us to match family members to specific loans arbitrarily (when no real relationship exists).

I would rather say that the correct question is the one with the following answer:

```
SELECT e.EmpId, e.Name,
    SUBSTRING((
        SELECT ', '+f.Relationship AS '*'
        FROM @TFamily f
        WHERE f.EmpId=e.EmpId
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(4000)'),3,4000) AS FamilyMembers,
    SUBSTRING((
        SELECT ', '+l.LoanId AS '*'
        FROM @TLoan l
        WHERE l.EmpId=e.EmpId
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(4000)'),3,4000) AS Loans
FROM @TEmployee e

```