1

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

Improve this question

5 Answers 5

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you. Currently I can't test it in real example, but it seems that it works! One question: Do I change last WHERE condition to "WHERE b.order_id IS NULL"? I see that it also works and it seems more comfortable for me, because I'd like to find all non-matching request_id :)
Since, both columns (request_id and order_id) are used in JOIN clause, you don't need it. It's the same thing.
Maybe I'm mis-understanding the requirement, but I thought the OP was saying that he wants all cases where there is a record in table B that matches table A on request_id but NOT on order_id. The above query will list records from table A which have no match in table B on either request_id or order_id.
This doesn't match the OPs problem statement: all rows in A with a match in B on request id, but no match on BOTH request and order id.
@NicholasCarey That's what I thought he meant at first too. But then look at his expected output. I think he needs to clarify what he means by "have equal request_id column values". Equal to what?
2 
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 )
Improve this answer

5 Comments

In MS SQL, you need to use EXCEPT instead of minus. The syntax is otherwise identical. The second method I don't believe works in T-SQL. You could also do table_a LEFT JOIN table_b on primary keys, followed by WHERE table_b.<primary_key> IS NULL.
@BaconBits I don't understand the clue of your second solution. What do you mean by primary_key in this case? Is it request_id? IMHO in LEFT OUTER JOIN I only can match tables with one condition, so how I can match by request_id and order_id? Thanks
@Viper AgentSQL's answer is what I was thinking of.
Thank you for pointing out the EXCEPT. Learn something new every day ;)
@BaconBits Thank you for your help. Now I understand your approach I think
0 
 SELECT request_id, order_id,e-mail FROM table_a EXCEPT SELECT request_id, order_id,e-mail FROM table_b
Improve this answer

Comments

0

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)
Improve this answer

2 Comments

This fails the O.P.s problem statement as well.
As I said at the beginning of my post here, I think the OP needs to clarify his problem statement. I believe the above query will give what the OP lists as his expected output given the stated inputs.
-1

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!

Improve this answer

1 Comment

Do return values match with expected output? I don't think so. Look at his OUTPUT.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.