mysql 4.1.1 & subqueries

[BEFOR]

I'm about to switch servers from mysql 4.0.2 to 4.1.1

I'm doing this mainly to be able to run subqueries.

It's my understanding that to join many to many tables(composite index) with more than a single table having multiple rows per user (pulling from the last row with DESC LIMIT 0,1) I need to do so with subqueries. (forgetting the binary 0/1 "flag concept) Also I ned to join multiple many to many tables by the same value. Is this true that I need the subqueries?

Also, I have reviewed the mysql documentation on 4.1.1 authentication issues and am trying to figure out if this is for encrypted passwords or all passwords in general. Too, I've heard there can be a connection problem through certain php versions with 4.1.1

The host I'm looking at has php 4.3.2 and mysql 4.1.1a

Much appreciate any advice.

[Sxooter]

While there may be some things you want to do that will need sub selects, to just join data in a many to many relationship you don't need subselects.

If you're looking at 4.1, you might want to go ahead and try 5.0.4 and see how it works. It seems like MySQL 5 is quickly heading towards release quality, and it has many new features as well. Plus the query planner is supposed to be a lot smarter about subselects and such.

[BEFOR]

I've had enormous problems joining many to many tables with repeat-row tables, even consulting a well-known author and an expert an mysql. Posting both here and on other forums.

to just join data in a many to many relationship you don't need subselects

This is a many to many table and more than one repeating row table. Possibly two many to many tables.

I found (1) programmer that could successfully join a many to many with a repeat row (other normal tables in the join, and pulling from the last row of the repeat table).

The programmer told me to NEVER do it more than once ("getting the MAX of a group). It cannot be done. (without subqueries)

Doing more a m-t-m to more than a (1) repreat row table seems to be a real feat.

My problem is that I must join mulitple many to many tables with repeat row tables.

I'm trying to verify this because I'm going to the trouble of changing servers because of this alone. (current host is on mysql 4.0.24)

Obviously I'm not a programmer.

[Sxooter]

Generally, when doing many to many, you're using primary keys, which are unique, as the join keys.

If you're using a repeating row, then in MySQL you're probably better off using temp tables than sub selects, at least in 4.x and before, as mysql is still pretty pokey at doing complex things with subselects.

I imagine you use a subselect to do some kind of (select distinct field from table) thing? in that case, just select those into a temp table and use that to join to the other tables.

It's a pain, but that's the MySQL way of doing things, at least until the query planner grows a bigger brain . 5.0 may be better at planning sub queries.

Myself, I use MySQL for simple data sets and content management, and postgresql for complex data mining.

[BEFOR]

Haven't done subselects, changing over to 4.1.1a this week.