Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with Query Output Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 3:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:05 AM
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
Post #1542442
Posted Tuesday, February 18, 2014 4:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1542492
Posted Tuesday, February 18, 2014 5:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:05 AM
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
Post #1542512
Posted Tuesday, February 18, 2014 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1542524
Posted Tuesday, February 18, 2014 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1542529
Posted Tuesday, February 18, 2014 6:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542547
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse