Stewart,

Actually, Andy didn't notice that the two WHERE clauses in the UNION query reference different columns for hoodfrom and hoodto and so the simple IN example isn't the proper answer. 

The two queries Stewart posted do two different things.  The first is a union of two queries and returns two sets of rows merged together.  The first set has those where route.hoodFROM = the neighborhood name and quadrant = NE, while the second set has those where the route.hoodTO = neighborhood.name and the quadrant = NW. 

The subquery version won't give you anything like what I think you want.  The outer query picks up those rows similar to the first query in the union example but then RESTRICTS those rows based on the subquery (the EXISTS clause).  However, the exists clause isn't "correlated" to the outer query and therefore, if there is even ONE row that matches the inner query, all of the rows in the outer query will be returned (since the EXISTS will ALWAYS be true) and if there are NO rows that matche the inner query, you won't get ANYTHING back from the outer query (black or white, all or nothing).  To be correlated, the inner query has to reference a value from the outer query, and since you use BOTH tables in both the inner query and outer query and don't use any aliases, there can be no correlation.

As for how to do EITHER of these queries with SQLObject, I don't know the answer and would also be interested in learning that.

Jon Rosen


Andy Todd wrote:
On 11/29/05, Stewart Midwinter <stewart.midwinter@gmail.com> wrote:
  
hi all:

I've got a mySQL database I'm using SQLobject to query. I'm doing ok with
simple queries - I'm a simple guy - but now I want to try something more
complex.  In particular I want to use UNION.  Is this possible with
SQLobject?   If not I could use a subquery, I guess.    I've searched the
archives and found only one message on the topic of UNION (from Oleg), but
the example there used cursor(), which I haven't used up to now.

here's my SQL query, which produces the desired results in command-line
mySQL:
mysql> select origin, dest, hoodfrom, hoodto, name, quadrant
    -> from route, neighbourhood
    -> where route.hoodfrom = neighbourhood.name
    -> and neighbourhood.quadrant = 'NE'
    -> union
    -> select origin, dest, hoodfrom, hoodto, name, quadrant
    -> from route, neighbourhood
    -> where route.hoodto = neighbourhood.name
    -> and neighbourhood.quadrant = 'NW';

And here's the same query using subquery:
select origin, dest, hoodfrom, hoodto, name, quadrant from route,
neighbourhood
where (route.hoodfrom = neighbourhood.name and neighbourhood.quadrant =
'NE')
and exists (select origin, dest, hoodfrom, hoodto, name, quadrant from
route, neighbourhood
 where route.hoodto = neighbourhood.name and neighbourhood.quadrant = 'NW')


all hints appreciated!

thanks,
--
Stewart Midwinter
 stewart@midwinter.ca
stewart.midwinter@gmail.com
Skype, GoogleTalk, iChatAV, MSN, Yahoo: midtoad
AIM:midtoad1


    

Whilst it's not an answer to your general question, in this case you
probably don't need a union. You can answer this question with
something like;

mysql> select origin, dest, hoodfrom, hoodto, name, quadrant
    -> from route, neighbourhood
    -> where route.hoodfrom = neighbourhood.name
    -> and neighbourhood.quadrant IN ('NE', 'NW')

Regards,
Andy
--
>From the desk of Andrew J Todd esq


-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems?  Stop!  Download the new AJAX search engine that makes
searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_idv37&alloc_id865&op=click
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

  


-- 
---------------------------------------------------------------
"The difference between theory and reality is that in theory,
 there is no difference between theory and reality,
 but in reality, there is." - Anonymous