asked on

SQL - NOT IN

Hello all,
I have the following tables:
Rxs has columns rxno,patid,rono,drug
Rxadmin has columns rxno,patid,rono,admintime.
How to write a query to find all the rono which are in Rxs table but not present in Rxadmin table.
I wrote the below sql

SELECT rono,patid
       
FROM   [rx].[dbo].[rxs] 
WHERE  rxs.rono NOT IN (SELECT rono
                             FROM   [rx].[dbo].[rxadmintimes]
                             WHERE  rono IS NOT NULL) 
                             order by rxs.RoNo

Open in new window

Somehow its not giving the right results

guswebb

select * from rxs where not rxno in (select rxno from rxadmin)

Shaun Kline

Your query is syntactically correct. Are you missing rows in your results or do you have extra rows?

guswebb

sorry, that should have been...

select * from rxs where not rono in (select rono from rxadmin)

Star79

ASKER

my query is missing rows
I also tried
select * from rxs where not rono in (select rono from rxadmin)
still missing rows

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shaun Kline

Try this:

SELECT rono,patid
FROM   [rx].[dbo].[rxs] rxs
WHERE  NOT EXISTS (SELECT 1
                             FROM   [rx].[dbo].[rxadmintimes]
                             WHERE  rxs.rono = rono
                             AND rxs.rxno = rxno) 
ORDER BY rxs.RoNo

Open in new window

This SQL assumes that RXNo is a linking field between the two tables along with rono.

The NOT EXISTS is considered more efficient that using a NOT IN query.