Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

getting data that exists in one table but not in the other

I have two tables

1. dept_staff_od
2. dept_staff_od_plus

first table has 2421 records
second table has 2362 records.


I want to get the rows not in second tbale but exist in the first. I used minus, not exists but not getting the right data.
0
anumoses
Asked:
anumoses
2 Solutions
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Please describe the tables for us and what you've tried already.

Assuming you have a key in both tables, let's call it staff_id for now, try this:

select * from dept_staff_od
where dept_staff_od.staff_id not in
(select distinct staff_id from dept_staff_od_plus);
0
 
Deepak ChauhanSQL Server DBACommented:
use this :

select * from dept_staff_od

where staff_id -- assuming key column
IN (select staff_id from dept_staff_od
except
select staff_id from dept_staff_od_plus)

Hope so you will got desired result as this is tested query.
Please let me know for any concern
0
 
ianmills2002Commented:
Like above, I will assume you have a key in both tables, let's call it staff_id.

select *
from dept_staff_od
     left outer join dept_staff_od_plus
        on dept_staff_od.staff_id = dept_staff_od_plus.staff_id
where dept_staff_od_plus.staff_id is NULL

Open in new window


All columns in the 2nd table after the LEFT OUTER JOIN will have a NULL value
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if both of your tables have the same number/data type of columns, then you can use the below...

select * from dept_staff_od
minus
select * from dept_staff_od_plus

or you can try below exists query...

select * from dept_staff_od a
where not exists ( select 1 from dept_staff_od_plus b
where a.key_column = b.key_column )
0
 
anumosesAuthor Commented:
@deepakChauhan

select * from dept_staff_od
where social_security_no -- assuming key column
IN (select social_security_no from dept_staff_od
except
select social_security_no from dept_staff_od_plus)

ORA-00907: missing right parenthesis
0
 
anumosesAuthor Commented:
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now