Join related Issue

Question

New to SQL

Suppose we have two tables

One has got the ID and Name column :

+----+-------+
| ID |  Name |
+----+-------+
|  1 | Sam   |
|  1 | Dan   |
+----+-------+

and the second one has also got two columns as follow :

+----+------------+
| ID |  Relatives |
+----+------------+
|  1 | Uncle      |
|  2 | Aunty      |
+----+------------+

If we do inner join we would only get the rows where the condition satisfies. But i want the output to be Like

+------+------------+
|  ID  |  Relatives |
+------+------------+
| 1    | Uncle      |
| NULL | Aunty      |
+------+------------+

once only the value in the ID column should be shown. If the occurrence is twice or thrice it should come as null.

Just tell me if it is possible or not? and How for both the cases.

Answer 1

Try this:

SELECT
  T1.Id,
  T2.Relatives
FROM SecondTable T2
LEFT JOIN FirstTable T1
  ON T1.ID = T2.ID
GROUP BY T1.Id,
         T2.Relatives

This is what I get exactly:

CREATE TABLE #a (
  id int,
  name varchar(10)
)
CREATE TABLE #b (
  id int,
  name varchar(10)
)
INSERT INTO #a
  VALUES (1, 'sam')
INSERT INTO #a
  VALUES (1, 'Dan')
INSERT INTO #b
  VALUES (1, 'Uncle')
INSERT INTO #b
  VALUES (2, 'Aunty')

SELECT
  T1.Id,
  T2.name
FROM #b T2
LEFT JOIN #a T1
  ON T1.ID = T2.ID
GROUP BY T1.Id,
         T2.name

DROP TABLE #a
DROP TABLE #b

Output:

Id      name
NULL    Aunty
1       Uncle

Hope, this is what you ask in your question.

Answer 2

As your question is not clear, so assuming that you need to retrieve id from table a and name from table b and you also want to avoid duplicate rows, then an option could be to use distinct along with left join:

select distinct a.id, b.name
from b
left outer join a
on b.id = a.id
order by id desc

Result:

+------+-------+
|  id  | name  |
+------+-------+
| 1    | Uncle |
| NULL | Aunty |
+------+-------+

DEMO