Hi Barry--

First, I want to see if I understand what you mean, then I'll take a shot
at some SQL to solve the problem.

I take it you have two tables, "a" and "b".  They may look something like

Table: a
id      name
---     ------
001     Bob
002     Ted
003     Mary
004     Sandy

Table: b

And you want your result table to look something like:

id      name            selected
---     ------  --------
001     Bob             0
002     Ted             1
003     Mary            1
004     Sandy           0

What you're trying to do is not very set-oriented (SQL is very
set-oriented), so how about gathering the two sets you're talking about and
returning the union of the two?

Something like:

select a.id as id, name, 1 as selected from a, b where a.id = b.id union
select a.id as id, name, 0 as selected from a where a.id NOT IN (select id
from b);

There are undoubtedly other ways to do this, but the above query works on
an old PostgreSQL installation I mess around with sometimes.

Hope this helps.


At 02:30 PM 8/16/01 -0700, Barry Prentiss wrote:
> Here's another SQL query problem:
>How do I construct a query that returns 1 if a value is present and zero if
>it is not, for a list of values returned in another query?
>i.e. select a.id, a.name, c.selected from table-with-id-and-name a,
>    (select 1 as selected from another-table b if a.id = b.id
>     or select 0 as selected from another-table b if a.id != b.id ) c;
> Thx in Advance,
> Barry Prentiss

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to