CODEWITHSUNDEEP
sqldatabasesql-server-2008t-sql
Viper
Viper

Reputation: 627

How to find records from table A, which are not present in table B?

I use a SQL Server 2008 database.

I have two tables with columns like these:

Table A:

request_id|order_id|e-mail
100       |2567    |[email protected]
100       |4784    |[email protected]
450       |2578    |[email protected]
450       |8432    |[email protected]
600       |9032    |[email protected]
600       |9033    |[email protected]

Table B has also id and order_no columns and many others columns:

Table B:

request_id|order_id|e-mail
100       |2563    |[email protected]
300       |4784    |[email protected]
600       |9032    |[email protected]
650       |2578    |[email protected]
850       |8432    |[email protected]

As you can see, a given request_id in table A can occur more than once (see 100 & 450 records)

I need to find all records from table A, which are not present in table B by order_id, but have equal request_id column values.

For above example I expect something like this:

Output:

request_id|order_id|e-mail
100       |2567    |[email protected]
100       |4784    |[email protected]
450       |2578    |[email protected]
450       |8432    |[email protected]
600       |9033    |[email protected]

As you can see above records from table A are not present in table B. This criteria is only satisfied with record where order_id=600

I created the sketch of T-SQL query:

select 
    D.request_id, D.order_id 
from 
    table A AS D 
where 
    D.request_id = 600 
    and D.order_id not in (select M.order_id 
                           from table B AS M 
                           where M.request_id = 600)

Unfortunately I don't have idea how to transform my query for all request_id. The first think is to use while loop over all request_id from table A, but it seems not smart in SQL world.

Thank you for any help

Upvotes: 1

Views: 18185

Answers (5)

Nicholas Carey
Nicholas Carey

Reputation: 74365

The Original Poster's problem statement was (and I quote):

I need to find all records from table A, which are not present in table B by order_id , but have equal request_id column values.

The thing about SQL is that is has strong mathematical underpinnings from

  • set theory, and
  • the predicate calculus

Thus, the O.P's problem statement can be simply restated in standard SQL, pretty much as-is:

select *                                    -- select all rows
from A a                                    -- from table A 
where exists (                              -- * that have at least one matching
        select *                            --   row in B with the same request ID
        from B b                            --
        where b.request_id = a.request_id   --
      )                                     --
  and not exists (                          -- * but have no matching row in B
        select *                            --   with the same
        from B b                            --   request AND order IDs
        where b.request_id = a.request_id   --
          and b.order_id   = a.order_id     --
      )                                     --

Easy!

Upvotes: -1

AgentSQL
AgentSQL

Reputation: 2940

Try this - 

SELECT a.*
FROM      table_a a
LEFT JOIN table_b b ON a.request_id = b.request_id
                   AND a.order_id   = b.order_id
WHERE b.request_id IS NULL

Check here - SQL Fiddle

Upvotes: 3

Jay
Jay

Reputation: 27492

I am not sure what you mean by "but have equal request_id column values". Equal to what?

If you simply want all the records in table_a that do not have a record in table_b with matching request_id and order_id, then:

select a.request_id, a.order_id
from table_a a
where not exists (select * from table_b b where b.request_id=a.request_id 
  and b.order_id=a.order_id)

Upvotes: 0

Guntram Blohm
Guntram Blohm

Reputation: 9819

select request_id, order_id from table_a
except
select request_id, order_id from table_b

EDIT: this does not work in MS SQL:

If you want the email addresses as well:

select request_id, order_id, email from table_a
where (request_id, order_id) not in (
    select request_id, order_id from table_b
)

Upvotes: 2

Vikas Hardia
Vikas Hardia

Reputation: 2695

 SELECT  request_id, order_id,e-mail
  FROM  table_a
  EXCEPT 
  SELECT  request_id, order_id,e-mail
  FROM  table_b

Upvotes: 0

Related Questions