Cross Join followed by Left Join

Question

Is it possible to do a CROSS JOIN between 2 tables, followed by a LEFT JOIN on to a 3rd table, followed by possibly more left joins? I am using SQL Server 2000/2005.

I am running the following query, which is pretty straightForward IMO, but I am getting an error.

select  P.PeriodID,
        P.PeriodQuarter,
        P.PeriodYear,
        M.Name,
        M.AuditTypeId,
        A.AuditId
from Period P, Member M

LEFT JOIN Audits A 
ON P.PeriodId = A.PeriodId

WHERE 
    P.PeriodID > 29 AND P.PeriodID < 38
    AND M.AuditTypeId in (1,2,3,4)
order by M.Name

I am getting the following error:

Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "P.PeriodId" could not be bound.

If I remove the LEFT JOIN, the query works. However, I need the LEFT JOIN, as there is more information that I need to pull from other tables.

What am I doing wrong? Is there a better way to this?

score 6 · Answer 1 · answered Jul 12 '10 at 14:00

6

you forgot CROSS JOIN in your query:

select  P.PeriodID,
        P.PeriodQuarter,
        P.PeriodYear,
        M.Name,
        M.AuditTypeId,
        A.AuditId
from Period P CROSS JOIN Member M

LEFT JOIN Audits A 
ON P.PeriodId = A.PeriodId

WHERE 
    P.PeriodID > 29 AND P.PeriodID < 38
    AND M.AuditTypeId in (1,2,3,4)
order by M.Name

answered Jul 12 '10 at 14:00

A-K

16,804
8
54
74

I don't need to specify it, AFAIK. The query works fine if I remove the LEFT JOIN on to the Audits table. From what I've read, my OP defines an implicit cross join. – Saajid Ismail Jul 12 '10 at 14:19
OK so I tried your code, and it works now.. But why? It doesn't make sense. – Saajid Ismail Jul 12 '10 at 14:21
@Saajid: SQL Tends to look at entire clauses at once. For example you can't says `select a+b as c, 2*c as double_c` Because the `c` is not defined when the select clause starts. The same thing happens when mixing old and new style joins. The pieces between commas in the from clause are each looked at, as if in parellel. So the P defined on the left side of the comma is not seen on the right. See http://stackoverflow.com/questions/1080097/the-multi-part-identifier-could-not-be-bound-on-sql-server-2005-8/1080407#1080407 for another example. – Shannon Severance Jul 12 '10 at 20:54
1

Note: You could reverse P & M in the from clause, but an explicit cross join is better. We know the intent was to create a cross join, it's not a mistaken old style inner join. – Shannon Severance Jul 12 '10 at 20:55
This answer is not correct. The syntax above is correct and still works in some databases. This doesn't directly answer the question. As of Aug. 2022 I'm in the process of migrating code from MSSQL 2005 Syntax to MSSSQL 2019. The implicit join syntax is still valid, but some of the operators available back then are not available now. I was searching for an answer to the OP's questions today and the answer with "You cannot combine implicit and explicit joins." is the answer I was looking for. – Jason Aug 09 '22 at 15:44

score 3 · Accepted Answer · edited Aug 29 '17 at 14:58

3

You cannot combine implicit and explicit joins - see this running example.

CROSS JOINs should be so infrequently used in a system, that I would want every one to be explicit to ensure that it is clearly not a coding error or design mistake.

If you want to do an implicit left outer join, do this (not supported on SQL Azure):

select  P.PeriodID,
        P.PeriodQuarter,
        P.PeriodYear,
        M.Name,
        M.AuditTypeId,
        A.AuditId
from #Period P, #Member M, #Audits A 
WHERE 
    P.PeriodID > 29 AND P.PeriodID < 38
    AND M.AuditTypeId in (1,2,3,4)
    AND P.PeriodId *= A.PeriodId
order by M.Name

edited Aug 29 '17 at 14:58

Cœur

37,241
25
195
267

answered Jul 12 '10 at 18:29

Cade Roux

88,164
40
182
265

Thnx. This makes things clearer to me now. Doing a cross join is the only way that I can think of to solve my particular problem. What does the '*=' operator do though? – Saajid Ismail Jul 13 '10 at 07:43
@Saajid Imsail *= is the old style LEFT JOIN operator for implicit joins. – Cade Roux Jul 13 '10 at 13:56
That operator is unreliable and deprecated, the correct fix is to use explicit joins. There is no excuse for not using explicit joins in any query. – HLGEM Sep 02 '11 at 17:25
One can combine implicit & explicit joins, comma just has lower precedence than keyword joins, and it is not deprecated. *= style outer joins are deprecated. PS Every JOIN ON involves a cross join semantically. So it is reasonable to say we cross join all the time. We just seldom cross join without (immediately) wanting to filter it via ON or WHERE. – philipxy Sep 26 '19 at 18:39

Cross Join followed by Left Join

2 Answers2

Linked