PDA

View Full Version : get list of items which are there in result of 1 query and not there in another query



aravindhan_3
07-07-2010, 01:34 AM
Hi,
Can you please help for my problem,
I want the list of Items which are there in the result of one query and not there in another query.

eg:
Query 1Select emp_Id, Emp_Name
From employee
Where entry_date = 17-Jun-2010

this query results
emp_Id ----------Emp_Name
1 ---------------- John
2 ---------------- Jim
3 ---------------- Richard

now I have another query
Query 2
Select emp_Id, Emp_Name
From employee
Where entry_date = 18-Jun-2010

this query results
emp_Id ----------Emp_Name
9 ---------------- Andy
10 ---------------- Clara
3 ---------------- Richard
4 ---------------- Peter

now i want to the list of Emp_Id & Emp_Name which were there in query 1
and not there in Query 2. in this case John & Jim which are missing in query 2.

If the numbers or less than a million i could have done in this excel, the actual results are more than 2 million.

Can some SQL experts help me to build this in a SQL query it self?

Thanks,
Arvind

Gollem
07-08-2010, 10:38 PM
Hope this is what you seek:

Select emp_Id, Emp_Name
From employee
Where entry_date = 17-Jun-2010 and Emp_Id not in (

Select emp_Id
From employee
Where entry_date = 18-Jun-2010 )

stanl
07-17-2010, 03:48 AM
just out of curiosity - does your database have one Richard with 2 entry dates, or 2 Richards. If the latter, I would consider creating a query with addtional columns and either include the DISTINCT or DISTINCTROW keyword and avoid am intensive sub-select (especially given the size of the DB).