[Zope-DB] SQL-subselect question
Thu, 21 Nov 2002 10:40:55 -0500
Depending on you database, you could you the following SQL92 syntax as well.
select a.col1, a.col2, b.col3, c.col4
from mytable1 a join mytable2 b
on a.col1 = b.col1
and b.col3 = 'somevalue'
join anothertable c
on a.col2 = c.col4
In the above query "a", "b" and "c" are aliases for each respective
table and is used to save typing and enhance readablity. The alia can be
anything you would like. I tend to use the first letter of the table
name so that the alias name has some intelligence built into it.
> On 2002-11-21 at 10:04:55 [+0100], Smith, Neil (Worcester) wrote:
> > If I've got it right about what you want (and I'm not sure I have),
> > about
> > SELECT *
> > FROM
> > mytable mytable1,
> > mytable mytable2,
> > anothertable
> > WHERE
> > anothertable.value = 'something' AND
> > mytable1.f_key = anothertable.f_key AND
> > mytable2.f_key = anothertable.f_key AND
> > mytable1.ext_id1 = 1 AND
> > mytable2.ext_id2 = 1
> > If it performs poorly, you might need some indexes. Indexing f_key on
> > both tables would be a start, after that it depends very much on
> > data is like as to what is best to index.
> Yes, I think that should work. It seems a bit strange to have to
> same table over and over again to do it though which does suggest I
> use separate tables in the first place.
> Zope-DB mailing list