CODEWITHSUNDEEP
asp.netsqlms-access
nick
nick

Reputation: 33

List un-used ID

I have a DropDownList that is populated with the primary key of Table A. Table B uses Table A primary key as a foreign key. The foreign key can only be used once.

So currently my list always populates with: 1, 2, 3, 4, 5 (exists as primary in Table A). Table B is already using keys: 1, 2, 3. When they keys are already existing in Table B, I need them not to populate in the dropdownlist.

I have tried to state in an SQL query, to only populate keys that don't exist in Table B. The query I'm trying, but doesn't work is:

SELECT Table_A.Grades_ID FROM Table_A INNER JOIN Table_B ON Table_A_ID = Table_B.Grades_ID WHERE Table_A.Grades_ID != Table_B.Grades_ID

How do I get my dropdownlist to not populate keys (data) that already exists in Table B?

Upvotes: 3

Views: 59

Answers (4)

Adnan Bhatti
Adnan Bhatti

Reputation: 3490

Select Table_A.Grades_ID from Table_A where Table_A.Grades_ID NOT IN (Select Grades_ID from Table_B)

Upvotes: 0

aquinas
aquinas

Reputation: 23796

SELECT Grades_ID FROM Grades WHERE Grades_ID NOT IN (SELECT Grades_ID FROM Table_B)

Upvotes: 0

No'am Newman
No'am Newman

Reputation: 6477

Try this:

select table_a.grades_id
from table_a left outer join
on table_a.grades_id = table_b.grades_id
where table_b.<some other field> is null

Upvotes: 0

Brian Webster
Brian Webster

Reputation: 30865

Just adjust your SQL to exclude the PKey values that already exist in TableB

SELECT Table_A.Grades_ID 
FROM 
  Grades 
  LEFT JOIN Table_B ON Table_A_ID = Table_B.Grades_ID 
WHERE Table_B.Grades_ID Is Null

Alternatively, you may wish to use "not in"

SELECT
  Tables_A.Grades_ID
WHERE 
  Tables_A.Grades_ID not in (Select Grades_ID from Table_B)

Upvotes: 5

Related Questions