Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select and syb select - need help

Status
Not open for further replies.
snufse1

snufse1

Programmer
Nov 14, 2008
66
US
I need to do the select based upon the content (equipmentID) in #TempTable1 and bring the result into #TempTable2. I am not sure how to do the join. Can anyone help?


CREATE TABLE #TempTable1 (equipmentID1 nvarchar(20))


Fill #TempTable1 here .......

Now use records in #TempTable1 as basis for my select..... Tried below code, but gives me error: Incorrect syntax near the keyword 'on'.



INSERT INTO #TempTable2
SELECT equipmentID1
FROM #TempTable1
INNER JOIN
(SELECT
substring(dbo.Job.CompanyJobId, 1, 10) as job,
substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
dbo.Batch.ReportDate as jobdate,
dbo.Equipment.CompanyEquipmentID as equipmentID,
substring(dbo.Equipment.Name, 1, 20) as equipmentname,
dbo.EquipmentLaborEvent.Hours as hours,
substring(dbo.EventStatusType.Name, 1, 1) as event_code,
dbo.EventStatusType.Name as event_name,
substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
substring (e2.UserName, 1, 10) as approver

FROM dbo.Equipment)

on dbo.Equipment.CompanyEquipmentID = equipmentID1
inner join EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid
inner join dbo.Event on dbo.Event.EventGuid = EquipmentLaborEvent.EventGuid
inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
inner join dbo.Equipment e3 on dbo.Equipment.CompanyEquipmentID = #TempTable1.equipmentID

WHERE dbo.Region.CompanyRegionID = '00060' and
dbo.Job.CompanyJobID = @JobNumber and
dbo.Batch.Reportdate >= '2009-05-14' and
dbo.Batch.ReportDate <= '2009-05-29' and
 
Sort by date Sort by votes
Code: 
INSERT INTO #TempTable2
SELECT      equipmentID1
FROM        #TempTable1
            INNER JOIN
                  (SELECT         
                       substring(dbo.Job.CompanyJobId, 1, 10) as job,
                       substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
                       dbo.Batch.ReportDate as jobdate,
                       dbo.Equipment.CompanyEquipmentID as equipmentID,
                       substring(dbo.Equipment.Name, 1, 20) as equipmentname,
                       dbo.EquipmentLaborEvent.Hours as hours,
                       substring(dbo.EventStatusType.Name, 1, 1) as event_code,  
                       dbo.EventStatusType.Name as event_name,  
                       substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
                  
                       FROM dbo.Equipment) [!]As SomeAliasName[/!]

                 on dbo.Equipment.CompanyEquipmentID = [!]SomeAliasName.[/!]equipmentID1
         inner join EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
                 inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid                  
                 inner join dbo.Event on dbo.Event.EventGuid = EquipmentLaborEvent.EventGuid
                 inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                 inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
                 inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
                 inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
                 left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
                 inner join dbo.Equipment e3 on dbo.Equipment.CompanyEquipmentID = #TempTable1.equipmentID

WHERE            dbo.Region.CompanyRegionID = '00060' and
                 dbo.Job.CompanyJobID = @JobNumber and
                 dbo.Batch.Reportdate >=  '2009-05-14' and
                 dbo.Batch.ReportDate <= '2009-05-29' and

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Tried by getting errors:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.Job' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Item' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Batch' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EquipmentLaborEvent' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EventStatusType' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EventStatusType' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Batch' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Batch' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'e2' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Equipment' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Equipment' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Equipment' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Equipment' does not match with a table name or alias name used in the query.
 
Upvote 0 Downvote
I alaso tried this:

INSERT INTO #TempTable2
SELECT *
FROM #TempTable1
INNER JOIN
(SELECT
substring(dbo.Job.CompanyJobId, 1, 10) as job,
substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
dbo.Batch.ReportDate as jobdate,
dbo.Equipment.CompanyEquipmentID as equipmentID,
substring(dbo.Equipment.Name, 1, 20) as equipmentname,
dbo.EquipmentLaborEvent.Hours as hours,
substring(dbo.EventStatusType.Name, 1, 1) as event_code,
dbo.EventStatusType.Name as event_name,
substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
substring (e2.UserName, 1, 10) as approver
FROM dbo.Equipment


inner join EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid
inner join dbo.Event on dbo.Event.EventGuid = EquipmentLaborEvent.EventGuid
inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
inner join dbo.Equipment e3 on dbo.Equipment.CompanyEquipmentID = #TempTable1.equipmentID


WHERE dbo.Region.CompanyRegionID = '00060' and
dbo.Job.CompanyJobID = @JobNumber and
dbo.Batch.Reportdate >= '2009-05-14' and
dbo.Batch.ReportDate <= '2009-05-29' and
dbo.EquipmentLaborEvent.Hours > 0 ) as x

on x.EquipmentID = #Temptable1.equipmentID1

 
Upvote 0 Downvote
INSERT INTO #TempTable2
SELECT equipmentID1
FROM #TempTable1 T
INNER JOIN
(SELECT
substring(dbo.Job.CompanyJobId, 1, 10) as job,
substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
dbo.Batch.ReportDate as jobdate,
dbo.Equipment.CompanyEquipmentID as equipmentID,
substring(dbo.Equipment.Name, 1, 20) as equipmentname,
dbo.EquipmentLaborEvent.Hours as hours,
substring(dbo.EventStatusType.Name, 1, 1) as event_code,
dbo.EventStatusType.Name as event_name,
substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
substring (e2.UserName, 1, 10) as approver

FROM dbo.Equipment) T2

on T.CompanyEquipmentID = T2.equipmentID1
inner join EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid
inner join dbo.Event on dbo.Event.EventGuid = EquipmentLaborEvent.EventGuid
inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
inner join dbo.Equipment e3 on dbo.Equipment.CompanyEquipmentID = #TempTable1.equipmentID

WHERE dbo.Region.CompanyRegionID = '00060' and
dbo.Job.CompanyJobID = @JobNumber and
dbo.Batch.Reportdate >= '2009-05-14' and
dbo.Batch.ReportDate <= '2009-05-29' and


But actually your SQL is still not-readable - wrong. Give each table an alias, then it would be easy to understand all the joins.





 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
764
SashaBuilder3
SashaBuilder3
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
665
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
466
grnzbra
grnzbra
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
521
f0rg3tfu1
f0rg3tfu1
Auguy
  • Locked
  • Question
Using UDF In Select, Bad Idea? 2
Replies
3
Views
293
Auguy
Auguy

Part and Inventory Search

Sponsor

Back
Top