Thread: How can we match a condition among 2 diff. tables?

Hi,Consider the below... table 'employee' with unique 'emp_id', table 'salesorder' with 'emp_id' and unique 'sales_id'
Remember,both tables include some more different fields.Now, i need to get the details of all employees who did receive
NONEof the salesorders. ie.. i wish to select the records of table 'employee' whose 'emp_id' are not there in table
'salesorder'.Ineed to accompolish in a single query!Thankx in advance.Regards,Bhuvaneswar.
 

bhuvansql@yahoo.com writes:

>  Now, i need to get the details of all employees who did
>  receive NONE of the salesorders. ie.. i wish to select the
>  records of table 'employee' whose 'emp_id' are not
>  there in table 'salesorder'.

SELECT * FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM salesorder);

or, slightly uglier but possibly faster

SELECT * FROM employee WHERE NOT EXISTS   (SELECT 1 FROM salesorder WHERE salesorder.emp_id = employee.emp_id);

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Hi,

if you translate your (english) idea striktly to SQL, you'll make it ;-)
>  Now, i need to get the details of all employees who did 
>  receive NONE of the salesorders. ie.. i wish to select the 
>  records of table 'employee' whose 'emp_id' are not 
>  there in table 'salesorder'.

SELECT * FROM employee
WHERE emp_id NOT IN (   SELECT emp_id FROM salesorder
);

Something like?

select employee.* from employee where
not exists (select * from salesorder wheresalesorder.emp_id=employee.emp_id);

On Fri, 13 Jul 2001 bhuvansql@yahoo.com wrote:

> 
>  
>  Hi,
>  
>  Consider the below...
>  
>   table 'employee' with unique 'emp_id',
>   table 'salesorder' with 'emp_id' and unique 'sales_id' 
>  
>  Remember, both tables include some more different fields.
>  
>  Now, i need to get the details of all employees who did 
>  receive NONE of the salesorders. ie.. i wish to select the 
>  records of table 'employee' whose 'emp_id' are not 
>  there in table 'salesorder'.
>  
>  I need to accompolish in a single query!

> >  Now, i need to get the details of all employees who did 
> >  receive NONE of the salesorders. ie.. i wish to select the 
> >  records of table 'employee' whose 'emp_id' are not 
> >  there in table 'salesorder'.
> >  
> >  I need to accompolish in a single query!

This should work:

select e.emp_id
from employee as e left join salesorder as s on e.emp_id = s.emp_id
where s.emp_id is null;


-- Joe