[Zope-DB] SQL-subselect question

[Smith, Neil (Worcester)]

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.  

--
Neil


-----Original Message-----
From: Charlie Clark [mailto:charlie@begeistert.org]
Sent: 20 November 2002 16:17
To: zope-db@zope.org
Subject: [Zope-DB] SQL-subselect question


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

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
**********************************************************************
 
The information contained in this e-mail is confidential and intended only
for the use of the addressee. If the reader of this message is not the
addressee, you are hereby notified that you have received this e-mail in
error and you must not copy, disseminate, distribute, use or take any action
as a result of the information contained in it.
    
If you have received this e-mail in error, please notify
postmaster@npower.com (UK 01384 275454) and delete it immediately from your
system.
    
**********************************************************************
**********************************************************************
 
The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it.
    
If you have received this e-mail in error, please notify postmaster@npower.com (UK 01384 275454) and delete it immediately from your system.
    
Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.

Npower Limited
Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277

This e-mail may be sent on behalf of a member of the Innogy group of companies.

**********************************************************************