help with query - no subqueries :(

[siva]

I have three tables:

generic_form
---------------
formid
firstname
lastname
address1
address2
borrower

form_user
---------------
form_userid
userid
formid

form_history
-----------------
form_historyid
form_userid
created (timestamp)

There's a one to many relationship from form_user to form_history (form_history has mutliple entries for each form_history.form_userid)

I want to join the two tables, but only select the oldest entry in the form_history table.

The way I'd write this with a sub-query would be:

select fu.formid, fh.created, f.borrower, f.address1, f.address2, fs.form_status
from form_user fu, generic_form f, form_status fs, form_history fh
where fh.form_userid = fu.form_userid and fu.formid = f.formid and fh.created IN (select min(created) from form_history)

Please suggest alternatives without using the sub-query, as the MYSQL version I'm working with is pre 4.1.

Thanks!!!

[raf]

Welcome here!

I suppose you'll already realise that your only alternative is to run the subquery first and then use the returned valu inside the where clause of your join-select.

It wount have any noticable impact on performance. (Insert a timer if you're realy woried). If your site gets that much trafic that this little 'one value' extra select has an impact, then i suppose there wount be any reason why you shouldn't upgrade to 4.1.
I would even bet that if a high load situation, doing an extra select to get the highest primary key value (which is instantly returned sine mySQL is optimized to get the highest PK value) and then insert that in the second select, woudn't be much slower then using the subquery where you select on that timestampcolumn.

The only alternative would be to store the highest form_historyid inside a sessionvariable or so. Or if you just inserted a new record inside that table, you could use mysql_insert_id() (check sticky at top of this forum)

But waisting developmenttime trying to avoid that extra select is just pointless.

[siva]

Yes, I realize making two selects is an option.

I find I'm doing this all the time, so I thought I'd take a few minutes to ask around to see if there was a way to do it all in one statement. No big deal, really.


Thanks for the reply.