using left join and TOP

Question

using left join and TOP

bherman-628226

Valued Member

Points: 54
More actions
May 9, 2012 at 6:33 am

#96839

I have two separate queries that I would like to combine into 1. My first query returns a set of rows from TABLEA. Such as:
SELECT * FROM TABLEA WHERE ChecklistID = 12
This will return say 10 rows. For each one of those rows, I then query TABLEB. There can be multiple rows or no rows in TABLEB for each row in TABLEA, but I only want to return the last one.
SELECT TOP 1 * FROM TABLEB WHERE ChecklistStepID = X ORDER BY ID DESC
(Where X is the ID of the row from TABLEA)
I've tried all sorts of things using left join because I want all the entries from TABLEA even if there are no corresponding rows in TABLEB. Either my query returns all the columns with the entries from TABLEB set to null or it returns multiple rows from TABLEA, one for each entry in TABLEB instead of a single row containing the last entry from TABLEB.
Any assistance would be greatly appreciated.
Thanks,
Brad

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

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

Ant-Green SSC Guru Points: 113563 More actions · Answer 1

Hi and welcome to SSC, please could you follow the second link in my signature block and post the table definitions, sample data and your expected output of based on the data provided so we can help you better.

bherman-628226 Valued Member Points: 54 More actions · Answer 2

bherman-628226

Valued Member

Points: 54

May 9, 2012 at 7:27 am

#1485040

I sure will. Thanks!!

Brad

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

As Anthony said if you provide DDL's ect you will get much better answers

I'll take a shot in the dark, maybe this will help you or put you on the right track:

SELECT *

FROM

TABLEA AS TA

LEFT JOIN(SELECT

*

,ROW_NUMBER() OVER (PARTITION BY ChecklistStepID ORDER BY ID DESC) AS RowNum

FROM

TABLEB

) AS TB

ON TA.ChecklistID = TB.ChecklistStepID AND TB.RowNum = 1

Hope it helps

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

bherman-628226 Valued Member Points: 54 More actions · Answer 4

Here is a set of data/code that outputs using my incorrect query and also has a final #myTableC with the results that I am looking for. I hope this is a better way to formulate my question. Thanks for the guidance.

Brad

IF OBJECT_ID('TempDB..#mytableA','U') IS NOT NULL

DROP TABLE #mytableA

CREATE TABLE #myTableA(

[ID] [int] NOT NULL,

[ChecklistStepDefinitionID] [int] NULL,

[Status] [int] NULL,

[ChecklistID] [int] NULL,

[PlannedUser] [nvarchar](128) NULL,

)

GO

INSERT INTO #myTableA

(ID, ChecklistStepDefinitionID

,Status

,ChecklistID

,PlannedUser)

SELECT '1', '702', '0', '535', 'Brad' UNION ALL

SELECT '2', '703', '1', '535', 'Tim' UNION ALL

SELECT '3', '704', '2', '535', 'Bob' UNION ALL

SELECT '4', '702', '0', '536', 'Derek' UNION ALL

SELECT '5', '703', '1', '536', 'Kyle' UNION ALL

SELECT '6', '704', '2', '536', 'Brad'

IF OBJECT_ID('TempDB..#mytableB','U') IS NOT NULL

DROP TABLE #mytableB

CREATE TABLE #myTableB(

[ID] [int] NOT NULL,

[ChecklistStepID] [int] NULL,

[StartTime] [datetime] NULL,

[EndTime] [datetime] NULL,

[Notes] [nvarchar](1024) NULL,

)

GO

INSERT INTO #myTableB

(ID,

ChecklistStepID

,StartTime

,EndTime

,Notes)

SELECT '1', '1', 'May 4 2012 8:00 AM', 'May 4 2012 8:12AM', 'First Run' UNION ALL

SELECT '2', '1', 'May 4 2012 9:00 AM', 'May 4 2012 9:15AM', 'Second Run' UNION ALL

SELECT '3', '1', 'May 5 2012 7:00 AM', 'May 5 2012 7:10AM', 'Third Run' UNION ALL

SELECT '4', '3', 'May 6 2012 8:00 AM', 'May 6 2012 8:19AM', 'Forth Run' UNION ALL

SELECT '5', '3', 'May 6 2012 9:00 AM', 'May 6 2012 9:40AM', 'Fifth Run' UNION ALL

SELECT '6', '4', 'May 6 2012 10:00 AM', 'May 6 2012 10:05AM', 'Sixth Run' UNION ALL

SELECT '7', '4', 'May 7 2012 8:00 AM', 'May 7 2012 8:34AM', 'Seventh Run' UNION ALL

SELECT '8', '4', 'May 7 2012 9:00 AM', 'May 7 2012 9:30AM', 'Eighth Run' UNION ALL

SELECT '9', '5', 'May 8 2012 8:00 AM', 'May 8 2012 8:50AM', 'Ninth Run'

-- The ChecklistID column in table #mytableB corresponds to the ID column of #mytableA

-- These results of the following two queries are the ones I am trying to combine into one

-- This query returns the rows for a particular checklist, in this example 3 rows.

-- SELECT * FROM #myTableA WHERE ChecklistID = 535

-- These queries return the last row for all the entries in #myTableB that correspond to a particular row of #myTableA

-- SELECT TOP 1 * FROM #myTableB WHERE ChecklistStepID = 1 ORDER BY ID DESC

-- SELECT TOP 1 * FROM #myTableB WHERE ChecklistStepID = 2 ORDER BY ID DESC

-- SELECT TOP 1 * FROM #myTableB WHERE ChecklistStepID = 3 ORDER BY ID DESC

-- This combined query is close to what I want, but it returns a row for every entry in #myTableB instead of just the last one.

-- For instance, instead of 3 rows returned for ID 1 of #myTableA, it should just be the last one with a Notes column of 'Third Run'

select * from #myTableA as TA left join #myTableB as TB on TB.ChecklistStepID = TA.ID where TA.ChecklistID = 535

-- Here is what I need to have returned for a query for a specific #myTableA.ChecklistID, in this case 535

IF OBJECT_ID('TempDB..#mytableC','U') IS NOT NULL

DROP TABLE #mytableC

CREATE TABLE #myTableC(

[ID] [int] NULL,

[ChecklistStepDefinitionID] [int] NULL,

[Status] [int] NULL,

[ChecklistID] [int] NULL,

[PlannedUser] [nvarchar](128) NULL,

[TableBID] [int] NULL,

[ChecklistStepID] [int] NULL,

[StartTime] [datetime] NULL,

[EndTime] [datetime] NULL,

[Notes] [nvarchar](1024) NULL,

)

GO

INSERT INTO #myTableC

(ID

,ChecklistStepDefinitionID

,Status

,ChecklistID

,PlannedUser

,TableBID

,ChecklistStepID

,StartTime

,EndTime

,Notes)

SELECT '1', '702', '0', '535', 'Brad', '3', '1', 'May 5 2012 7:00 AM', 'May 5 2012 7:10AM', 'Third Run' UNION ALL

SELECT '2', '703', '1', '535', 'Tim', null, null, null, null, null UNION ALL

SELECT '3', '704', '2', '535', 'Bob', '5', '3', 'May 6 2012 9:00 AM', 'May 6 2012 9:40AM', 'Fifth Run'

SELECT * FROM #myTableC

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 5

Does this help?

SELECT *

FROM

#myTableA AS TA

LEFT JOIN(SELECT

*

,ROW_NUMBER() OVER (PARTITION BY ChecklistStepID ORDER BY EndTime DESC) AS RowNum

FROM

#myTableB

) AS TB

ON TA.ID = TB.ChecklistStepID AND TB.RowNum = 1

WHERE TA.ChecklistID = 535

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

bherman-628226 Valued Member Points: 54 More actions · Answer 6

That works. The Start/End times are really irrelavant, it needed to return the last one inserted in #myTableB because they could be back dating entries, so I changed the ORDER BY to use ID instead of EndTime and added a "WHERE TA.ChecklistID = 535" to the end and it worked perfectly.

I learned something and I now can add it to my bag of tricks. I can go home now, right?

Thanks for the assistance!!

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 7

I can go home now, right?

Yep off you pop then 😀

Glad to help 🙂

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe