Skip site navigation (1) Skip section navigation (2)

Re: Boolean product of rows in multiple tables

From: Jean-Luc Lachance <jllachan(at)nsd(dot)ca>
To: Artur Rataj <arataj(at)iitis(dot)gliwice(dot)pl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Boolean product of rows in multiple tables
Date: 2002-12-30 20:29:47
Message-ID: 3E10ACBB.DF9750E3@nsd.ca (view raw or flat)
Thread:
Lists: pgsql-general
Artur,

You are right about the missing union.

Still, if most tables are alike (at least they have id and string) you
should think about
inheritance. Create the base table/class with id and string and have all
the other tables inherit from it.

Them you can do:

SELECT id from base_table t1, base_table t2 where t1.string = 'string1'
and
t2.string='string2' and t1.id=t2.id;

JLL


Artur Rataj wrote:
> 
> On Mon, 30 Dec 2002, Jean-Luc Lachance wrote:
> 
> > I think you are looking for UNION.
> >
> 
> If there would be one component more:
> 
> > 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
> 
> UNION
> SELECT id from table2 t1, table1 t2 where t1.string = 'string1' and
> t2.string='string2' and t1.id=t2.id
> 
> > ;
> >
> 
> it would work in the discussed case. The problem is that there can be
> several such tables and there can also be several strings, what could give a
> relatively large number of such components, and in effect it could be slow.
> A table being a sum of the several searched tables could be used with a
> single fast `select', but because an arbitrary subset of the available
> tables could possibly be searched, a relatively large number of tables being
> sums of the tables in the arbitrary subsets would then be needed.
> 
> Best regards,
> Artur Rataj

In response to

Responses

pgsql-general by date

Next:From: Medi MontaseriDate: 2002-12-30 20:31:45
Subject: Re: pg and transactions
Previous:From: Doug McNaughtDate: 2002-12-30 20:17:42
Subject: Re: lock table question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group