How to display record set where value does not exist 1

[dadazs]

hello,
I am new at sql & appologies if it is a too basic question; however, i am still working on it:

select * from CONTACT1
inner join CONTSUPP
on CONTACT1.ACCOUNTNO= CONTSUPP.ACCOUNTNO

where CONTACT1.KEY1='Client' and

(CONTSUPP.RECTYPE = "P" )=False



I want to display records where CONTSUPP.RECTYPE = "P" does not exist.

I cannot use CONTSUPP.RECTYPE <> "P" because it displays other rectype values.
I am interested in records that do not have "p" value only & all other rectype letter should be ignored.

Many Thanks

 

[SimonSellick]

You can't do an inner (equi) join to a row that does not exist, so you need to use NOT EXISTS:

SELECT
  *
FROM Contact1 c1
WHERE Key1 = 'Client'
AND NOT EXISTS (
  SELECT 1
  FROM ContSupp
  WHERE AccountNo = c1.AccountNo
    AND RecType = 'P'
)

You could use a LEFT (outer) join and test for a null value in a not-null column of ContSupp, but NOT EXISTS probably gives a clearer idea of what you are looking for.

HTH
Simon

 

[dadazs]

Great Stuff! Thank you!!!