Additional join - 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)

Additional join

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-12-17 : 08:58:03

Hi,

I've got this


USE PUB_CAR

SELECT 
  CAPVehicles.CVehicle_ManText, CAPVehicles.CVehicle_ModText, CAPVehicles.CVehicle_ShortModText, 
  CAPVehicles.CVehicle_DerText,NVDPrices.PR_EffectiveTo, 
  NVDPrices.PR_Basic, NVDPrices.PR_Vat, NVDPrices.PR_Delivery, 
  NVDTechnical.TECH_TechCode, NVDTechnical.TECH_EffectiveTo, NVDTechnical.TECH_Value_String, 
  NVDPrices.PR_Basic + NVDPrices.PR_Vat + NVDPrices.PR_Delivery AS BIK, 
  CapDer.cder_doors, CapDer.cder_fueltype, CapDer.cder_transmission 
FROM 
  CAPVehicles 
LEFT OUTER JOIN NVDPrices 
  ON CAPVehicles.CVehicle_ID = NVDPrices.PR_Id 
LEFT OUTER JOIN CapDer 
  ON CAPVehicles.CVehicle_ID = CapDer.cder_ID 
LEFT OUTER JOIN NVDTechnical 
  ON CAPVehicles.CVehicle_ID = NVDTechnical.TECH_Id 
WHERE 
 (NVDPrices.PR_EffectiveTo IS NULL) AND 
 (NVDTechnical.TECH_TechCode = 67) AND 
 (NVDTechnical.TECH_EffectiveTo IS NULL) AND 
 cvehicle_id = 24467

I need to also join NVDTechnical.TECH_TechCode, NVDTechnical.TECH_EffectiveTo, NVDTechnical.TECH_Value_String a further time but where NVDTechnical.TECH_TechCode = 20

I tried and tried but cant seem to get the syntax correct.

Thanks

(added line breaks to code - Jeff)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 09:07:04

Can you give an overview of what output you are trying to get here?Also can i ask to put the query in more ligible manner so that its easier to read. cheers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 09:08:57

[code]USE PUB_CAR

SELECT v.CVehicle_ManText,
v.CVehicle_ModText,
v.CVehicle_ShortModText,
v.CVehicle_DerText,
p.PR_EffectiveTo,
p.PR_Basic,
p.PR_Vat,
p.PR_Delivery,
t.TECH_TechCode,
t.TECH_EffectiveTo,
t.TECH_Value_String,
COALESCE(p.PR_Basic, 0) + COALESCE(p.PR_Vat, 0) + COALESCE(p.PR_Delivery, 0) AS BIK,
cd.cder_doors,
cd.cder_fueltype,
cd.cder_transmission
FROM CAPVehicles AS v
LEFT JOIN NVDPrices AS p ON p.PR_Id = v.CVehicle_ID
LEFT JOIN CapDer AS cd ON cd.cder_ID = v.CVehicle_ID
LEFT JOIN NVDTechnical AS t ON t.TECH_Id = v.CVehicle_ID
AND t.TECH_TechCode = 67
WHERE v.cvehicle_id = 24467
AND p.PR_EffectiveTo IS NULL
AND t.TECH_EffectiveTo IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 09:10:11

I don't think the two last statements in the WHERE clause are necessary (the two IS NULL).

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 09:15:18

Try this

USE PUB_CAR
SELECT CAPVehicles.CVehicle_ManText,
CAPVehicles.CVehicle_ModText,
CAPVehicles.CVehicle_ShortModText,
CAPVehicles.CVehicle_DerText,
NVDPrices.PR_EffectiveTo,
NVDPrices.PR_Basic,
NVDPrices.PR_Vat,
NVDPrices.PR_Delivery,
NVDTechnical.TECH_TechCode,
NVDTechnical.TECH_EffectiveTo,
NVDTechnical.TECH_Value_String,
NVDPrices.PR_Basic + NVDPrices.PR_Vat + NVDPrices.PR_Delivery AS BIK,
CapDer.cder_doors,
CapDer.cder_fueltype,
CapDer.cder_transmission,
t.TECH_TechCode,
t.TECH_EffectiveTo,
t.TECH_Value_String
FROM CAPVehicles
LEFT OUTER JOIN NVDPrices
ON CAPVehicles.CVehicle_ID = NVDPrices.PR_Id
LEFT OUTER JOIN CapDer
ON CAPVehicles.CVehicle_ID = CapDer.cder_ID
LEFT OUTER JOIN NVDTechnical
ON CAPVehicles.CVehicle_ID = NVDTechnical.TECH_Id
AND NVDTechnical.TECH_TechCode = 67
LEFT OUTER JOIN
(SELECT NVDTechnical.TECH_TechCode, NVDTechnical.TECH_EffectiveTo, NVDTechnical.TECH_Value_String
FROM NVDTechnical
WHERE NVDTechnical.TECH_TechCode = 20
)t
ON CAPVehicles.CVehicle_ID = t.TECH_Id
WHERE (NVDPrices.PR_EffectiveTo IS NULL) AND (NVDTechnical.TECH_EffectiveTo IS NULL) AND cvehicle_id = 24467

What are trying to get here?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources