Hmm.

Firstly, I don't know whether this is lounge or drawing room material... no naturally I went upper class

Edit: So it ends up in the toy room? I thought toys were fun?

Anyway, I'm having trouble with a very simple correlated subquery in MySQL.

I want to query the first table and get the second table (IE the most recent version of all the records).

-------------------------------------------------------

:lamer:

:elefant: lol

:lamer:

Lamer? I know I'm a lamer........ but don't know how! :D

I have heard tell that MySQL doesn't support correlated subqueries (from our webmaster, as a matter of fact)

I have heard tell that MySQL doesn't support correlated subqueries (from our webmaster, as a matter of fact)

First: Thank you Nick and Nick's wembaster (Edit: oops, that'll be Colin then)

Second: :pants: WTF????? It's even in the (F) manual!

http://www.mysql.com/doc/en/Correlated_subqueries.html

Third: Is there a workaround?

Aaaaah. Solved it! All working now. Cheers Nick (you provided me with the clue!) and a big :slap: to me.

First: Thank you Nick and Nick's wembaster

Second: :pants: WTF????? It's even in the (F) manual!

http://www.mysql.com/doc/en/Correlated_subqueries.html

Third: Is there a workaround?

Yup just looked at it myself - it does have some kind of caveats tho. When I said our webmaster - I meant Briskoda (i.e. Colin)

More of an Oracle man - would work fine in that. Must have a play with MySQL sometime.

Not sure about workarounds - when you want an aggregate to match against it's difficult to do with a join.

Should be careful of aggregates in joins in Oracle, as they cause full table scans. Same with functions, too :(

SELECT * FROM table1, table2

WHERE UPPER(table1.column1) = table2.column1

is case in point. Learned the hard way.

Going back to Simon's original - couldn't you use a GROUP BY and a MAX to achieve the same thing?

Should be careful of aggregates in joins in Oracle' date=' as they cause full table scans. Same with functions, too :(

SELECT * FROM table1, table2

WHERE UPPER(table1.column1) = table2.column1

is case in point. Learned the hard way.[/quote']

Be careful from a performance point of view, you mean? Yeah, I guessed that the query I am going to use will not be the quickest known to man, but I think that the only alternative is a rather major tables redesign. Unless you know something I don't.* Thanks for the "heads up", though.

(*More than likely )

Going back to Simon's original - couldn't you use a GROUP BY and a MAX to achieve the same thing?

Do continue.....

Hmm, was thinking of the code below, but unsure what 'Value' would be set to....

SELECT MAX(RecordID), MAX(RecordVersion), Value

FROM Table1 AS temp

GROUP BY temp.RecordID, temp.RecordVersion

Still thinking.....

... more thoughts....

Create a view for the select MAX part and then join to the view in your original query.

... more thoughts....

Create a view ...

I think views are a no go with MySQL. CSQs are only available in the new Alpha release (just installed d'oh).

How would that be more efficient than a CSQ?

Aha - so I was partially right - they're only JUST supported - my conversation with Colin goes back some months.

I seem to remember in the case we were discussing procedural code was required - kind of - select max() into , followed by select from

Aha - so I was partially right - they're only JUST supported - my conversation with Colin goes back some months.

I seem to remember in the case we were discussing procedural code was required - kind of - select max() into ' date=' followed by select from

Yeah, and that was my problem. I was reading the new documentation, having installed MySQL a couple of months ago - didn't twig what was going on. It didn't occur to me that CSQs might be a new addition to the featureset (because other DBs have supported them for ages) so I didn't check whether I was running the latest version.

For what it's worth, 4.1 is labeled as an Alpha release, but from what I read MySQL have a different meaning of "Alpha" to, say, Microsoft - MySQL alphas are stable.

For what it's worth, 4.1 is labeled as an Alpha release, but from what I read MySQL have a different meaning of "Alpha" to, say, Microsoft - MySQL alphas are stable.

I take it all back :( CSQ functionality seems to be broken in 4.1.1

[rant]How can they call it a database?[/rant]

I'm just about to evaluate Firebird (www.firebird.org) which is supposed to united the speed and stability of MySQL with the database functions of Postgres etc (hey, I should get a job as a marketer:D).

Basically, it's an open source version of Borland interbase (v6.5), which Borland thought would be dead as a commercial product. Strange thing is that Borland decided to resurrect the Interbase product and now make version 7.3 (so I'm told). It is supported ona WIDE range of platforms and it what Banks and Nokia used for YEARS so it is well tested commercially.

That's my 2 cents

Big Rich

I'm just about to evaluate Firebird (www.firebird.org) which is supposed to united the speed and stability of MySQL with the database functions of Postgres etc (hey' date=' I should get a job as a marketer:D).

Basically, it's an open source version of Borland interbase (v6.5), which Borland thought would be dead as a commercial product. Strange thing is that Borland decided to resurrect the Interbase product and now make version 7.3 (so I'm told). It is supported ona WIDE range of platforms and it what Banks and Nokia used for YEARS so it is well tested commercially.

That's my 2 cents

Big Rich[/quote']

Let us know how you get on. Is it likely any time soon? I only ask because the homepage has a sort of vapourware look to it!

I think views are a no go with MySQL. CSQs are only available in the new Alpha release (just installed d'oh).

How would that be more efficient than a CSQ?

It wouldn't have been more efficient (indeed it probably would have been less efficient, but would have achieved your aim if CSQs weren't available and Views were. As neither seem to be available its a bit of a moot point. Sorry!

Dear all,

I've just had a look on Technet to see if there was anything useful that I could add, and unfortunately most of the Knowledge Base Articles say in a very, very typical Microsoft way something along the lines of "This issue may well have been resolved in a later release of the software or in the latest Service Pack, please ensure your software is up to date"...

Chocolate teapot.... solar powered torch etc., etc.

Chaos

Dear all' date='

I've just had a look on Technet to see if there was anything useful that I could add, and unfortunately most of the Knowledge Base Articles say in a very, very typical Microsoft way something along the lines of "This issue may well have been resolved in a later release of the software or in the latest Service Pack, please ensure your software is up to date"...

Chocolate teapot.... solar powered torch etc., etc.

Chaos[/font']

I can blame a lot on MS ..... but not features lacking in third party software

It wouldn't have been more efficient (indeed it probably would have been less efficient' date=' but would have achieved your aim if CSQs weren't available and Views were. As neither seem to be available its a bit of a moot point. Sorry!

[/quote']

Goodness me, Howard - don't apologise! The more ideas being thrown around the better.

In V4.1.1Alpha the

IN

and

= ANY

keywords / synonyms aren't supported, and doing a = results in only one row being returned, rather than one per RecordID - so I think it's broken but still don't know why :confused:

Oh well, I could always buy a MS SQLServer license!