Join on one row only - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join on one row only

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-27 : 09:50:47

Have following query:

select           goLabor30.dbo.Batch.Name as name, 
                 goLabor30.dbo.Batch.ReportDate as reportdate,
                 goLabor30.dbo.Region.CompanyRegionId as company,
                 goLabor30.dbo.Employee.Name as batchowner,
                 goLabor30.dbo.EventStatusType.Name as batchstatus,
                 CASE 
                      WHEN dbo.EventStatusType.KeyName = 'sync' THEN 'Level 1'
                      WHEN dbo.EventStatusType.KeyName = 'approval' and dbo.EventApprovalLevel.Level = 1 THEN 'Level 2'
                      WHEN dbo.EventStatusType.KeyName = 'backofficeadd' THEN 'Rollback' 
                      ELSE Null                
                 end as "approvalneeded",
                 CASE 
                      WHEN dbo.EventType.EventTypeGuid is not Null THEN 'Y' ELSE 'N'
                 end as "employee",
                 CASE 
                      WHEN dbo.EventType.EventTypeGuid is not Null and dbo.EventType.Name = 'Equipment Labor' THEN 'Y' ELSE 'N'
                 end as "equipment",
                 CASE 
                      WHEN dbo.EventType.EventTypeGuid is not Null and dbo.EventType.Name = 'Production' THEN 'Y' ELSE 'N'
                 end as "production",
                 goLabor30.dbo.Job.CompanyJobID as jobname 
from             goLabor30.dbo.Batch 
                       inner join dbo.Region ON dbo.Region.RegionGuid = dbo.Batch.RegionGuid
                       inner join dbo.Account ON dbo.Account.AccountGuid = dbo.Batch.AccountGuid
                       inner join dbo.Employee ON dbo.Employee.EmployeeGuid = dbo.Account.EmployeeGuid
                       inner join dbo.Event ON dbo.Event.BatchGuid = dbo.Batch.BatchGuid                       inner join dbo.Job ON dbo.Job.Jobguid = dbo.Event.JobGuid
                       inner join dbo.EventStatusType ON dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                       inner join dbo.EventApprovalLevel ON dbo.EventApprovalLevel.EventApprovalLevelGuid = dbo.Event.EventApprovalLevelGuid
                       left join dbo.EventType ON dbo.EventType.EventTypeGuid = dbo.Event.EventTypeGuid
where          goLabor30.dbo.Region.CompanyRegionID = '00032' and
               goLabor30.dbo.Batch.Reportdate >=  '2009-08-24'and
               goLabor30.dbo.Batch.ReportDate <= '2009-08-26' and
               goLabor30.dbo.EventStatusType.Name <> 'Loaded to ERP'
order by       goLabor30.dbo.Batch.ReportDate, goLabor30.dbo.Batch.Name

Result is:

Name                            ReportDate      Company         Batchowner
RGGTRHA-392 TUE 8/25/09  	2009-08-25 	00032	    	Angel, Richard H.                       
RGGTWKH-385 TUE 8/25/09	      	2009-08-25 	00032    	Hamilton, William K.                    
RGGTWKH-385 TUE 8/25/09		2009-08-25 	00032		Hamilton, William K.                    
RGWPAWB-474 TUE 8/25/09		2009-08-25 	00032		Bolin, Arthur W.                        
RGWPAWB-474 TUE 8/25/09		2009-08-25 	00032		Bolin, Arthur W.

I need a single row only returned for Name (in above example there are multiple with same name). The reason for this is that in the "inner join dbo.Event ON dbo.Event.BatchGuid = dbo.Batch.BatchGuid" there are multiple row in the Event table. How can I achieve just to get first row returned? Thank you.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-27 : 09:56:01

add "distinct" after "select "

An infinite universe is the ultimate cartesian product.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-27 : 10:02:18

I did that:

select           distinct
                 goLabor30.dbo.Batch.Name as name, 
                 goLabor30.dbo.Batch.ReportDate as reportdate,
                 goLabor30.dbo.Region.CompanyRegionId as company,
                 goLabor30.dbo.Employee.Name as batchowner........

Returns multiple records with same name

RGFPFR-497 MON 8/24/09		2009-08-24 32	Reis, Fernando                          
RGFPFR-497 MON 8/24/09		2009-08-24 32	Reis, Fernando                          
RGFPFR-497 MON 8/24/09		2009-08-24 32	Reis, Fernando                          
RGFPJDS-369 MON 8/24/09		2009-08-24 32	Smith, Jimmy D.                         
RGFPKLD-456 MON 8/24/09		2009-08-24 32	Durham, Kenneth L.                      
RGFPLER-304 MON 8/24/09		2009-08-24 32	Roberts, Larry E.                       
RGFPLER-304 MON 8/24/09		2009-08-24 32	Roberts, Larry E.                       
RGFPLER-304 MON 8/24/09		2009-08-24 32	Roberts, Larry E.

I suspect I need a sub query but not sure how to put it together.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 11:03:45

i think you've posted only part of the result as using distinct wont give you duplicates if you only include the columns shown.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-27 : 11:40:52

[code]select distinct
goLabor30.dbo.Batch.Name as name,
goLabor30.dbo.Batch.ReportDate as reportdate,
goLabor30.dbo.Region.CompanyRegionId as company,
goLabor30.dbo.Employee.Name as batchowner,
goLabor30.dbo.EventStatusType.Name as batchstatus,
CASE
WHEN dbo.EventStatusType.KeyName = 'sync' THEN 'Level 1'
WHEN dbo.EventStatusType.KeyName = 'approval' and dbo.EventApprovalLevel.Level = 1 THEN 'Level 2'
WHEN dbo.EventStatusType.KeyName = 'backofficeadd' THEN 'Rollback'
ELSE Null
end as "approvalneeded",
CASE
WHEN dbo.EventType.EventTypeGuid is not Null THEN 'Y' ELSE 'N'
end as "employee",
CASE
WHEN dbo.EventType.EventTypeGuid is not Null and dbo.EventType.Name = 'Equipment Labor' THEN 'Y' ELSE 'N'
end as "equipment",
CASE
WHEN dbo.EventType.EventTypeGuid is not Null and dbo.EventType.Name = 'Production' THEN 'Y' ELSE 'N'
end as "production",
goLabor30.dbo.Job.CompanyJobID as jobname
from goLabor30.dbo.Batch
inner join dbo.Region ON dbo.Region.RegionGuid = dbo.Batch.RegionGuid
inner join dbo.Account ON dbo.Account.AccountGuid = dbo.Batch.AccountGuid
inner join dbo.Employee ON dbo.Employee.EmployeeGuid = dbo.Account.EmployeeGuid
inner join dbo.Event ON dbo.Event.BatchGuid = dbo.Batch.BatchGuid
inner join dbo.Job ON dbo.Job.Jobguid = dbo.Event.JobGuid
inner join dbo.EventStatusType ON dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join dbo.EventApprovalLevel ON dbo.EventApprovalLevel.EventApprovalLevelGuid = dbo.Event.EventApprovalLevelGuid
left join dbo.EventType ON dbo.EventType.EventTypeGuid = dbo.Event.EventTypeGuid
where goLabor30.dbo.Region.CompanyRegionID = '00032' and
goLabor30.dbo.Batch.Reportdate >= '2009-08-24'and
goLabor30.dbo.Batch.ReportDate <= '2009-08-26' and
goLabor30.dbo.EventStatusType.Name <> 'Loaded to ERP'
order by goLabor30.dbo.Batch.ReportDate, goLabor30.dbo.Batch.Name[/code]

Result:

[code]RGFPFR-497 MON 8/24/09 2009-08-24 00032 Reis, Fernando
RGFPFR-497 MON 8/24/09 2009-08-24 00032 Reis, Fernando
RGFPFR-497 MON 8/24/09 2009-08-24 00032 Reis, Fernando
RGFPJDS-369 MON 8/24/09 2009-08-24 00032 Smith, Jimmy D.
RGFPKLD-456 MON 8/24/09 2009-08-24 00032 Durham, Kenneth L.
RGFPLER-304 MON 8/24/09 2009-08-24 00032 Roberts, Larry E.
RGFPLER-304 MON 8/24/09 2009-08-24 00032 Roberts, Larry E.
RGFPLER-304 MON 8/24/09 2009-08-24 00032 Roberts, Larry E. [/code]

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-08-27 : 11:46:23

I also tried this, still giving duplicates:

select           distinct
                 goLabor30.dbo.Batch.ReportDate as batchreportdate,
                 goLabor30.dbo.Batch.Name as batchname, 
                 goLabor30.dbo.Region.CompanyRegionId as batchcompany,
                 goLabor30.dbo.Employee.Name as batchowner,
                 batchstatus,
                 approvalneeded,
                 employee,
                 equipment,
                 jobname
from             goLabor30.dbo.Batch 
                       inner join dbo.Region ON dbo.Region.RegionGuid = dbo.Batch.RegionGuid
                       inner join dbo.Account ON dbo.Account.AccountGuid = dbo.Batch.AccountGuid
                       inner join dbo.Employee ON dbo.Employee.EmployeeGuid = dbo.Account.EmployeeGuid
left join   (      
                       select    e.BatchGuid,
                                 goLabor30.dbo.EventStatusType.Name as batchstatus,
                                 CASE 
                                   WHEN dbo.EventStatusType.KeyName = 'sync' THEN 'Level 1'
                                   WHEN dbo.EventStatusType.KeyName = 'approval' and dbo.EventApprovalLevel.Level = 1 THEN 'Level 2'
                                   WHEN dbo.EventStatusType.KeyName = 'backofficeadd' THEN 'Rollback' 
                                 ELSE Null                
                                 end as "approvalneeded",
                                 CASE 
                                   WHEN dbo.EventType.EventTypeGuid is not Null THEN 'Y' ELSE 'N'
                                 end as "employee",
                                 CASE 
                                   WHEN dbo.EventType.EventTypeGuid is not Null and dbo.EventType.Name = 'Equipment Labor' THEN 'Y' ELSE 'N'
                                 end as "equipment",
                                 CASE 
                                   WHEN dbo.EventType.EventTypeGuid is not Null and dbo.EventType.Name = 'Production' THEN 'Y' ELSE 'N'
                                 end as "production",
                                 goLabor30.dbo.Job.CompanyJobID as jobname 
                       from dbo.Event as e
                       inner join dbo.Job ON dbo.Job.Jobguid = e.JobGuid
                       inner join dbo.EventStatusType ON dbo.EventStatusType.EventStatusTypeGuid = e.EventStatus
                       inner join dbo.EventApprovalLevel ON dbo.EventApprovalLevel.EventApprovalLevelGuid = e.EventApprovalLevelGuid
                       left join dbo.EventType ON dbo.EventType.EventTypeGuid = e.EventTypeGuid
where                  goLabor30.dbo.EventStatusType.Name <> 'Loaded to ERP'
             ) as e on e.BatchGuid = dbo.Batch.BatchGuid
where          goLabor30.dbo.Region.CompanyRegionID = '00032' and
               goLabor30.dbo.Batch.Reportdate >=  '2009-08-24'and
               goLabor30.dbo.Batch.ReportDate <= '2009-08-26' 
order by       goLabor30.dbo.Batch.Name

Result:

RGFPFR-497 MON 8/24/09		2009-08-24 00032	Reis, Fernando                          
RGFPFR-497 MON 8/24/09		2009-08-24 00032	Reis, Fernando                          
RGFPFR-497 MON 8/24/09		2009-08-24 00032	Reis, Fernando                          
RGFPJDS-369 MON 8/24/09		2009-08-24 00032	Smith, Jimmy D.                         
RGFPKLD-456 MON 8/24/09		2009-08-24 00032	Durham, Kenneth L.                      
RGFPLER-304 MON 8/24/09		2009-08-24 00032	Roberts, Larry E.                       
RGFPLER-304 MON 8/24/09		2009-08-24 00032	Roberts, Larry E.                       
RGFPLER-304 MON 8/24/09		2009-08-24 00032	Roberts, Larry E.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 12:00:13

try using LTRIM(RTRIM()) over all the character data columns to remove any unwanted spaces

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources