Join problem 1

[NotSQL]

Hi, Im having problems joining these two queries. Can anyone push me in the right direction to get this to work.

The two queries are:

SELECT RegNo, ChassisNo, VehicleModel, CabType, BodyType, AncilliaryEquipment, StartDate,
ContractTermsMonths, EndDate, DateOfRegistration, RegistrationLetterCAP, RegistrationYearCAP,
CAPIDNo, UVCModel, AgreedResidualValue
From VehicleBuyBack
where ChassisNo = 'WMAL20ZZZ2Y093510'

and

select ChassisNo, CAPCleanCurrent,CAPCleanAtTermination
from FinancialValues
where ChassisNo = 'WMAL20ZZZ2Y093510' and financialYearMonth in (select max(financialYearMonth) from FinancialValues)
and CAPValueType = 'C'

These both produce a single record, however when i join them together i am getting duplicates...


Any help would be appreciated...

 

[NotSQL]

I should have mentioned that i need to join both these using the CAPID and ChassisNo

 

[bborissov]

SELECT *
FROM (SELECT RegNo, ChassisNo, VehicleModel, CabType,
             BodyType, AncilliaryEquipment, StartDate,
             ContractTermsMonths, EndDate,
             DateOfRegistration, RegistrationLetterCAP,
             RegistrationYearCAP,
             CAPIDNo, UVCModel,  AgreedResidualValue
        From VehicleBuyBack
        where ChassisNo = 'WMAL20ZZZ2Y093510') Tbl1
INNER JOIN (select ChassisNo,CAPCleanCurrent,
                   CAPCleanAtTermination
            from FinancialValues
            where ChassisNo = 'WMAL20ZZZ2Y093510' and
                  financialYearMonth in (select
                   max(financialYearMonth)
                  from FinancialValues)
              and CAPValueType = 'C') Tbl2
ON Tbl1.ChassisNo = Tbl2.ChassisNo AND
   Tbl1.CAPIDNo   = Tbl2.???? -- I couldn't find the matching field

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[NotSQL]

Thanks bborissov,

However is there a better way of writting this as it takes over 30 seconds to run...

 

[bborissov]

There is always better way, but i don't know your database. Don't know what indexes you have.
See the execution plan.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[NotSQL]

Thanks bborissov,

Would you take some time in explaining how the execution plan works. This is the first time i've used it.

Thanks

 

[MarkXaml]

How about something like this:

SELECT TOP 1		vbb.RegNo, 
					vbb.ChassisNo, 
					vbb.VehicleModel, 
					vbb.CabType, 
					vbb.BodyType, 
					vbb.AncilliaryEquipment, 
					vbb.StartDate, 
					vbb.ContractTermsMonths, 
					vbb.EndDate, 
					vbb.DateOfRegistration, 
					vbb.RegistrationLetterCAP, 
					vbb.RegistrationYearCAP,
					vbb.CAPIDNo, 
					vbb.UVCModel,  
					vbb.AgreedResidualValue, 
					fv.CAPCleanCurrent, 
					fv.CAPCleanAtTermination,
FROM				VehicleBuyBack vbb (NOLOCK)
INNER JOIN			FinancialValues  fv (NOLOCK)
ON					vbb.CAPIDNo = fv.CAPIDNo
AND					vbb.ChassisNo = fv.ChassisNo
WHERE				vbb.ChassisNo = 'WMAL20ZZZ2Y093510'
AND					fv.CAPValueType = 'C'
ORDER BY			fv.financialYearMonth DESC

 

[ca8msm]

Thanks bborissov,

Would you take some time in explaining how the execution plan works. This is the first time i've used it.

Thanks

You should take some time to read up on it if you are not sure what it does:

[google]SQL Server execution plan[/google]


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[MarkXaml]

Sorry, I was multi-tasking again and missed a line...

SELECT TOP 1		vbb.RegNo, 
					vbb.ChassisNo, 
					vbb.VehicleModel, 
					vbb.CabType, 
					vbb.BodyType, 
					vbb.AncilliaryEquipment, 
					vbb.StartDate, 
					vbb.ContractTermsMonths, 
					vbb.EndDate, 
					vbb.DateOfRegistration, 
					vbb.RegistrationLetterCAP, 
					vbb.RegistrationYearCAP,
					vbb.CAPIDNo, 
					vbb.UVCModel,  
					vbb.AgreedResidualValue, 
					fv.CAPCleanCurrent, 
					fv.CAPCleanAtTermination,
					fv.CAPValueType,
					fv.financialYearMonth
FROM				VehicleBuyBack vbb (NOLOCK)
INNER JOIN			FinancialValues  fv (NOLOCK)
ON					vbb.CAPIDNo = fv.CAPIDNo
AND					vbb.ChassisNo = fv.ChassisNo
WHERE				vbb.ChassisNo = 'WMAL20ZZZ2Y093510'
AND					fv.CAPValueType = 'C'
ORDER BY			fv.financialYearMonth DESC

 

[NotSQL]

Thanks for your help guys. After looking into the execution plan and implementing certain features I’ve managed to cut the run time of my query by over 45 seconds...