pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Help
8+ messages / 6 participants
[nested] [flat]
* Query Help
@ 2000-12-27 16:20 Brian C. Doyle <bcdoyle@mindspring.com>
0 siblings, 3 replies; 8+ messages in thread
From: Brian C. Doyle @ 2000-12-27 16:20 UTC (permalink / raw)
To: pgsql-sql
What do I have to do a query where information in table1 is not in table2
I am looking for something like
Select table1.firstname where table1.firstname is not in table2.firstname
and table2.date='yesterday'
I tried
Select table1.firstname where table1.firstname != table2.firstname and
table2.date='yesterday'
and that did not work just listed everyone....
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Query Help
@ 2000-12-27 16:47 Francis Solomon <francis@stellison.co.uk>
parent: Brian C. Doyle <bcdoyle@mindspring.com>
2 siblings, 0 replies; 8+ messages in thread
From: Francis Solomon @ 2000-12-27 16:47 UTC (permalink / raw)
To: Brian C. Doyle <bcdoyle@mindspring.com>; pgsql-sql
Hi Brian,
Try something like this:
SELECT firstname FROM table1 WHERE firstname NOT IN (SELECT firstname
FROM table2 WHERE table2.date='yesterday'::date);
Hope this helps.
Francis Solomon
>
> What do I have to do a query where information in table1 is
> not in table2
>
> I am looking for something like
>
> Select table1.firstname where table1.firstname is not in
> table2.firstname
> and table2.date='yesterday'
>
> I tried
> Select table1.firstname where table1.firstname !=
> table2.firstname and
> table2.date='yesterday'
>
> and that did not work just listed everyone....
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query Help
@ 2000-12-27 17:01 Yury Don <yura@vpcit.ru>
parent: Brian C. Doyle <bcdoyle@mindspring.com>
2 siblings, 0 replies; 8+ messages in thread
From: Yury Don @ 2000-12-27 17:01 UTC (permalink / raw)
To: pgsql-sql
Hello Brian,
Wednesday, December 27, 2000, 9:20:53 PM, you wrote:
BCD> What do I have to do a query where information in table1 is not in table2
BCD> I am looking for something like
BCD> Select table1.firstname where table1.firstname is not in table2.firstname
BCD> and table2.date='yesterday'
BCD> I tried
BCD> Select table1.firstname where table1.firstname != table2.firstname and
BCD> table2.date='yesterday'
BCD> and that did not work just listed everyone....
If I understand correctly
Select table1.firstname from table1 where not exists
(select table2.firstname from table2 where table1.firstname = table2.firstname and
table2.date='yesterday')
--
Best regards,
Yury mailto:yura@vpcit.ru
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query Help
@ 2000-12-28 09:54 patrick.jacquot@anpe.fr
parent: Brian C. Doyle <bcdoyle@mindspring.com>
2 siblings, 0 replies; 8+ messages in thread
From: patrick.jacquot@anpe.fr @ 2000-12-28 09:54 UTC (permalink / raw)
To: pgsql-sql
"Brian C. Doyle" wrote:
> What do I have to do a query where information in table1 is not in table2
>
> I am looking for something like
>
> Select table1.firstname where table1.firstname is not in table2.firstname
> and table2.date='yesterday'
>
> I tried
> Select table1.firstname where table1.firstname != table2.firstname and
> table2.date='yesterday'
>
> and that did not work just listed everyone....
imho the most natrural way for what you need seems to be :
select whatyouwant from table1 where not exists
select * from table2 where table2.firstname = table1.firstname and
table2.date='yesterday';
hoping that helps
P. Jacquot
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query Help
@ 2000-12-28 12:19 Brian C. Doyle <bcdoyle@mindspring.com>
0 siblings, 0 replies; 8+ messages in thread
From: Brian C. Doyle @ 2000-12-28 12:19 UTC (permalink / raw)
To: pgsql-sql
Thank you to everyone with their suggestions.....
Where on the PostgreSQL site would I have found more info on the NOT EXISTS
At 11:20 AM 12/27/00 -0500, you wrote:
>What do I have to do a query where information in table1 is not in table2
>
>I am looking for something like
>
>Select table1.firstname where table1.firstname is not in table2.firstname
>and table2.date='yesterday'
>
>I tried
>Select table1.firstname where table1.firstname != table2.firstname and
>table2.date='yesterday'
>
>and that did not work just listed everyone....
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Query Help
@ 2003-10-23 18:33 yusuf0478@netscape.net
0 siblings, 1 reply; 8+ messages in thread
From: yusuf0478@netscape.net @ 2003-10-23 18:33 UTC (permalink / raw)
To: pgsql-sql
I'm interested in finding the minimim A.id such that the following holds:
select A.charge
, B.user_id
, C.employee_id
from A
inner join B using (user_id)
inner join C using (employee_id)
except
select X.charge
, Y.user_id
, Z.employee_id
from X
inner join Y using (user_id)
inner join Z using (employee_id)
--
I can't do the following, since the number of selected columns have to match:
select A.id
, A.charge
, B.user_id
, C.employee_id
from A
inner join B using (user_id)
inner join C using (employee_id)
except
select X.charge
, Y.user_id
, Z.employee_id
from X
inner join Y using (user_id)
inner join Z using (employee_id)
Can someone help me with the query?
Thanks in advance.
__________________________________________________________________
McAfee VirusScan Online from the Netscape Network.
Comprehensive protection for your entire computer. Get your free trial today!
http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397
Get AOL Instant Messenger 5.1 free of charge. Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query Help
@ 2003-10-23 18:34 scott.marlowe <scott.marlowe@ihs.com>
parent: yusuf0478@netscape.net
0 siblings, 0 replies; 8+ messages in thread
From: scott.marlowe @ 2003-10-23 18:34 UTC (permalink / raw)
To: yusuf0478@netscape.net; +Cc: pgsql-sql
On Thu, 23 Oct 2003 yusuf0478@netscape.net wrote:
> I'm interested in finding the minimim A.id such that the following holds:
>
> select A.charge
> , B.user_id
> , C.employee_id
> from A
> inner join B using (user_id)
> inner join C using (employee_id)
>
> except
>
> select X.charge
> , Y.user_id
> , Z.employee_id
> from X
> inner join Y using (user_id)
> inner join Z using (employee_id)
>
> --
>
> I can't do the following, since the number of selected columns have to match:
>
> select A.id
> , A.charge
> , B.user_id
> , C.employee_id
> from A
> inner join B using (user_id)
> inner join C using (employee_id)
>
> except
>
> select X.charge
> , Y.user_id
> , Z.employee_id
> from X
> inner join Y using (user_id)
> inner join Z using (employee_id)
Maybe you can add a dummy field in the second half like this:
except
select -1
, X.charge
, Y.user_id
, Z.employee_id
???
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Query Help
@ 2003-10-23 19:44 yusuf0478@netscape.net
0 siblings, 0 replies; 8+ messages in thread
From: yusuf0478@netscape.net @ 2003-10-23 19:44 UTC (permalink / raw)
To: pgsql-sql
Wouldn't that return every A.id ? since A.id would be compared to -1, and the wouldnt' be the same.
I want:
select min(A.id) such that A.charge, B.userid, C.employee_id in
(
select A.charge
, B.user_id
, C.employee_id
from A
inner join B using (user_id)
inner join C using (employee_id)
except
select X.charge
, Y.user_id
, Z.employee_id
from X
inner join Y using (user_id)
inner join Z using (employee_id)
)
"scott.marlowe" <scott.marlowe@ihs.com> wrote:
>On Thu, 23 Oct 2003 yusuf0478@netscape.net wrote:
>
>> I'm interested in finding the minimim A.id such that the following holds:
>>
>> select A.charge
>> , B.user_id
>> , C.employee_id
>> from A
>> inner join B using (user_id)
>> inner join C using (employee_id)
>>
>> except
>>
>> select X.charge
>> , Y.user_id
>> , Z.employee_id
>> from X
>> inner join Y using (user_id)
>> inner join Z using (employee_id)
>>
>> --
>>
>> I can't do the following, since the number of selected columns have to match:
>>
>> select A.id
>> , A.charge
>> , B.user_id
>> , C.employee_id
>> from A
>> inner join B using (user_id)
>> inner join C using (employee_id)
>>
>> except
>>
>> select X.charge
>> , Y.user_id
>> , Z.employee_id
>> from X
>> inner join Y using (user_id)
>> inner join Z using (employee_id)
>
>Maybe you can add a dummy field in the second half like this:
>
>except
>
> select -1
> , X.charge
> , Y.user_id
> , Z.employee_id
>
>???
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
__________________________________________________________________
McAfee VirusScan Online from the Netscape Network.
Comprehensive protection for your entire computer. Get your free trial today!
http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397
Get AOL Instant Messenger 5.1 free of charge. Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-10-23 19:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-27 16:20 Query Help Brian C. Doyle <bcdoyle@mindspring.com>
2000-12-27 16:47 ` Francis Solomon <francis@stellison.co.uk>
2000-12-27 17:01 ` Yury Don <yura@vpcit.ru>
2000-12-28 09:54 ` patrick.jacquot@anpe.fr
2000-12-28 12:19 Re: Query Help Brian C. Doyle <bcdoyle@mindspring.com>
2003-10-23 18:33 Query Help yusuf0478@netscape.net
2003-10-23 18:34 ` scott.marlowe <scott.marlowe@ihs.com>
2003-10-23 19:44 Re: Query Help yusuf0478@netscape.net
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