Thread: Multiple Selects

ok, i'm definitely showing how little i know here.
i need to select from two sets of records from a single table using an
inner join.
i can't find clear documentation on that process (a pointer would be
great).
essentially, i want to select records from the table with one WHERE
clause, then select a second set from the same table with a different
WHERE to compose a VIEW.
any help would be appreciated,
david slayton

Take a peek at UNION.  I basically appends on SQL SELECT to another=
 assuming the structure of the results are the same. Dam=
ond

David Slayton <dslayton@mhsoftware.com> writes:
> essentially, i want to select records from the table with one WHERE
> clause, then select a second set from the same table with a different
> WHERE to compose a VIEW.

I think what you are after here is something like

    SELECT *
    FROM mytable AS a, mytable AS b
    WHERE a.f1 = 33 AND b.f2 = 42 AND a.key = b.key ...

Basically, you select from the same table twice, assigning different
aliases to the two entries so that you can distinguish between them
in references from the rest of the query.  I used "a" and "b" here,
but you can use any names you like as table aliases.  The two FROM
entries are then joined just as if they were totally separate tables.

When you just write "SELECT FROM foo", you really get "FROM foo AS foo",
ie, the table's real name is also its alias for that query.

BTW, the "AS" is just a noise word and is often omitted.

            regards, tom lane