----- Original Message ----- From: "electroteque" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 28, 2004 6:33 PM Subject: Sub queries
> Hi there, I have Mysql 4.1 on my development machine, I have been > trying to test out if I am going to be able to do this. What I would > like to do is return a one to many resultset but without the duplicated > results in the first query. Hows is this going to be possible ? I would > like to get all the records out of the second table from a key from the > first table. > > I got this using 4.1.5 > > mysql> select * from shotlist s limit 1 union select * from sources ss > where ss.sourceID IN (select sourceID from shotlist s limit 1); > ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & > IN/ALL/ANY/SOME subquery' > 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. 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. 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. > > I have also notice union joins add one resultset after the other, how > do you mix this in, or add that resultset as a column in the row > instead of one after the other ? > > Also I am trying to push for 4.1 to be installed on the servers i build > web apps on. When will be a possible date to say that gamma which is > practically production quality, to actually say production quality ? > heheh. Our systems guy will only trust it if it says that, god only > knows that latest versions are always feature rich and bug fixed darn. > I have no idea about this but others will probably have an idea when we can expect gamma code. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
