Question about LEFT 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)

Question about LEFT JOIN

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-09-22 : 18:31:53

Hi,

I have this

SELECT COUNT(*) AS Expr1
FROM vwAllMatrixWithLombardAndShortModel
WHERE (Type = 'commercial')

It returns 396 records

then I have this

SELECT count(*)
FROM vwAllMatrixWithLombardAndShortModel AS main LEFT OUTER JOIN PUB_LIGHTS.dbo.CapDer AS der
ON main.CapID = der.cder_ID
LEFT OUTER JOIN PUB_LIGHTS.dbo.NVDTechnical AS co ON main.CapID = co.TECH_Id AND main.type = 'commercial'
WHERE (co.TECH_EffectiveTo IS NULL) AND (co.TECH_TechCode = 67)

But that only returns 360 records

My understanding of LEFT JOIN was that I would return all records from the "main" table in this case regardless of if a match was found in the other tables.

Could someone explain

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-22 : 18:34:20

Because you are counting records with count(*). Specify columns from main table.

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-09-22 : 18:40:33

If I change it to SELECT COUNT(main.vehicleref) on both queries the counts are still different - 396 to 360. I dont understand why?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-22 : 19:54:26

[code]
SELECT count(*)
FROM vwAllMatrixWithLombardAndShortModel AS main LEFT OUTER JOIN PUB_LIGHTS.dbo.CapDer AS der
ON main.CapID = der.cder_ID
LEFT OUTER JOIN PUB_LIGHTS.dbo.NVDTechnical AS co ON main.CapID = co.TECH_Id AND main.type = 'commercial'
~~WHERE~~ AND (co.TECH_EffectiveTo IS NULL) AND (co.TECH_TechCode = 67)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources