SQL query : creating table with distinct values on selected columns

Question

I'm quite new with SQL. I have a query to construct and fill a table that looks like this:

SELECT
   A.ID AS Identification,
   B.Name AS Family_name,
   B.Firstname AS first_name,
   B.Address AS adress_1
FROM  
    A 
LEFT JOIN 
    B ON B.ID = A.ID

UNION 

SELECT
   C.ID AS Identification,
   D.Name AS Family_name,
   D.Firstname AS first_name,
   D.Address AS adress_1
FROM 
    C 
LEFT JOIN 
    D ON D.ID = C.ID

UNION 

...

Is there a way to add a DISTINCT at the beginning so that when Identification, Family_name and First_name, but not ADRESS_1, are the same, only one line appears?

For example:

Identification	Family_Name	First_Name	address_1
Passport	Demir	Arçan	123 Main Street
Passport	Demir	Arçan	10 Downing St
Driver's license	Demir	Arçan	10 Downing St
Passport	Demir	Elif	123 Main Street

I saw some solution but with a second query, not in the same one.

Thank you for your help

Arcan

I tried with a

DISTINCT (ID, name, firstname) FROM (Myquery)

but it didn't work. I've also tried :

Select Distinct Identification, Family_Name, First_Name
Union
Select
A.ID as Identification,
...

which seems to work better, but I get an error saying that

"Identification" is not a valid column.

Answer 1

The way i would do it is:

WITH raw AS (
    SELECT
        A.ID as Identification,
        B.Name as Family_name,
        B.Firstname as first_name,
        B.Address as adress_1
    FROM A 
        LEFT JOIN B ON B.ID = A.ID

    UNION 

    SELECT
        C.ID as Identification,
        D.Name as Family_name,
        D.Firstname as first_name,
        D.Address as adress_1
    FROM C 
        LEFT JOIN D on D.ID = C.ID

    UNION 

    ...
)
SELECT
    Identification, Family_name, first_name, STRING_AGG(address_1, ', ') AS address_1
FROM raw
GROUP BY Identification, Family_name, first_name

Giving:

Identification	Family_Name	First_Name	address_1
Passport	Demir	Arçan	123 Main Street, 10 Downing St
Driver's license	Demir	Arçan	10 Downing St
Passport	Demir	Elif	123 Main Street

Or if you really only want 1 address:

WITH raw AS (
    SELECT
        A.ID as Identification,
        B.Name as Family_name,
        B.Firstname as first_name,
        B.Address as adress_1
    FROM A 
        LEFT JOIN B ON B.ID = A.ID

    UNION 

    SELECT
        C.ID as Identification,
        D.Name as Family_name,
        D.Firstname as first_name,
        D.Address as adress_1
    FROM C 
        LEFT JOIN D on D.ID = C.ID

    UNION 

    ...
), distinctFields AS (
    SELECT
        DISTINCT Identification, Family_name, first_name
    FROM raw
)
SELECT
    Identification, Family_name, first_name, 
    oneAddress.address_1
FROM distinctFields
    OUTER APPLY (
            SELECT TOP(1) address_1 
            FROM raw
            WHERE raw.Identification = distinctFields.Identification
            AND raw.Family_name = distinctFields.Family_name
            AND raw.first_name = distinctFields.first_name) oneAddress

Giving:

Identification	Family_Name	First_Name	address_1
Passport	Demir	Arçan	10 Downing St
Driver's license	Demir	Arçan	10 Downing St
Passport	Demir	Elif	123 Main Street