How can I join these select statements into a single query?-CodePudding

I have the following select statements below that each have their own inner join to another table based on the same client_id. How can I combine these into a single query? The fact that each already relies on a inner join to a different table is what is giving me trouble.

DECLARE client_id INT
SET client_id = {placeholder}

SELECT 
    N.first_name,
    N.middle_name,
    N.last_name
FROM Name N WITH(NOLOCK)
LEFT OUTER JOIN NameLink C WITH(NOLOCK)
    ON N.name_id = C.name_id
WHERE C.client_id = @client_id

SELECT 
    A.street_name,
    A.house_num,
    A.city,
    A.state_id,
    A.zip,
    S.state
FROM Address A
INNER JOIN AddressLink C
    ON A.address_id = C.address_id
INNER JOIN State S
    ON A.state_id = S.state_id
WHERE C.client_id = @client_id

SELECT 
E.email 
FROM Email E
INNER JOIN EmailLink C
    ON E.email_id = C.email_id
WHERE C.client_id = @client_id

SELECT
    P.phone_num
FROM Phone P
INNER JOIN PhoneLink C
    ON P.phone_id = C.phone_id
WHERE C.client_id = @client_id

CodePudding user response：

When you need to JOIN the output for 2 queries you'd use

WITH
cte1 AS ( { query 1 } ),
cte2 AS ( { query 2 } )
SELECT {columns list}
FROM cte1
JOIN cte2 ON { joining condition }
{ WHERE, GROUP BY and so on }

CodePudding user response：

Use multiple CTE's, be sure to SELECT the client_id in each one then use the client_id for each CTE in a JOIN statement outside of the CTE:

DECLARE client_id INT
SET client_id = {placeholder}

WITH cteA AS (
SELECT 
    N.first_name,
    N.middle_name,
    N.last_name,
    C.client_id
FROM Name N WITH(NOLOCK)
LEFT OUTER JOIN NameLink C WITH(NOLOCK)
    ON N.name_id = C.name_id
WHERE C.client_id = @client_id
), cteB AS (
SELECT 
    A.street_name,
    A.house_num,
    A.city,
    A.state_id,
    A.zip,
    S.state,
    C.client_id
FROM Address A
INNER JOIN AddressLink C
    ON A.address_id = C.address_id
INNER JOIN State S
    ON A.state_id = S.state_id
WHERE C.client_id = @client_id
), cteC AS (
SELECT 
E.email,
C.client_id
FROM Email E
INNER JOIN EmailLink C
    ON E.email_id = C.email_id
WHERE C.client_id = @client_id
), cteD AS (
SELECT
    P.phone_num,
    C.client_id
FROM Phone P
INNER JOIN PhoneLink C
    ON P.phone_id = C.phone_id
WHERE C.client_id = @client_id
)
SELECT
    cteA.first_name,
    cteA.middle_name,
    cteA.last_name,
    cteB.street_name,
    cteB.house_num,
    cteB.city,
    cteB.state_id,
    cteB.zip,
    cteB.state,
    cteC.email,
    cteD.phone_num
FROM cteA
LEFT JOIN cteB ON cteA.client_id = cteB.client_id
LEFT JOIN cteC ON cteA.client_id = cteC.client_id
LEFT JOIN cteD ON cteA.client_id = cteD.client_id