[Zope-DB] SQL-subselect question

[M.-A. Lemburg]

Charlie Clark wrote:

> Dear list,
>
> can anyone point me in the right direction?
> I've got a table which has entries a bit like
> p_key	f_key	ext_id1	ext_id2	ext_id3	ext_id4
> 1		1			1
> 2		1					1
> 3		1							2
> 4		1									1
> 5		1					2
> 6		2			1
> 7		2							1
> ....
>
> I need to be able to do something like
> SELECT * from mytable, anothertable
> WHERE
> anothertable.value = 'something'
> AND
> f_key = anothertable.f_key
> AND
> ext_id1 = 1
> AND
> ext_id2 = 1
>
> this currently doesn't work as there is only one ext_id per row

In what way does this not work ? Or put differently: what
would you like to achieve with the query ?

>
> I think subselects should work but I haven't been able to write them
> properly
>
> SELECT * from anothertable
> WHERE
> anothertable.value = 'something'
> AND EXISTS
> 	(SELECT * from mytable WHERE ext_id1 = 1 AND mytable.f_key =
> anothertable.f_key)
> AND EXISTS
> 	(SELECT * from mytable WHERE ext_id2 = 1 AND mytable.f_key =
> anothertable.f_key)
>
> this works but is incredibly slow (it's looping I think)
>
> Any help is much appreciated.
>
> Charlie
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db


-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/