How to sort by using ORDER using a field as the sort category in other table within the same database file?

Other Tech Topics
1

Using a VB6.0 DAO Database in my software, green on Databases & SQL so my question might be a simple one:

I'm looking for a SQL query solution to get a RecordSet in a sorted result where the sort category sits in a different table in the same Database file

Database structured as below:

DBase picture

This works fine:
SELECT * FROM BASETable ORDER BY PointNumber ASC

But:

SELECT * FROM BASETable,DescriptionTable ORDER BY DescriptionTable.Description ASC

comes up with 7 times repeated data in the RS, unsorted

Any ideas to solve it into a single list, sorted by the sort category are most welcome,

Thanks for your help.

2

hi

you have to join the two tables ...
do the order by

OR

create a temp table with column order by you want
join the temp table to main table
do the order by

hope this helps
::slight_smile:

1 Like
3

Thanks harishgg1,

joined the two tables and added the WHERE function which solved it:

SELECT * FROM BASETable,DescriptionTable
WHERE BASETable.D_ID=DescriptionTable.Id
ORDER BY DescriptionTable.Description ASC

4

hi

joins are done like this in SQL

select * from tableA join tableB on tableA.columnz = tableB.columnz

1 Like
5

Ok,

tried the JOIN word instead of a comma but resulted in an error in the SQL line where the comma solved it fine.

Must be the ancient VB6.0 DAO environment I'm using as a lot of solutions here are causing errors in my program. Not exchanging the old Access 95 database I use anyway, just using it to import ASCII text data, sort and manipulate and export it back to a ASCII text file as the Land Survey controllers I use are ASCII text based.

thanks for your info.

6

hi

:slight_smile: :slight_smile:

SQL Server Microsoft ( thats what i am using )

there are so many variations and limitations
from one thing to other

It can drive anyone crazy
-- or at least me

-- some dont work in other applications
-- same thing is done differently
-- cannot do becuase no functionality
-- same thng to do is 100 times more code

if you are learning for the first time ..
then its even more painful