[Zope-DB] SQL-subselect question

[Michael Long]

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.

Cheers,
Mike
> 
> 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),
what 
> > 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
what the 
> > data is like as to what is best to index.  
> 
> Yes, I think that should work. It seems a bit strange to have to
rename the 
> same table over and over again to do it though which does suggest I
should 
> use separate tables in the first place.
> 
> Thanx
> 
> Charlie
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db
> 
>