Posted on 2013-11-27
Last Modified: 2013-11-27
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
Question by:Star79
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 39680654
select * from rxs where not rxno in (select rxno from rxadmin)
LVL 26

Expert Comment

by:Shaun Kline
ID: 39680677
Your query is syntactically correct. Are you missing rows in your results or do you have extra rows?

Expert Comment

ID: 39680722
sorry, that should have been...

select * from rxs where not rono in (select rono from rxadmin)
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.


Author Comment

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

Accepted Solution

Jim Horn earned 500 total points
ID: 39680933
>find all the rono which are in Rxs table but not present in Rxadmin table.
SELECT r.rono, r.patid
FROM rxs r
-- The LEFT JOIN ensures that all rxs rows are in the return set
LEFT JOIN Rxadmin ra ON r.rono = ra.rono  
-- And then limits the return set to only those with no Rxadmin match
   WHERE ra.rono IS NULL

Open in new window

>where not rxno in
Not sure if it matters, but in the above T-SQL try where Rxs.rxno NOT IN
LVL 26

Expert Comment

by:Shaun Kline
ID: 39680934
Try this:
SELECT rono,patid
FROM   [rx].[dbo].[rxs] rxs
                             FROM   [rx].[dbo].[rxadmintimes]
                             WHERE  rxs.rono = rono
                             AND rxs.rxno = rxno) 

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.

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question