Complex join and sorting the parent and child data

Question

Post reply

Complex join and sorting the parent and child data

ramrajan

SSCommitted

Points: 1737
More actions
July 26, 2016 at 8:03 am

#315595

Instructions
1. First We need to get the records based on CityCode between the two table the output is total no of records count of #City and #SubCity
based on join condition CityCode
2. Order data based on latest year first and then City code and then Subcitycode
Final Out should display like below
------------------------------------------
Price CityCodeSubCityCodeYears
63.66094616ASCC NULL 2016
55.74122275ASCC-II ASCC-II 2016
46.79728235FRCC NULL 2016
32.80067063MRO NULL 2016
48.28697266ASCC NULL 2015
43.5081352 MRO-US MRO-US 2015
87.94235804FRCC NULL 2015
43.5081352 MRO NULL 2015
54.14196456AZNMSN AZNMSN 2015
69.65487472CAMX CAMX 2015
32.4631329 CENTRL CENTRL 2015
61.7535716 NPCC NULL 2015
45.79454251DELTA DELTA 2015
35.59232288GATEWY GATEWY 2015
-------------------------------------------
CREATE TABLE #City
(
Price FLoat,
CityCode varchar(10),
SubCityCode varchar(10),
Years INT
)
CREATE TABLE #SubCity
(
Price FLoat,
CityCode varchar(10),
SubCityCode varchar(10),
Years INT
)
INSERT INTO #City
SELECT 48.2869726635563,'ASCC' ,NULL,2015
UNION ALL
SELECT 87.9423580442767,'FRCC' ,NULL,2015
UNION ALL
SELECT 43.5081351954692,'MRO' ,NULL,2015
UNION ALL
SELECT 61.7535715981744,'NPCC' ,NULL,2015
UNION ALL
SELECT 63.6609461617676,'ASCC' ,NULL,2016
UNION ALL
SELECT 46.7972823504606,'FRCC' ,NULL,2016
UNION ALL
SELECT 32.8006706295531,'MRO' ,NULL,2016
INSERT INTO #SubCity
SELECT 54.1419645596344,'MRO' ,'AZNMSN',2015
UNION ALL
SELECT 69.6548747243506,'MRO' ,'CAMX' ,2015
UNION ALL
SELECT 32.463132901897,'MRO' ,'CENTRL',2015
UNION ALL
SELECT 45.7945425052862,'NPCC' ,'DELTA' ,2015
UNION ALL
SELECT 35.592322881251,'NPCC' ,'GATEWY',2015
UNION ALL
SELECT 43.5081351954692,'ASCC','MRO-US',2015
UNION ALL
SELECT 55.7412227528379,'ASCC','ASCC-II',2016

Viewing 9 posts - 1 through 8 (of 8 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

is it as simple as this ....??

SELECT * FROM (

SELECT * FROM #City

UNION ALL

SELECT * FROM #Subcity) x

ORDER BY Years DESC, Citycode, subcitycode

edit....just rechecked my results.....aint what you have asked for....hmmm.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Awesome job posting ddl, sample data and desired output. I have been staring at this for several minutes and I can't figure out the rules here at all. A join between those two tables using CityCode and the sample provided returns 12 rows, you have 14 in your desired output.

select *

from #City c

join #SubCity s on s.CityCode = c.CityCode

order by c.Years, c.CityCode

Can you explain the rules for how you expect the output to be?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

cant fathom your sort order...please explain

cant see how "Order data based on latest year first and then City code and then Subcitycode " delivers what you have posted?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

Piling on, the demonstrated expected output does not fit the description, can you please clarify?

😎

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

J Livingston SQL (7/26/2016)
cant fathom your sort order...please explain
cant see how "Order data based on latest year first and then City code and then Subcitycode " delivers what you have posted?

G, 3 of the rows in the result set are out of order. This nails it, I reckon:

SELECT

Price,

CityCode = ISNULL(SubCityCode,CityCode),

SubCityCode = SubCityCode,

Years

FROM (

SELECT c.* FROM #City c UNION ALL SELECT sc.* FROM #SubCity sc

) d

ORDER BY d.Years DESC, d.CityCode, d.Price 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

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

Eirikur Eiriksson (7/26/2016)
Piling on, the demonstrated expected output does not fit the description, can you please clarify?
😎

The rows for citycode 'MRO' are upside down 🙂

^{“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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

ChrisM@Work (7/26/2016)
J Livingston SQL (7/26/2016)
cant fathom your sort order...please explain
cant see how "Order data based on latest year first and then City code and then Subcitycode " delivers what you have posted?
G, 3 of the rows in the result set are out of order. This nails it, I reckon:
SELECT
Price,
CityCode = ISNULL(SubCityCode,CityCode),
SubCityCode = SubCityCode,
Years
FROM (
SELECT c.* FROM #City c UNION ALL SELECT sc.* FROM #SubCity sc
) d
ORDER BY d.Years DESC, d.CityCode, d.Price DESC

Chris...thats exactly where I had reached, but was waiting to see if OP came up with some more sample data and rationale.....OP comment in title of "parent and Child" had me wondering 🙂

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

drew.allen SSC Guru Points: 76997 More actions · Answer 8

J Livingston SQL (7/26/2016)
is it as simple as this ....??
SELECT * FROM (
SELECT * FROM #City
UNION ALL
SELECT * FROM #Subcity) x
ORDER BY Years DESC, Citycode, subcitycode
edit....just rechecked my results.....aint what you have asked for....hmmm.

J Livingston was close. The confusing thing is that the parent city isn't displayed for child cities, but it's still sorted by the parent city.

SELECT Price, CityCode, SubCityCode, Years FROM (

SELECT Price, CityCode, SubCityCode, Years, CityCode AS CitySort FROM #City

UNION ALL

SELECT Price, SubCityCode, SubCityCode, Years, CityCode FROM #Subcity

) x

ORDER BY Years DESC, CitySort, subcitycode

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA