How can you do a Order by based on having a query on multiple tables?

I am using MySQL 3.22.28 as my database...

When I use this code WITHOUT the Order by, I get records, so it works...

Select ADPE.*, equipment.*, Location.*
FROM ADPE, equipment, Location
WHERE ADPE.MEDL_No = equipment.MEDL_No and ADPE.MEDL_No = Location.MEDL_No

When I order by any field in ADPE, I get records...

but when I try to Order by a field that isn't from ADPE the Order by doesn't work...

Select ADPE.*, equipment.*, Location.*
FROM ADPE, equipment, Location
WHERE ADPE.MEDL_No = equipment.MEDL_No and ADPE.MEDL_No = Location.MEDL_No
Order By Location.MEDL_No

Select ADPE.*, equipment.*, Location.*
FROM ADPE, equipment, Location
WHERE ADPE.MEDL_No = equipment.MEDL_No and ADPE.MEDL_No = Location.MEDL_No
Order By equipment.MEDL_No

Can any one help with this order by problem.... I am trying to have an order by where it allows the user to order by any table field and have an order by where the fields listed in the order by can also be from different tables.... is that possible....

I've moved your post to the correct forum.

What doesn't work? Do you get an SQL error, or does it not order it properly? If it's an error, what does the error say?

That's quite an old version of MySQL, but there shouldn't be any problem running an oder by on other columns.

Cheers,

Alex
--
Gossamer Threads Inc.

SQL-query : SELECT ADPE.*, equipment.*, location.*FROM ADPE, equipment, locationWHERE ADPE.MEDL_No = equipment.MEDL_Noand ADPE.MEDL_No = location.MEDL_NoORDER BY location.MEDL_No LIMIT 0, 100



MySQL said:
Can't create/write to file '\\sql30d73_0.ism' (Errcode: 2)this is the error message I have been getting... I can only sort by the fields in ADPE... also I would like to sort by different combinations of fields from more than one table....currently, the above is what I have been getting for an error....

You need to define a tmp directory. I'm guessing this is on windows? If so, you really should try upgrading to a newer version, as older versions had issues setting preferences on windows.

Cheers,

Alex
--
Gossamer Threads Inc.

so this error is more a windows preference thing....

so my sql statement is actually correct? I wasn't sure if in sql, you could have a selection statement on 3 or more tables in which you can do an order by based on columns in any of the tables....

thanks..

Yes, the SQL is correct, the problem is MySQL can not write to a temp folder it needs in order to do the order by. You need to set the tmp_dir preference in mysql to a folder (usually c:\temp or something similiar).

However, early versions of MySQL on windows had issues with trying to set preferences, and it's much, much easier to do it on the later versions.

Cheers,

Alex
--
Gossamer Threads Inc.

So, you would suggest, that I should upgrade to a higher version of MySQL to solve this problem... is there a specific version that solves this problems... what is the most current version of MySQL out there? would the latest version of MySQL take care of this problem?

thanks