CODEWITHSUNDEEP
sqlsql-servert-sql
SkyeBoniwell
SkyeBoniwell

Reputation: 7102

Query for a group of IDs

I use the simple query below to output a list of partIDs based on a modelID that we get from a printout on a sheet of paper.

We've always just used one modelId at a time like this:

   SELECT gm.partId, 343432346 as modelId
   FROM dbo.systemMemberTable sm
   WHERE gm.partID NOT IN (SELECT partID FROM systemsPartTable)

Is there a way to create a query that uses 10 modelIds at a time?

I can't think of a way because the modelIds are not in any table, just a printout that is handed to us.

Thanks!

Upvotes: 0

Views: 67

Answers (4)

paparazzo
paparazzo

Reputation: 45096

SELECT gm.partId, T.number as modelId
FROM ( values (4),(9),(16),(25) 
     ) as T(number) 
CROSS JOIN dbo.systemMemberTable sm
WHERE gm.partID NOT IN (SELECT partID FROM systemsPartTable)

op said getting an error but this is the test that runs for me 

SELECT T.number as [modelID] 
      ,[main].[name]     
  FROM ( values (4),(9),(16),(25) 
       ) as T(number)  
  cross join [Gabe2a_ENRONb].[dbo].[docFieldDef] as [main] 
  where [main].[ID] not in (1)

Upvotes: 3

radar
radar

Reputation: 13425

insert model ids into a table variable and then do the join with this table variable

Also use not exists instead of not in as not in doesn't work if there are null values in the parts table.

declare @modelIds table
(
model_id int
)

insert into @modelIds values (343432346) , (123456)

your select would be

As you want same model id repeated for all parts, you can just use cross join

   select gm.partId, m.model_id
   from dbo.systemMeberTable sm
   cross join @modelIds m 
   where not exists ( select 1 from systemPartsTable SPT where  SPT.partId = gm.PartID )

Upvotes: 3

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Create table #tempmodelTable
(
  @modelid int
)

insert all modelid here, then use join with your select query

   INSERT INTO #tempmodelTable values(123123)
   INSERT INTO #tempmodelTable values(1232323)
   INSERT INTO #tempmodelTable values(1232343123)

   SELECT gm.partId, modelId
   FROM dbo.systemMemberTable gm inner join #tempmodelTable 
   WHERE gm.partID NOT IN (SELECT partID FROM systemsPartTable)

Upvotes: 2

dario
dario

Reputation: 5269

Try this:

DECLARE @T TABLE (ModelId INT);
INSERT INTO @T (ModelID)
VALUES (343432346), (343432347) -- And so on and so forth

SELECT gm.partId, T.ModelId
FROM dbo.systemMemberTable sm
INNER JOIN @T AS T
    ON T.ModelId = SM.ModelID
WHERE gm.partID NOT IN (SELECT partID FROM systemsPartTable)

Upvotes: 2

Related Questions