March 29, 2011 at 12:07 pm
It hit me that without doing an alter on the #table to add the column temp table and then update the temp #table, I can not get the record count to stay the same. I cannot believe I do not know any other way.
table 'a' has a one to many relationship with table 'b'.
Without the join I get 2219944 rows. After my join below I get over 3000000 rows.
I just want to see the SubmissionBatchID column from the other table with only 2219944 rows returned.
I tried left join and right join too.
Select a.*,b.SubmissionBatchID
from
tblsubmissions a
join
tblsubmissionHistory b
on a.submissionID = b.submissionID
where a.currentstatus = 'P' and year(a.DOS) > 2009
Thanks in advance for the help!
Adam
March 29, 2011 at 12:34 pm
You could use
Select DISTINCT a.*,b.SubmissionBatchID
If you still get more than 2219944 then you have more than one distinct value of b.SubmissionBatchID per submissionID.
As a side note: I recommend to avoid using SELECT *. Instead, specifically name the columns you want to return.
March 29, 2011 at 1:02 pm
I tried distinct to no avail. Yes it is a 1 tomany relationship, so how can I just add column b.submissionbatch so my result without the records going up.
Is there not a way?
March 29, 2011 at 1:17 pm
You need to define the submissionbatch you want to return if there is more than one. You could either use MIN(),MAX(),SUM() or any other aggregation you like.
But then you'd need to use a GROUP BY and specify all other columns you have in your select list.
Or you could use a CTE (or subquery) and pre-aggregate the data from tblsubmissionHistory based on submissionID and join the CTE to your tblsubmissions table.
March 29, 2011 at 1:21 pm
Can you help me using a CTE?, I am trying to understand what their purpose is exactly and how and when to use them
March 29, 2011 at 1:47 pm
Here's the CTE solution using MIN(SubmissionBatchID).
Side note: I also change the way to query for the year 2009 to make it SARGable...
;WITH cte AS
(
SELECT submissionID, MIN(SubmissionBatchID)
FROM tblsubmissionHistory
GROUP BY submissionID
)
SELECT a.*,cte.SubmissionBatchID
FROM tblsubmissions a
JOIN cte b
ON a.submissionID = b.submissionID
WHERE a.currentstatus = 'P' AND a.DOS >= '20090101' AND a.DOS<'20100101'
March 29, 2011 at 7:49 pm
LutzM (3/29/2011)
You need to define the submissionbatch you want to return if there is more than one. You could either use MIN(),MAX(),SUM() or any other aggregation you like.But then you'd need to use a GROUP BY and specify all other columns you have in your select list.
Or you could use a CTE (or subquery) and pre-aggregate the data from tblsubmissionHistory based on submissionID and join the CTE to your tblsubmissions table.
Or, you can convert the join to a CROSS APPLY and use either MAX/MIN or TOP 1. Something like this:
SELECT a.*
,t.SubmissionBatchID
FROM tblsubmissions a
CROSS APPLY (
SELECT TOP 1 SubmissionBatchID
FROM tblSubmissionHistory b
WHERE b.submissionID = a.submissionID
ORDER BY b.submissionID) AS t
WHERE a.currentstatus = 'P'
AND a.DOS >= '20090101'
AND a.DOS < '20100101'
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply