Search Records that DON'T Exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Search Records that DON'T Exist

Author

Topic

gwfran
Starting Member

9 Posts

Posted - 2004-08-05 : 15:25:09

Here's the problem. We have a database setup with a 4 level heirarchy in the following layout:

Compound
|
Lot Number
|
Junction--------Assay
|
Repetitions

What we need is a way to check and see if a particular Assay has not been run. Is there a way to build a query (or series of queries) that will give the Compound that does not have an Assay assigned to it? I'm going to go ahead and code it, but it would be nice if we could drive it by a query instead of running parsing code through the entire database to flag compounds that don't have an Assay.

Thanks!

G

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-05 : 15:31:37

? not much info given but something like this:


select 
  c.*
from 
  compound c
left outer join
  (select compoundID
   from lotnumber inner join junction on <...> inner join Assay on <...>) a
on
  c.compoundID = a.compoundID
where
  a.compoundID is null

- Jeff

gwfran
Starting Member

9 Posts

Posted - 2004-08-05 : 15:54:21

As an example, I have the following statement that will pull all of the compound numbers from lots that have had the assay "log P" run.

SELECT dbo.COMPOUNDS.CMPD_NUMBER, dbo.COMPOUND_LOT.LOT_NUMBER, dbo.COMPOUND_JUNCTION.ASSAY
FROM dbo.COMPOUND_JUNCTION INNER JOIN
dbo.COMPOUND_LOT ON dbo.COMPOUND_JUNCTION.LOT_INDEX = dbo.COMPOUND_LOT.LOT_INDEX INNER JOIN
dbo.COMPOUNDS ON dbo.COMPOUND_LOT.CMPD_NUMBER = dbo.COMPOUNDS.CMPD_NUMBER
WHERE (dbo.COMPOUND_JUNCTION.ASSAY = N'log P')

I'm trying to do a compare between a full list of compound numbers and the above query (Function1). What I want is an output of all the compound numbers that exist, but do not show up in Function1.

Thanks for the rapid reply!

G

gwfran
Starting Member

9 Posts

Posted - 2004-08-05 : 18:06:49

Okay - got it to work like a charm!

Using the Function1 I described and the method you described, I created the following query:

SELECT A.CMPD_NUMBER
FROM dbo.COMPOUNDS A LEFT OUTER JOIN
dbo.Function1() B ON A.CMPD_NUMBER = B.CMPD_NUMBER
WHERE (B.CMPD_NUMBER IS NULL)
ORDER BY A.CMPD_NUMBER

So now every compound number that has not had the specified assay (in this case Log P) run on it will be displayed. THANKS!

G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources