question

Katie 1 avatar image
Katie 1 asked

How to concatenate results from two queries

all, i have a table example create table group (id , host , participant) insert into group(id, host, participant) values (1,1000, 2000) insert inot group (id, host, participant) values (1,1000,2000) organisation 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.
sql-server-2008sqlquery
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
Is the creation of the group table correct? This would only give you 1 group ID, can you confirm this please.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
the example data and result sets don't quite match up - this makes it difficult to understand the relationships between the tables
0 Likes 0 ·
Katie 1 avatar image Katie 1 commented ·
I am soo sorry guys it was a typo. I meant it to be the following data in group insert into group(id, host, participant) values (1,1000, 2000) insert inot group (id, host, participant) values (2,1000,null) insert inot group (id, host, participant) values (3,null,3000)
0 Likes 0 ·

1 Answer

·
AdaTheDev avatar image
AdaTheDev answered
So if host and participant relate to custmerid in the customer (organisation?) table, then you just need to do 2 JOINs on there from the group table like below. The LEFT JOIN means if the host/participant is not in the customer table, it will still return the group and just return NULL as that particular field. SELECT g.id, c1.customername AS Host, c2.customername AS Participant FROM Group g LEFT JOIN Customer c1 ON g.host = c1.custmerid LEFT JOIN Customer c2 ON g.participant = c2.custmerid
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.