Help with Query Output

Question

Post reply

Help with Query Output

kalikoi

SSC Journeyman

Points: 94
More actions
February 18, 2014 at 3:27 am

#288239

I have three tables as follows
TableA
------
Col1 col2 col3
1 C01 1
2 C02 2
3 C03 1
4 C04 2
5 C05 3
6 C06 1
TableB(here col2 references to col3 from TableA)
------
ID Col1 col2 displayorder
1 1 1 2
2 1 2 3
3 1 3 1
TableC(here col2 refers to col1 from TableB and col3 refers to col1 from tableA)
------
Col1 col2 col3
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 6 6
Based on the three tables i need to generate output from TableA as follows
5 c05
6 c06
3 C03
1 C01
4 C04
2 C02
can anyone help me on this

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kalikoi SSC Journeyman Points: 94 More actions · Answer 2

that's my typo C04 shoould be included

the output should be

5 c05

6 c06

3 C03

1 C01

4 C04

2 C02

not

5 c05

6 c06

1 c01

3 c03

4 c04

2 c02

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Why?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

This will do it, but it's somewhat arbitrary:

ORDER BY b.displayorder, ISNULL(c.col1,2147483647) DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

kalikoi (2/18/2014)
I have three tables as follows
TableA
------
Col1 col2 col3
1 C01 1
2 C02 2
3 C03 1
4 C04 2
5 C05 3
6 C06 1
TableB(here col2 references to col3 from TableA)
------
ID Col1 col2 displayorder
1 1 1 2
2 1 2 3
3 1 3 1
TableC(here col2 refers to col1 from TableB and col3 refers to col1 from tableA)
------
Col1 col2 col3
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 6 6
Based on the three tables i need to generate output from TableA as follows
5 c05
6 c06
3 C03
1 C01
4 C04
2 C02
can anyone help me on this

Chris is a generous man but he's not always available. Please see the first link under "Helpful Links" in my signature line below for the best way to post your data in the future. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)