Avatar of buttonMASTER
buttonMASTER
 asked on

Writing a sorting query involving two tables

I have two tables that looks like this:

Data_Table

Name               Type               
------------------ ------------------ 
ID                 NUMBER
Data               DOESN'T_MATTER

Open in new window


Sort_Table

Name               Type               
------------------ ------------------ 
ID                 NUMBER
Reference_ID       NUMBER
Order              NUMBER

Open in new window


The values in that Data_table are:

ID		data
----------------------------------------------------
1		value_1
2		value_2
3		value_3
4		value_4
5		value_5

Open in new window


The values in the Sort_Table are:

ID		reference_id	order
----------------------------------------------------
1		2		2
2		3		1
3		5		3

Open in new window


Where the reference ID refers to the ID in the data_table.

I need a query to get the IDs from the Data_table in the order defined in the sort_table, but if anything from the data_table doesn't show up in the sort_table I want it to show up at the end like this:

3
2
5
1 -- this isn't in sort_table
4 -- this isn't in sort_table

Open in new window

DatabasesSQL

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Pavel Celba

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
buttonMASTER

ASKER
That worked. Thank you very much!
Pavel Celba

Great!

Please note the NULL value can be sorted differently depending on SQL engine used. If values missing in the Sort_table are at the top you may use different expression in ORDER BY, e.g.:

ORDER BY -s.order DESC, d.ID

Also the [order] column name must be enclosed in brackets in some SQL engines...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy