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.
Is the creation of the group table correct? This would only give you 1 group ID, can you confirm this please.
the example data and result sets don't quite match up - this makes it difficult to understand the relationships between the tables
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)
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
