Order 2 tables by the same field in each table, mysql, classic asp

Question

I am trying to order data from 2 tables to display in one list also based on the id's from a third table.

Table 3 contains ID's to the other 2 tables.

e_allocation table
------------------
eid, sid, iid

5, 1234, NULL

5, NULL, 1234

table 1 and 2 are tables of similar data.

i_status table
--------------
iid, status

1234, Complete

1235, Complete

1236, Not Complete

s_status table
--------------
sid, status

1234, Complete

1235, Not Complete

1237, Not Complete

objCon.Execute ("SELECT a.sid, a.iid, b.status AS istats, c.status AS sstats
FROM e_allocation a LEFT JOIN i_status b ON a.iid=b.iid LEFT JOIN s_status c
ON a.sid=c.sid WHERE a.eid = '5' ORDER BY b.status Desc, c.status Desc")

So Currently I can get the data from the database but it obviously orders table b first and then c second. I need these ordered as joined.

Currently returning (Table=I or S, Record ID=1234, Status = Complete or Not Complete):

I:1234:Complete

I:1235:Complete

I:1236:Not complete

S:1234:Complete

S:1235:Not Complete

S:1237:Not Complete

I'm trying to get:

I:1234:Complete

S:1234:Complete

I:1235:Complete

S:1235:Not Complete

I:1236:Not complete

S:1237:Not Complete

while not objDb.EOF
    sid = objDb("id")
    iid = objDb("iid")
    if sid <>"" then datlst = datlst &"S:"& sid &":"&objDb("sstats")&",<BR>"
    if iid <>"" then datlst = datlst &"I:"& iid &":"&objDb("istats")&",<BR>"
    objDb.MoveNext
Wend
response.write datlst

Any pointers in the right direction greatly appreciated? Thanks

Answer 1

Here's my attempt.

create table e_allocation (eid int, sid int, iid int);
insert into e_allocation select 5, 1234, NULL;
insert into e_allocation select 5, NULL, 1234;
insert into e_allocation select 5, 1235, NULL;
insert into e_allocation select 5, NULL, 1235;
insert into e_allocation select 5, 1236, NULL;
insert into e_allocation select 5, NULL, 1236;

create table i_status (iid int, status varchar(25));
insert into i_status select 1234, 'Complete';
insert into i_status select 1235, 'Complete';
insert into i_status select 1236, 'Not Complete';

create table s_status (sid int, status varchar(25));
insert into s_status select 1234, 'Complete';
insert into s_status select 1235, 'Not Complete';
insert into s_status select 1237, 'Not Complete';

SELECT
  case when a.sid is null then 'I' else 'S' end as iors,
  ifnull(a.sid,a.iid) as id,
  ifnull(b.status,c.status) as status
FROM e_allocation a LEFT JOIN i_status b ON a.iid=b.iid LEFT JOIN s_status c
ON a.sid=c.sid
WHERE a.eid = '5' and (b.status is not null or c.status is not null)
ORDER BY id, iors

Another time please do provide the CREATE TABLE etc. statements so we don't have to type them in.