How can I select the rows from a table which are not linked to another table in SQL Server?

I have two tables linked by 2 fields. Some rows from the first table are linked to second table, some are not. How can I select those which are not linked to the second table? I simulated the situation.

create table MainTbl(qno int, idno int, cat int);
create table SubTbl(newno int, idno1 int);

insert into maintbl values(1001, 88, 1);
insert into maintbl values(1002,88,1);

insert into subtbl values(11001, 88);
insert into subtbl values(11002,99);
insert into subtbl values(11003,88);

select * from maintbl,subtbl where (((cat*10000)+qno)=newno) an开发者_C百科d (idno=idno1);

select * from maintbl
left join subtbl on
(((cat*10000)+qno)<>newno) and (idno=idno1)

drop table maintbl;
drop table subtbl;

The first select statement is to select all the linked rows which I already succeeded.

The second statement is to select all the rows which are not linked.. which I m still trying to figure out.

The newno of subtbl is linked to (cat*10000)+qno of maintbl

The idno1 of subtbl is linked to idno of maintbl

You need to do a proper ANSI JOIN (not the table,table,table.... style JOIN - that's deprecated - stop using it!) and then you need to do a LEFT OUTER JOIN to join those rows that only exist in mainTbl and in those cases, the values from subTbl are NULL:

SELECT * 
FROM dbo.maintbl m
LEFT OUTER JOIN dbo.subtbl s ON (m.cat * 10000 + m.qno) = s.newno AND (m.idno = s.idno1)
WHERE s.newno IS NULL

This lists all those rows that exist in mainTbl but are not present in subTbl:

qno    idno  cat   newno   idno1
1002   88     1     NULL    NULL

You're close - you do the left join but then do a WHERE to find maintbl rows that don't have matching subtbl rows

select * from maintbl 
left join subtbl on (((cat*10000)+qno)=newno) and (idno=idno1) 
where newno IS NULL