pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow 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
2001-07-13 15:43 ` Re: How can we match a condition among 2 diff. tables? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-13 16:18 ` Re: How can we match a condition among 2 diff. tables? Peter Eisentraut <peter_e@gmx.net>
2001-07-13 16:41 ` Re: How can we match a condition among 2 diff. tables? Andre Schnabel <A_Schnabel@t-online.de>
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 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 ` Re: How can we match a condition among 2 diff. tables? Joe Conway <joseph.conway@home.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 06:11 How can we match a condition among 2 diff. tables? bhuvansql@yahoo.com
2001-07-13 15:43 ` Re: How can we match a condition among 2 diff. tables? Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-07-13 18:57 ` Joe Conway <joseph.conway@home.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
* Re: How can we match a condition among 2 diff. tables?
2001-07-13 06:11 How can we match a condition among 2 diff. tables? bhuvansql@yahoo.com
@ 2001-07-13 16:18 ` Peter Eisentraut <peter_e@gmx.net>
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 06:11 How can we match a condition among 2 diff. tables? bhuvansql@yahoo.com
@ 2001-07-13 16:41 ` Andre Schnabel <A_Schnabel@t-online.de>
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
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