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
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
Microsoft SQL Server 2008
Last Comment
Shaun Kline
select * from rxs where not rxno in (select rxno from rxadmin)
Your query is syntactically correct. Are you missing rows in your results or do you have extra rows?
sorry, that should have been...
select * from rxs where not rono in (select rono from rxadmin)
select * from rxs where not rono in (select rono from rxadmin)
ASKER
my query is missing rows
I also tried
select * from rxs where not rono in (select rono from rxadmin)
still missing rows
I also tried
select * from rxs where not rono in (select rono from rxadmin)
still missing rows
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Try this:
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.
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
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.