x

SQL Doubt

Hi All,

Can you please tell me how to find the primary key column value is already exists or not in Foreign key table in sql server2008 R2.
more ▼

asked Apr 23, 2012 at 10:00 AM in Default

Sakthivel gravatar image

Sakthivel
109 7 9 10

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I am not sure I understand your question, but I am guessing a little:

You have table A with PK column ID and table B with column AID and you want to know if there are rows in B where AID is not in A?

Select * From B where AID not in (select ID from A)
more ▼

answered Apr 23, 2012 at 10:48 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

Nice one. I spent too much time typing.
Apr 23, 2012 at 10:50 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

The easiest way to do this that I know of would be to do a JOIN operation. You can use OUTER JOIN and look for NULL values. Something like:

SELECT x.ID
FROM dbo.TableY AS y
LEFT JOIN dbo.TableX AS x
ON y.ID = x.ID
You can add a WHERE clause that either filters for the parent table, Y, ID value, or just looks for IS NULL or IS NOT NULL on the child table, X, ID value.
more ▼

answered Apr 23, 2012 at 10:49 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.5k 19 21 74

(comments are locked)
10|1200 characters needed characters left

A -- Primary Key table B -- Foreign Key table

Below query returns the values in primary table and which are not in foreign key table or child table.

Select * From A where ID not in (select ID from B)

If you want to check for a specified value then run the below by replace the question mark(?) with the value,

Select * From A where ID not in (select ID from B) and Id = ?
more ▼

answered Apr 23, 2012 at 05:08 PM

satya gravatar image

satya
361 18 18 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834

asked: Apr 23, 2012 at 10:00 AM

Seen: 457 times

Last Updated: Apr 23, 2012 at 05:08 PM