Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Query Output


Help with Query Output

Author
Message
kalikoi
kalikoi
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 36
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8966 Visits: 19020
A simple query exactly described generates this dataset:

Col1   col2   col3   #   ID   Col1   col2   displayorder   #   Col1   col2   col3
5 C05 3 # 3 1 3 1 # 5 1 5
6 C06 1 # 1 1 1 2 # NULL NULL NULL
1 C01 1 # 1 1 1 2 # 1 1 1
3 C03 1 # 1 1 1 2 # 3 1 3
4 C04 2 # 2 1 2 3 # 4 1 4
2 C02 2 # 2 1 2 3 # 2 1 2



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
Exploring Recursive CTEs by Example Dwain Camps
kalikoi
kalikoi
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 36
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8966 Visits: 19020
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
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8966 Visits: 19020
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
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45079 Visits: 39910
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search