Re: Boolean product of rows in multiple tables

I think you are looking for UNION.

SELECT id from table1 t1, table2 t2 where t1.string = 'string1' and
t2.string='string2' and t1.id=t2.id
UNION
SELECT id from table1 t1, table1 t2 where t1.string = 'string1' and
t2.string='string2' and t1.id=t2.id
UNION
SELECT id from table2 t1, table2 t2 where t1.string = 'string1' and
t2.string='string2' and t1.id=t2.id
;

Artur Rataj wrote:
>
> Hallo, I would like to ask you the following:
>
> There are tables table1 and table2. They both have
> identical columns (id, string). If the tables would be joined
> into a single table table3, the task would be
>
> select id from table3 t1, table3 t2 where t1.string='string1'
>         and t2.string='string2' and t1.id=t2.id;
>
> Thus, the task is to find all ids associated with both string1 and string2,
> in any of the tables table1 and table2. Because there are very few such
> combinations in the discussed case, the query could be fast. However, there
> is not table3 but two separate tables table1 and table2. Instead of table3
> t1, table3 t2 in the query, all combinations table<n> t1, table<n> t2 could
> be used in distinct questions whose results would be joined, but there would
> be four such distinct questions in case of the two tables table1 and table2
> and still more of them if there were more tables or more strings.
>
> I have tried to use various queries with the `or' operator, but then
> postgres used sequential scans and they were very slow.
>
> Is it possible to perform the described task fast in postgres, but without
> creating a new table? Perhaps a view could be used, but are indices used
> with views?
>
> Best regards,
> Artur Rataj
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html