CREATE TABLE Games
(gam_GameID        INTEGER
,Name              NVARCHAR(3)
,gam_GameTypeID    INTEGER
,CONSTRAINT pk_Games PRIMARY KEY (gam_GameID)
)
 
INSERT INTO Games
(gam_GameID
,Name
,gam_GameTypeID
)
SELECT 1, 'ABC', 1
 
CREATE TABLE Mappings
(cgm_GameID      INTEGER
,cgm_RequestID   INTEGER
,CONSTRAINT pk_Mappings PRIMARY KEY (cgm_GameID, cgm_RequestID)
,CONSTRAINT fk_Mappings_Games_cgm_GameID_gam_GameID
            FOREIGN KEY (cgm_GameID)
            REFERENCES Games (gam_GameID)
)
go
 
INSERT INTO Mappings
(cgm_GameID
,cgm_RequestID
)
SELECT 1, 1 UNION
SELECT 1, 2
   
  SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY objG.gam_GameID ASC) as ID
        ,objG.gam_GameID
        ,(SELECT G02.gam_GameTypeID
            FROM Games AS G02
           WHERE G02.gam_GameID = objG.gam_GameID) as gam_GameTypeID
    FROM (SELECT G01.gam_GameID
            FROM Games AS G01
        GROUP BY G01.gam_GameID) AS objG
   WHERE EXISTS
         (SELECT *
            FROM Mappings AS objCGM
           WHERE objG.gam_GameID = objCGM.cgm_GameID
             AND objcgm.cgm_RequestID IN (1,2))

ID   gam_GameID   gam_GameTypeID
1    1            1

quote:

---

Originally posted by anujpratap84

Thanks chris_n_osborne.
It's work like charm.

Anuj Pratap Singh

---