Retrieve columns from multiple tables

I believe you want to do something like this

DECLARE @customer TABLE 
(cust_id INT PRIMARY KEY,
email VARCHAR(50)
) 
DECLARE @customer_order TABLE (order_id INT PRIMARY KEY,cust_id INT)

-- TEMPORARY DATA INSERTION

INSERT @customer
        (
         [cust_id]
        ,[email]
        )
SELECT 1, 'a@mail.com'
UNION ALL
SELECT 2, 'b@mail.com'

INSERT @customer_order
        (
         [order_id]
        ,[cust_id]
        )
SELECT 1, 1
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3,1
UNION ALL
SELECT 4,2
UNION ALL
SELECT 5,2


-- FINAL OUTPUT
SELECT  [cust_id]
,       email
,       STUFF(( SELECT  ',' + CAST([order_id] AS VARCHAR(50))
                FROM    @customer_order AS CO
                WHERE   CO.[cust_id] = C.[cust_id]
              FOR
                XML PATH('')
              ), 1, 1, '')
FROM    @customer AS C