pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How can we match a condition among 2 diff. tables?
5+ messages / 5 participants
[nested] [flat]

* How can we match a condition among 2 diff. tables?
@ 2001-07-13 06:11  bhuvansql@yahoo.com
  0 siblings, 3 replies; 5+ messages in thread

From: bhuvansql@yahoo.com @ 2001-07-13 06:11 UTC (permalink / raw)
  To: pgsql-sql


 
 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!
 
 Thankx in advance.
 
 Regards,
 Bhuvaneswar.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How can we match a condition among 2 diff. tables?
@ 2001-07-13 15:43  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: bhuvansql@yahoo.com
  2 siblings, 1 reply; 5+ messages in thread

From: Stephan Szabo @ 2001-07-13 15:43 UTC (permalink / raw)
  To: bhuvansql@yahoo.com; +Cc: pgsql-sql


Something like?

select employee.* from employee where
not exists (select * from salesorder where
 salesorder.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!




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How can we match a condition among 2 diff. tables?
@ 2001-07-13 16:18  Peter Eisentraut <peter_e@gmx.net>
  parent: bhuvansql@yahoo.com
  2 siblings, 0 replies; 5+ messages in thread

From: Peter Eisentraut @ 2001-07-13 16:18 UTC (permalink / raw)
  To: bhuvansql@yahoo.com; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How can we match a condition among 2 diff. tables?
@ 2001-07-13 16:41  Andre Schnabel <A_Schnabel@t-online.de>
  parent: bhuvansql@yahoo.com
  2 siblings, 0 replies; 5+ messages in thread

From: A_Schnabel@t-online.de @ 2001-07-13 16:41 UTC (permalink / raw)
  To: pgsql-sql

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
);






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How can we match a condition among 2 diff. tables?
@ 2001-07-13 18:57  Joe Conway <joseph.conway@home.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Joe Conway @ 2001-07-13 18:57 UTC (permalink / raw)
  To: bhuvansql@yahoo.com; +Cc: pgsql-sql

> >  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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-07-13 18:57 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-13 06:11 How can we match a condition among 2 diff. tables? bhuvansql@yahoo.com
2001-07-13 15:43 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-13 18:57   ` Joe Conway <joseph.conway@home.com>
2001-07-13 16:18 ` Peter Eisentraut <peter_e@gmx.net>
2001-07-13 16:41 ` Andre Schnabel <A_Schnabel@t-online.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox