Same table multiple times

From: Jim Menard (jim..o.com)
Date: Wed Jan 28 2004 - 19:48:05 EST

  • Next message: Scott Ellsworth: "Profiles to help determine why uncommitted objects are so expensive"

    I'd like to build a query that contains the same table multiple times.
    For example:

            SELECT ...
            FROM table t1, table t2
            WHERE t1.fk_id = t2.fk.id
            AND t1.value = 'foo' and t1.type = 1
            AND t2.value = 'bar' and t2.type = 4
            ...

    Is this possible using expressions, or will I have to use raw SQL?

    Perhaps there is another way to do what I'm trying to do. I have a
    photo table and a metadata table, something like this:

            create table photo (
                    int id,
                    ...
            );
            create table metadata (
                    int photo_id,
                    varchar(255) value,
                    int type,
                    ...
            );

    I'm trying to implement a search where the user can specify values from
    multiple metadata types (like "name is Fred AND place is Paris"). The
    only way I can figure out how to do this in one query is like this:

            SELECT ...
            FROM photo t0, metadata t1, metadata t2
            WHERE t0.id = t1.photo_id
            AND t0.id = t2.photo_id
            AND (t1.value LIKE '%Fred%' AND t1.type = 1)
            AND (t2.value LIKE '%Paris%' AND t2.type = 4)

    Any suggestions as to how I can implement this without repeating the
    table in the query?

    (I can implement "name is Fred OR place is Paris" very easily. I don't
    need the table to appear multiple times in the query to implement
    that.)

    Thank you for your help.

    Jim 

    -- 
Jim Menard, jim..o.com, http://www.io.com/~jimm/
"I've memorized all the digits of pi. Just not the order they go in."
     -- Charles A. Lieberman, in rec.humor.oracle.d

    This archive was generated by hypermail 2.0.0 : Wed Jan 28 2004 - 19:48:12 EST