A simple query exactly described generates this dataset:

Col1col2col3#IDCol1col2displayorder#Col1col2col3

5C053#3131#515

6C061#1112#NULLNULLNULL

1C011#1112#111

3C031#1112#313

4C042#2123#414

2C022#2123#212

Here's a sample data script and a query constructed from your notes:

DROP TABLE #TableA

CREATE TABLE #TableA (Col1 INT, col2 VARCHAR(3), col3 INT)

INSERT INTO #TableA VALUES

(1, 'C01', 1),

(2, 'C02', 2),

(3, 'C03', 1),

(4, 'C04', 2),

(5, 'C05', 3),

(6, 'C06', 1)

CREATE TABLE #TableB (ID INT, Col1 INT, col2 INT, displayorder INT)

INSERT INTO #TableB VALUES

(1, 1, 1, 2),

(2, 1, 2, 3),

(3, 1, 3, 1)

-- (here col2 refers to col1 from TableB and col3 refers to col1 from tableA)

DROP TABLE #TableC

CREATE TABLE #TableC (Col1 INT, col2 INT, col3 INT)

INSERT INTO #TableC VALUES

(1, 1, 1),

(2, 1, 2),

(3, 1, 3),

(4, 1, 4),

(5, 1, 5),

(6, 6, 6)

-- simple query

SELECT a.*, '#' '#', b.*, '#' '#', c.*

FROM #TableA a

left JOIN #TableB b ON b.col2 = a.col3

left JOIN #TableC c ON c.col2 = b.col1 AND c.col3 = a.col1

ORDER BY b.displayorder

You will have to provide more information. How is the output order defined? How is the row with value 'C04' eliminated?

Edit: missed TableA.