SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


JOIN in subquery


JOIN in subquery

Author
Message
dcwilson2009
dcwilson2009
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 257
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25932 Visits: 17519
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 Modens 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)
dcwilson2009
dcwilson2009
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 257
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25932 Visits: 17519
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 Modens 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)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25932 Visits: 17519
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 Modens 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)
dcwilson2009
dcwilson2009
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 257
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25932 Visits: 17519
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 Modens 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)
dcwilson2009
dcwilson2009
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 257
Yes, it does, Sean. Thank you. I didn't thing of using TOP 1.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25932 Visits: 17519
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 Modens 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)
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