where clause question

On Fri, Sep 4, 2009 at 9:47 AM, Scott Frankel<leknarf@pacbell.net> wrote:

Hello,

Is it possible to perform selects in a where clause of a statement?

Given a statement as follows:

   SELECT foo.foo_id, foo.name
   FROM foo, bar
   WHERE foo.bar_id = bar.bar_id
   AND bar.name = 'martini';

I'm looking for a way to recast it so that the select and from clauses refer
to a single table and the join referencing the second table occurs in the
where clause.  For example, something like this:

   SELECT foo.foo_id, foo.name
   FROM foo
   WHERE (SELECT * FROM foo, bar WHERE ...)
   foo.bar_id = bar.bar_id
   AND bar.name = 'martini';

I've explored the "where exists" clause, but it's not supported by the
application toolkit I'm using.  AFAIK, I've only got access to where ...

where clauses is basically a set of boolean expressions. It's not
completely clear how to wrap that inside what you are trying to do.

you can do this:
WHERE something = (SELECT * FROM foo, bar WHERE ...)

or this:

WHERE (SELECT count(*) FROM foo, bar WHERE ...) > 0

for example. however, I'd advise dumping the application framework as
a long term objective. Another general tactic to try and express what
you are looking for in a view and query the view in a more regular
way. This is likely your best bet.

merlin

On 4 Sep 2009, at 15:47, Scott Frankel wrote:

Hello,

Is it possible to perform selects in a where clause of a statement?

Given a statement as follows:

SELECT foo.foo_id, foo.name
FROM foo, bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';

I've explored the "where exists" clause, but it's not supported by
the application toolkit I'm using. AFAIK, I've only got access to
where ...

Have you tried a view?

Is that some in-house toolkit you're using? If not, could you tell
what it is so that people can chime in with ways to use that toolkit
to get it do what you want or at least know what toolkit to avoid?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4aa1375011861997820494!

On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote:

Hello,

Is it possible to perform selects in a where clause of a statement?

Given a statement as follows:

SELECT foo.foo_id, foo.name
FROM foo, bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';

I'm looking for a way to recast it so that the select and from clauses
refer to a single table and the join referencing the second table occurs
in the where clause. For example, something like this:

SELECT foo.foo_id, foo.name
FROM foo
WHERE (SELECT * FROM foo, bar WHERE ...)
foo.bar_id = bar.bar_id
AND bar.name = 'martini';

I've explored the "where exists" clause, but it's not supported by the
application toolkit I'm using. AFAIK, I've only got access to where ...

Sounds like a great reason to modify, or if you can't modify, replace,
that application toolkit. This won't be the last time it will get in
your way.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

you'll need to create an alias beforehand

SELECT foo.foo_id, foo.name
FROM foo, (SELECT * FROM foo, bar WHERE ...) bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Date: Fri, 4 Sep 2009 10:21:24 -0700
From: david@fetter.org
To: leknarf@pacbell.net
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] where clause question

On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote:

Hello,

Is it possible to perform selects in a where clause of a statement?

Given a statement as follows:

SELECT foo.foo_id, foo.name
FROM foo, bar
WHERE foo.bar_id = bar.bar_id
AND bar.name = 'martini';

I'm looking for a way to recast it so that the select and from clauses
refer to a single table and the join referencing the second table occurs
in the where clause. For example, something like this:

SELECT foo.foo_id, foo.name
FROM foo
WHERE (SELECT * FROM foo, bar WHERE ...)
foo.bar_id = bar.bar_id
AND bar.name = 'martini';

I've explored the "where exists" clause, but it's not supported by the
application toolkit I'm using. AFAIK, I've only got access to where ...

Sounds like a great reason to modify, or if you can't modify, replace,
that application toolkit. This won't be the last time it will get in
your way.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Windows Live: Keep your friends up to date with what you do online.
http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_online:082009