Need T-SQL Code Help: Select based on 2nd Table count

[RichardMcCarty]

I'm new to T-SQL and trying to program a simple query:

Table1
Location ID
Flag

Table2 (many to Table1)
Location ID
Code

Select Table1 Location_IDs where the count of Table2's Code=value is 0

Thanks for your help!

[sknake]

There are two approaches, I recommend the first:

Select *
From Table1 
Where NOT EXISTS
(
  Select *
  From Table2
  Where Table1.ID = Table2.ID
)

The second, doing exactly as you described, is:

Select *
From Table1
Where
IsNull((
  Select Count(*)
  From Table2
  Where Table1.ID=Table2.ID
), 0) = 0

[RichardMcCarty]

Thank you for the assistance!

One more piece of my newbie-mystery to solve... now I need to conditionally insert a record in Table2:

Table1
Location ID
Flag

Table2 (many to Table1)
Location ID
Code

Select Table1 Location_IDs with Flag set
If count of related Table2 rows with Code=value is 0,
then insert a row in Table2
Rinse and repeat

[sknake]

Please mark this thread as solved since your original question was answered.

I'm not sure I understand you entirely but I think this is what you want:

Insert Into Table2 ([Location ID], Code)
Select [Location Id], 'ABC' As Code
From Table1
Where Table1.Flag = 1 and NOT EXISTS
(
  Select *
  From Table2 x
  Where x.[Location Id] = Table1.[Location ID]
)

Your description implies both Table1 and Table2 have a "Code" column, but your table layout only has the "Code" column in one table.