CODEWITHSUNDEEP
selectjoin
maxweber
maxweber

Reputation: 576

how to select from one table where no matching row in another (but has multiple)

I have tried this:

SELECT *
FROM   svc00100
WHERE  NOT EXISTS (SELECT *
                   FROM   svc00101
                   WHERE  TECHSTAT = 'INA'
                          AND svc00100.TECHID = svc00101.TECHID)

I want to select rows from svc00100 but not if there is a row in svc00101 with the same TECHID and with TECHSTAT = 'INA'. But, there are multiple rows in svc00101 with the TECHID matching, some having 'INA' and some having other stuff. I want to eliminate/ignore any TECHID where there is any row in svc00101 with TECHID and 'INA' for techstat. Using SQL server BTW if that helps.

Upvotes: 0

Views: 33

Answers (1)

steryd
steryd

Reputation: 481

You can use left outer join and Where clause. Like this:

select svc00100.* from svc00100 
left outer join svc00101 on TECHSTAT = "INA" 
                            and svc00100.TECHID = svc00101.TECHID 
where svc00101.KEY is null

Instead of KEY you should pass name of NOT NULL column. For example Primary Key.

Upvotes: 1

Related Questions