Create records based on the other table 1

[naitx]

I have two Tables Split and GBS

Split
Country GBS Split
PH aaa 0%
PH bbb 10%
US fff 4%
US ttt 10%


GBS
Country Account GBS Split Total
PH 1-abc comp 0%
PH 2-abc aaa 1%
PH 3-are ttt 3%
PH 1-def comp 0%

is there a query that would look up at Split table and for every GBS with "comp",create a corresponding record for that Account?result should be something like this...

GBS
Country Account GBS Split Total
PH 1-abc aaa 0%
PH 1-abc bbb 10%
PH 1-def aaa 0%
PH 1-def bbb 10%
PH 2-abc aaa 1%
PH 3-are ttt 3%

Thanks in Advance!

 

[PHV]

SELECT G.Country, G.Account, S.GBS, S.Split, 0 AS Total
FROM GBS AS G INNER JOIN Split AS S ON G.Country = S.Country
WHERE G.GBS = 'comp'
UNION SELECT Country, Account, GBS, Split, Total
FROM GBS
WHERE GBS <> 'comp'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[naitx]

Thanks PHV!!

Works like a charm!!