January 18, 2012 at 12:36 pm
I'm trying to join 2 tables. This is a one-to-many (1-N) join. I have a table with accounts and another table with information about those accounts. The "many" table could potentially have up to 12 entries for each account. However, I only want to return the next (future) due date on the account. I could potentially have several future dates, but I only want the next one chronologically.
If there is no future entry for a specific account, I want NULL values so that I still get a row with that account.
I know I need a LEFT JOIN to accomplish this but everything I've written so far only returns the rows from the 2nd table with due dates. I want all the accounts, not just the ones with due dates.
January 18, 2012 at 12:49 pm
Please see the first link in my signature. You need to provide ddl (create table), sample data (insert statements) and a clear explanation of what your desired output is based on your sample data.
_______________________________________________________________
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/
January 18, 2012 at 1:15 pm
Investigate using "CROSS APPLY ( SELECT TOP(1)" , sounds like its what you need.
January 18, 2012 at 2:17 pm
Dave,
Thanks. Looks like OUTER APPLY is the way to go. Now, how do I do the "next" due date only? So, if there's 2 future due dates, I only want the next one not the one that follows it and we don't want any past due dates.
January 18, 2012 at 2:20 pm
That is where the "Top 1" will come in handy.
cross apply (select top 1 date from table order by DateCol desc)
_______________________________________________________________
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/
January 18, 2012 at 2:25 pm
So something like this ?
Create table #Cust
(
CustomerId integer
)
go
Create Table #Orders
(
OrderId integer,
CustomerId integer,
DueDate date)
go
insert into #Cust(CustomerId) values(1)
go
insert into #Orders(OrderId,CustomerId,DueDate)
values(1,1,'20120101')
insert into #Orders(OrderId,CustomerId,DueDate)
values(2,1,'20120201')
insert into #Orders(OrderId,CustomerId,DueDate)
values(2,1,'20120301')
go
select * from
#Cust Outer apply (Select top(1) #Orders.*
from #Orders
where #Orders.CustomerId = #Cust.CustomerId
and DueDate >= GETDATE()
order by DueDate asc) as NextOrderDue
January 18, 2012 at 8:25 pm
Scott D. Jacobson (1/18/2012)
I'm trying to join 2 tables. This is a one-to-many (1-N) join. I have a table with accounts and another table with information about those accounts. The "many" table could potentially have up to 12 entries for each account. However, I only want to return the next (future) due date on the account. I could potentially have several future dates, but I only want the next one chronologically.If there is no future entry for a specific account, I want NULL values so that I still get a row with that account.
I know I need a LEFT JOIN to accomplish this but everything I've written so far only returns the rows from the 2nd table with due dates. I want all the accounts, not just the ones with due dates.
You want all the rows from the second table??? Well, that's a right join, not a left join - perhaps that's your problem? Try "right outer join" and "full outer join" instead of "left outer join" and see if one of them givies you what you want.
Tom
January 19, 2012 at 2:41 pm
Tom,
Other way around. I want all the rows from the first table. If there's nothing to join on in the 2nd table, I'll take NULLs.
It seems to me that this has some pretty good coverage: http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
Thanks for all the help folks. I'll pop back if I can't work this out.
January 19, 2012 at 2:46 pm
I think some here are operating under the assumption that no previous due dates are in that table. That's why they are selecting TOP 1.
My guess is that the assumption is incorrect and you may have old (more than 1), current (only 1), and future (more than 1). Am I correct?
EDIT: I saw script with duedate > GETDATE() 😉 Sorry!
Jared
CE - Microsoft
January 19, 2012 at 3:30 pm
Scott D. Jacobson (1/18/2012)
I'm trying to join 2 tables. This is a one-to-many (1-N) join. I have a table with accounts and another table with information about those accounts. The "many" table could potentially have up to 12 entries for each account. However, I only want to return the next (future) due date on the account. I could potentially have several future dates, but I only want the next one chronologically.If there is no future entry for a specific account, I want NULL values so that I still get a row with that account.
I know I need a LEFT JOIN to accomplish this but everything I've written so far only returns the rows from the 2nd table with due dates. I want all the accounts, not just the ones with due dates.
I think I know the problem here. These two queries are syntactically different:
-- Gets all of tbla
SELECT
a.*,
MIN( b.SomeDate) AS NextDueDate
FROM
tbla AS a
LEFT JOIN
tblb AS b
ON a.id = b.id
AND b.SomeDate >= GETDATE()
-- Does all JOINS, THEN reduces by the WHERE clause, tbla restricted
SELECT
a.*,
MIN( b.SomeDate) AS NextDueDate
FROM
tbla AS a
LEFT JOIN
tblb AS b
ON a.id = b.id
WHERE
b.SomeDate >= GETDATE()
When you want to limit the attached information in an outer join, you need to include your restriction in the ON clause, not the following WHERE clause. The WHERE clause will fire after the outer join completes.
EDIT: Yes, it's missing group bys, I wasn't going for syntactically perfect, before someone nails me on that... 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 20, 2012 at 8:24 pm
Evil Kraig F (1/19/2012)
Scott D. Jacobson (1/18/2012)
I'm trying to join 2 tables. This is a one-to-many (1-N) join. I have a table with accounts and another table with information about those accounts. The "many" table could potentially have up to 12 entries for each account. However, I only want to return the next (future) due date on the account. I could potentially have several future dates, but I only want the next one chronologically.If there is no future entry for a specific account, I want NULL values so that I still get a row with that account.
I know I need a LEFT JOIN to accomplish this but everything I've written so far only returns the rows from the 2nd table with due dates. I want all the accounts, not just the ones with due dates.
I think I know the problem here. These two queries are syntactically different:
-- Gets all of tbla
SELECT
a.*,
MIN( b.SomeDate) AS NextDueDate
FROM
tbla AS a
LEFT JOIN
tblb AS b
ON a.id = b.id
AND b.SomeDate >= GETDATE()
-- Does all JOINS, THEN reduces by the WHERE clause, tbla restricted
SELECT
a.*,
MIN( b.SomeDate) AS NextDueDate
FROM
tbla AS a
LEFT JOIN
tblb AS b
ON a.id = b.id
WHERE
b.SomeDate >= GETDATE()
When you want to limit the attached information in an outer join, you need to include your restriction in the ON clause, not the following WHERE clause. The WHERE clause will fire after the outer join completes.
EDIT: Yes, it's missing group bys, I wasn't going for syntactically perfect, before someone nails me on that... 🙂
I would say it's better to include it before the ON clause, so that it's clear what's going on: in this case
SELECT
a.*,
MIN( b.SomeDate) AS NextDueDate
FROM
tbla AS a
LEFT JOIN
(select SomeDate from tblb where b.SomeDate > GETDATE()) b
ON a.id = b.id
BUT: I wouldn't be surprised if all three versions produced the same query plan, it probably depends on the data/statistics.
sdit: and of course I'm deliberately omitting the group bys too.
Tom
January 22, 2012 at 12:30 am
L' Eomot Inversé (1/20/2012)
I would say it's better to include it before the ON clause, so that it's clear what's going on: in this case
BUT: I wouldn't be surprised if all three versions produced the same query plan, it probably depends on the data/statistics.
Actually, Tom, I could see the one where you're using the subquery and I'm including it in the ON clause producing the same query plan, but the one where the WHERE occurs would definately be different, simply because it has to filter at a different point of inclusion.
Your version would definately be more clear, I'm just used to working directly in the ON clause at this point so it's easier for me to read that then subqueries. When I see a subquery I usually assume some special logic is occuring (like subtotaling) other than a pre-join filter.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply