Q:

Creating an SQL query to show clients and their contacts

I am using SQL Server 2008. I have two tables: 
Clients
CREATE TABLE Clients 
(
  Id int primary key, 
  Name varchar(20), 
  ParentId int
);

INSERT INTO Clients(Id, Name, ParentId) VALUES (1, 'A', 1);
INSERT INTO Clients(Id, Name, ParentId) VALUES (2, 'B', 2);
INSERT INTO Clients(Id, Name, ParentId) VALUES (3, 'AA', 1);
INSERT INTO Clients(Id, Name, ParentId) VALUES (4, 'BB', 2);
INSERT INTO Clients(Id, Name, ParentId) VALUES (5, 'C', 1);

This table contains all the clients' information, some of them have parent company/client and ParentId shows it. If it matches with the Id, it means that these clients are the top companies in the hierarchy.
Contacts
CREATE TABLE Contacts 
(
  Id int primary key, 
  Name varchar(20),
  Email varchar(20),
  ClientId int
);

INSERT INTO Contacts(Id, Name, Email, ClientId) VALUES (1, 'Bob', 'Bob@A.com', 1);
INSERT INTO Contacts(Id, Name, Email, ClientId) VALUES (2, 'John', 'John@A.com', 1);
INSERT INTO Contacts(Id, Name, Email, ClientId) VALUES (3, 'Charlie', 'Charlie@B.com', 2);
INSERT INTO Contacts(Id, Name, Email, ClientId) VALUES (4, 'Peter', 'Peter@AA.com', 3);
INSERT INTO Contacts(Id, Name, Email, ClientId) VALUES (5, 'Chris', 'Chris@AA.com', 3);
INSERT INTO Contacts(Id, Name, Email, ClientId) VALUES (6, 'Neil', 'Neil@BB.com', 4);
INSERT INTO Contacts(Id, Name, Email, ClientId) VALUES (7, 'Grant', 'Grant@C.com', 5);
INSERT INTO Contacts(Id, Name, Email, ClientId) VALUES (8, 'Cher', 'Cher@C.com', 5);

This table contains all the contacts for the specific client (ClientId shows it).
I need to have a list which contains clients and all the contacts of the specific client as well as all the contacts of his parent company.
For example
ClientName | ContactName  | ContactEmail
   AA      |              |
           |     Bob      |   Bob@A.com
           |     John     |   John@A.com
           |     Peter    |  Peter@AA.com
           |     Chris    |  Chris@AA.com
    C      |              |
           |     Grant    |  Grant@C.com
           |     Cher     |   Cher@C.com

I managed to write a SQL query which gives a result just for one client, but I want it to return more than one client. To show clients (3rd and 5th) and their contacts as above.
SELECT 
    C.Name, ' ', ' ' 
FROM 
    Clients C 
WHERE 
    C.Id = 3

UNION ALL

SELECT 
    ' ', Co.Name, Co.Email 
FROM
    Contacts Co
WHERE 
    ClientId = 3 OR (ClientId = (SELECT ParentId FROM Clients WHERE Id = 3)) 

A:

Without the formatting on SQL.
select
    Cli.Name,
    Co.Name,
    Co.Email
from Clients Cli
left join Contacts Co on
    Co.ClientId in(Cli.Id,Cli.ParentId)
order by
    1,2

With Formatting.. you really should do formatting outside SQL. But here is a way to do that
select
    CASE WHEN cls <> 1 THEN '' ELSE CLI_NAME END CLIENT_NAME,
    CO_NAME CONTACT_NAME,
    CO_EMAIL CONTACT_EMAIL
from (
    select
    ROW_NUMBER() OVER (
        PARTITION BY
            T.CLI_NAME
        ORDER BY    
            T.CO_NAME
    ) cls,
    CLI_NAME,
    CO_NAME,
    CO_EMAIL
    from (
        select
        Cli.Name CLI_NAME,
        '' CO_NAME,
        '' CO_EMAIL
        from Clients Cli
        WHERE 
            Cli.id in (3,5)
        UNION ALL
        select
            Cli.Name,
            Co.Name,
            Co.Email
        from Clients Cli
        left join Contacts Co on
            Co.ClientId in(Cli.Id,Cli.ParentId)
        WHERE 
            Cli.id in (3,5)
    ) T
) T
order by
    CLI_NAME,
    CO_NAME

