Concatenate the columns from two result sets

I have a table group

CREATE TABLE
GROUP (id , HOST , participant)

INSERT INTO GROUP(id, HOST, participant)
VALUES (1, 1000, 2000)

INSERT INTO G开发者_开发问答ROUP (id, HOST, participant)
VALUES (1, 1000, 2000)

customer table

insert into customer( custmerid, customername, alias) values (1000,'abccorp', 'abc')
insert into customer( custmerid, customername, alias) values (2000,'bcacorp', 'bca')
insert into customer( custmerid, customername, alias) values (3000,'lcacorp', 'lca')

Now I want to write a view to get

groupID, host participation
1 abccorp bcacorp
2 abccorp null
3 null lcacorp

How can I get this? I see that there are two joins needed to the organisation. But how can I get it ??

Thanks.

---

If I didn't misunderstood your question, following statement would return the results you require.

I am using following assumptions

• Host is mapped to custmerid
• Participant is mapped to custmerid
• The input data you provided is incomplete

Note that if custmerid isn't a spelling error in your question, I would advice you to spell it out completely as customerid

SQL Statement

SELECT  groupid = g.id
        , host = chost.customername
        , participation = cparticipant.customername     
FROM    [Group] g
        LEFT OUTER JOIN Customer chost ON chost.custmerid = g.Host
        LEFT OUTER JOIN Customer cparticipant ON cparticipant.custmerid = g.Participant

Test Data

;WITH [Group](ID, Host, Participant) AS (
    SELECT 1, 1000, 2000
    UNION ALL SELECT 2, 1000, NULL
    UNION ALL SELECT 3, NULL, 3000
)
, Customer (custmerid, customername, alias) AS (
    SELECT 1000, 'abccorp', 'abc'
    UNION ALL SELECT 2000, 'bcacorp', 'bca'
    UNION ALL SELECT 3000, 'lcacorp', 'lca'
)
SELECT  groupid = g.id
        , host = chost.customername
        , participation = cparticipant.customername     
FROM    [Group] g
        LEFT OUTER JOIN Customer chost ON chost.custmerid = g.Host
        LEFT OUTER JOIN Customer cparticipant ON cparticipant.custmerid = g.Participant

Output

groupid     host    participation
----------- ------- -------------
1           abccorp bcacorp
2           abccorp NULL
3           NULL    lcacorp