how to NOT have repeating rows in SQL?

Question

My schemea is a customer table that joins to the table with the address. Some of the address have two: a billing and a shipping.

How can I get one customer per line with either a billing or shipping.

I feel like should use a DISTINCT but if I do, it will delete my customers that have a billing and shipping address attached.

enter image description here

enter image description here

SELECT
    Firstname,
    LastName,
    Line1,
    City,
    State,
    ZipCode,
FROM
    Customers as c
    INNER JOIN Addresses as a ON c.CustomerID = a.CustomerID
ORDER BY
    c.CustomerID

Answer 1

With row_number()over() and common table expression you can select first row from table Addresses for each customer. Then join that table with customer table to have single row per customer.

with addess as 
(
  select *,row_number()over(partition by customerid order by addressid)rn from Addresses 
)
SELECT
    Firstname,
    LastName,
    Line1,
    City,
    State,
    ZipCode,
FROM
    Customers as c
    INNER JOIN Address as a ON c.CustomerID = a.CustomerID and rn=1
ORDER BY
    c.CustomerID

A slower solution with subquery:

SELECT
    Firstname,
    LastName,
    Line1,
    City,
    State,
    ZipCode,
FROM
    Customers as c
    INNER JOIN Address as a ON c.CustomerID = a.CustomerID and 
    a.AddressID=(select min(addressid) from Addresses where CustomerID=c.CustomerID)
ORDER BY
    c.CustomerID