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

JOIN in subquery Expand / Collapse
Author
Message
Posted Thursday, May 15, 2014 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 12:18 PM
Points: 21, Visits: 200
How would I convert this query to use a JOIN in the subquery:

SELECT ProcDate , MAX(b.DayKey) DayKey
FROM dbo.MyTable a ,
( SELECT MAX(DayKey) DayKey
FROM dbo.MyTable
WHERE ExtractType = 'D'
) b
WHERE ( ExtractType = 'D' )
AND a.Daykey = b.DayKey
GROUP BY ProcDate
Post #1571407
Posted Thursday, May 15, 2014 10:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 13,302, Visits: 12,160
dcwilson2009 (5/15/2014)
How would I convert this query to use a JOIN in the subquery:

SELECT ProcDate , MAX(b.DayKey) DayKey
FROM dbo.MyTable a ,
( SELECT MAX(DayKey) DayKey
FROM dbo.MyTable
WHERE ExtractType = 'D'
) b
WHERE ( ExtractType = 'D' )
AND a.Daykey = b.DayKey
GROUP BY ProcDate


Not sure what the question is. Do you need the subquery to join to something else? Or is the question how can you use a join instead of the subquery?

From what you posted I don't understand the need for a subquery here at all.

SELECT ProcDate, MAX(DayKey) as DayKey 
FROM dbo.MyTable a
WHERE ExtractType = 'D'
GROUP BY ProcDate



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1571416
Posted Thursday, May 15, 2014 10:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 12:18 PM
Points: 21, Visits: 200
Thanks for the reply. My query gives a single row. Yours gives me multiple rows. But my query has 2 objects in the FROM clause instead of a JOIN, joining the results from the subquery to the rest of it.

I Do think I have found the answer to my own question. It does work:
SELECT a.ProcDate, MAX(b.DayKey) DayKey
FROM dbo.MyTable a
INNER JOIN ( SELECT MAX(DayKey) DayKey
FROM dbo.MyTable
WHERE ExtractType = 'D'
) b
ON a.DayKey = b.DayKey
WHERE ( a.ExtractType = 'D' )
GROUP BY a.ProcDate

Let me know if you think this would be correct in general.
Post #1571423
Posted Thursday, May 15, 2014 12:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 13,302, Visits: 12,160
So you only want to get the ProcDate and DayKey for the row with the highest DayKey?

How about this?

SELECT top 1 ProcDate, DayKey 
FROM dbo.MyTable a
WHERE ExtractType = 'D'
order by DayKey desc

It is difficult to say if this is correct because we haven't seen and ddl, sample data or desired output.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1571469
Posted Thursday, May 15, 2014 12:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 13,302, Visits: 12,160
dcwilson2009 (5/15/2014)
Thanks for the reply. My query gives a single row. Yours gives me multiple rows.


You only get one row because there is only one row with the max DayKey value. If you have two rows with the same DayKey value you would get two rows in your output.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1571472
Posted Friday, May 16, 2014 12:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 12:18 PM
Points: 21, Visits: 200
Good point, Sean. I am going to have to think about that one. I don't think there would be 2 or more proc dates with the same DayKey, but I do have to think about it. It would probably have to be counted as a failure, rather than processed for that, though. I probably should have provided some DDL, etc. At first, I was just trying to find the old and new syntax.
Thank you.
Post #1571856
Posted Friday, May 16, 2014 12:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 13,302, Visits: 12,160
dcwilson2009 (5/16/2014)
Good point, Sean. I am going to have to think about that one. I don't think there would be 2 or more proc dates with the same DayKey, but I do have to think about it. It would probably have to be counted as a failure, rather than processed for that, though. I probably should have provided some DDL, etc. At first, I was just trying to find the old and new syntax.
Thank you.


Doesn't the last query I posted using top 1 produce the same output? That would be better because it only has to hit the table once, and it is way easier to decipher.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1571871
Posted Tuesday, May 20, 2014 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 12:18 PM
Points: 21, Visits: 200
Yes, it does, Sean. Thank you. I didn't thing of using TOP 1.
Post #1572731
Posted Tuesday, May 20, 2014 9:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 13,302, Visits: 12,160
dcwilson2009 (5/20/2014)
Yes, it does, Sean. Thank you. I didn't thing of using TOP 1.


Cool glad that works for you.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1572734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse