Subquery dilemna

1

Kind of a weird little query I need to devise where as an order from my orders table needs to meet a few criteria in its own table while it has to check another table to meet a condition that doesnt have a certain value. I am wanting a list that contains unique customerids and sourceids that do have suspended or tagged in their status columns , do not have an empty or null in their ordertype, but in the corresponding sourceid in the Codes table , they cannot have a 100 code :
.:

TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
sourceid (varchar(12) NOT NULL),
Status varchar(50) NULL,
ordertype varchar(20) NULL,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);

TABLE dbo.codes --cod
(
codeID (pk uniqueidentifier NOT NULL),
sourceid (varchar(12) NOT NULL),
confirmed datetime NULL,
code (varchar(5) NULL),
CONSTRAINT PK_codeID PRIMARY KEY CLUSTERED (codeID ASC)
);

....as far as meeting all these conditions, i have become stumped on how to include these sourceids with their customerids that are in the same row that do not have a 100 code

select o.customerid,o.sourceid from orders as o where status in ('suspended','tagged') and o.ordertype <> '' and o.customerid in (select cod.orderid from codes as cod where code <> 100)
?
Thanks in advance

2

Try this:

select o.customerid
      ,o.sourceid
  from orders as o
 where status in ('suspended','tagged')
   and isnull(o.ordertype,'')<>''
   and not exists (select 1
                     from codes as c
                    where c.sourceid=o.sourceid
                      and c.code<>100
                  )
;