Code help for missing type

[djj55]

Hello, SQL 2008R2
I have two tables with a one to many relationship. Table B will have several group types say Alpha, Beta, Gamma,... for each record in table A. I want to add groups that are not there but only one at a time as the group is used to control another process. Currently this is done with several queries. Is that the best way?
Example:
[tt]R1 Alpha
R1 Beta
R1 Gamma
R2 Alpha
R3 Beta[/tt]
So in this case we would want to add R1 Delta, R2 Beta, and R3 Alpha. There is criteria that goes along with each group type that I though could be used in a CASE statement. What stoped me was two of the criteria were the same, say for Beta and Delta.

Can someone point me in the direction I should be going.

Thank you,


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[starsky51]

Is this the kind of thing you're looking for? It fills in the gaps from the example data:

DECLARE @tabA TABLE (gname varchar(10))
DECLARE @tabB TABLE (rid char(2), gname varchar(10))

INSERT INTO @tabA
SELECT 'Alpha' AS gname
UNION SELECT 'Beta'
UNION SELECT 'Gamma'
UNION SELECT 'Delta'

INSERT INTO @tabB
SELECT 'R1' AS rid, 'Alpha' AS gname
UNION SELECT 'R1', 'Beta'
UNION SELECT 'R1', 'Gamma'
UNION SELECT 'R2', 'Alpha'
UNION SELECT 'R3', 'Beta'

SELECT b.rid, a.gname FROM @tabA a, @tabB b
EXCEPT
SELECT * FROM @tabB

 

[djj55]

Thank you for your reply. Your use of a lookup table is something I was thinking about.

The problem is I do not want all missing just the next one.

However, you have given me an avenue to look into.

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!