Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've Table1 and Table2, Table1's PK is the FK of Table2. Now i need to perform a select from Table1 if and only if all the records in Table2 corresponding to a Table1 record has values (if any field value is null then skip).

ie, for an id in Table1 there have multiple sub ids in Table2. Out of those multiple ids if a particular field corresponds to any one id is null then select should be skipped.

Any one please help me out to write a single sql query for this requirement.

share|improve this question
I think I understand what you mean, but it would be much clearer if you provided example data and expected output. – Mat Nov 3 '12 at 12:27
And if a parent row (at Table1) is not related to any child row (at Table2), it should be shown, correct? – ypercubeᵀᴹ Nov 3 '12 at 12:51
up vote 5 down vote accepted

This is called an "anti-join" (or anti-semijoin). One way to write it in SQL is to use the NOT EXISTS construction:

FROM Table1 AS t1
      ( SELECT *
        FROM Table2 AS t2
        WHERE t2.FKcolumn = t1.PKcolumn
          AND t2.columnX IS NULL

If - as your comment - you want a parent row (at Table1) not related to any child row (at Table2), to NOT be shown, add this (or join Table2 if you also want columns from the second table shown):

      ( SELECT *
        FROM Table2 AS t2
        WHERE t2.FKcolumn = t1.PKcolumn
share|improve this answer
,Thanks...Also if a parent row (at Table1) is not related to any child row (at Table2), it should NOT be shown. The query is working for the other case. I used join along with your query to link both table1 and table2 and got the desired result. – Harun Nov 3 '12 at 13:40
select t1.* from table1 t1
where exists(select 1 from table2 t2 where t2.FKcolumn = t1.PKcolumn 
             having count(*)=count(t2.columnX) and count(*)>0)
share|improve this answer
-- create table temp1(did int primary key,name varchar(50))
-- insert into temp1 values(10,'IT')
-- insert into temp1 values(20,'cse')
-- insert into temp1 values(30,'eee')
-- insert into temp1 values(40,'mech')
-- insert into temp1 values(50,'Admin')
-- insert into temp1 values(60,'Sales')
-- insert into temp1 values(70,'Market')
-- insert into temp1 values(80,'Computers')
-- insert into temp1 values(90,'Business')
-- insert into temp1 values(100,'xyz')

-- create table temp2(id int identity primary key,did int foreign key references  temp1(did),name varchar(100))
-- insert into temp2 values(10,'a')
-- insert into temp2 values(10,'b')
-- insert into temp2 values(10,'c')
-- insert into temp2 values(10,'d')
-- insert into temp2 values(20,'e')
-- insert into temp2 values(20,'f')
-- insert into temp2 values(20,'g')
--insert into temp2 values(30,'i')
--insert into temp2 values(30,'j')
--insert into temp2 values(40,'d')
--insert into temp2 values(40,'d')
--insert into temp2 values(50,'x')
--insert into temp2 values(50,'x')
--insert into temp2 values(70,'y')
--insert into temp2 values(100,'z')

 select * from temp1
 where did in (select did from temp2)
share|improve this answer
The answer doesn't seem to answer the question. It may be answering a different question - but not this one. – ypercubeᵀᴹ Apr 1 at 11:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.