----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 9:26 PM Subject: Re: Sub queries
> > >> > > The keyword UNION should ensure that all duplicate rows are removed > > from the combined result set. If you use UNION ALL instead of UNION, > > the duplicates are left in the result set. > > > > Yeh right, funny, early versions of 4.0, UNION had in the docs this was > only avail in 4.1, i am so sure i read that, and i'm pretty sure I tried > union and didnt work and then I go back and it said in the docs it was > avail in 4.0 + hehe. > > > > I think the reason for the error message you are getting is that you > > have the 'limit 1' clause in the query twice, once in each select. You > > *may* be allowed to have the 'limit' clause in the subquery of the > > second SELECT although I doubt it, based on the text of the error > > message. More likely, you have to remove the 'limit' clause from the > > subquery. I think the only other place you can have it is after the > > last SELECT that is UNIONed together. Something like this: > > > > select * > > from shotlist s > > union > > select * > > from sources ss > > where ss.sourceID IN (select sourceID from shotlist s limit 1) > > limit 1; > > > > The final 'limit' clause affects the final result set, which is a > > combination of the result sets from both queries. > > > > Ok what I was more after was > > select * from shotlist s > union > select * from sources ss where ss.sourceID IN (s.sourceID) > > so it gets the results of sourceID from the first table, it doesnt seem to > like that. I want to prevent programatically having to do a second query > and loop within the script hehehe. > There are obviously many possible variations of your query; I just stated one that was pretty close to your original query. It's really not clear to me yet what you were trying to do so I just wanted to show you typical syntax. > > > > > > Unfortunately, I don't have a 4.1.x system to try this on so I am > > strictly guessing based on my work with DB2. > > Heh I currently have a DB2 jobby soon, doing jasper reports out of a system > that uses db2, whats the syntax like, is it a pain ? > It really depends on what you already know. I think DB2 is pretty easy to use but 've been using DB2 for 20 years; I don't know what you will think, because I don't know anything about you. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
