sql server issue with inner join

Question

I currenlty have this code

SELECT JOB.JobID, A.adID, AR.ARNum
FROM dbo.vEvent AS E, dbo.[vAd] as A, dbo.[vAR] as AR, dbo.JOB_EVENT as JOB
INNER JOIN A ON A.eventID = E.eventID
INNER JOIN AR ON AR.eventID = A.eventID
INNER JOIN JOB ON JOB.EVENT_ID = E.eventID
WHERE year = '' AND season = '' AND month = ''

however; when I execute it in SQl server I get this error.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'A'.

I don't understand, because all of my variables look correct to me.

Any help would be appreciated.

Matt Whitfield · Accepted Answer · 2012-07-18 13:56:12Z

5

You want

SELECT JOB.JobID, A.adID, AR.ARNum
FROM dbo.vEvent AS E
INNER JOIN dbo.[vAd] as A ON A.eventID = E.eventID
INNER JOIN dbo.[vAR] as AR ON AR.eventID = A.eventID
INNER JOIN dbo.JOB_EVENT as JOB ON JOB.EVENT_ID = E.eventID
WHERE year = '' AND season = '' AND month = ''

answered Jul 18, 2012 at 13:56

Matt Whitfield

6,6343 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bluesky Over a year ago

I get these errors with that statement. Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "E.eventID" could not be bound. Msg 4104, Level 16, State 1, Line 5 The multi-part identifier "E.eventID" could not be bound. Msg 207, Level 16, State 1, Line 1 Invalid column name 'JobID'.

Matt Whitfield Over a year ago

Sorry removed the main table alias by accident. Edited - try now.

bluesky Over a year ago

what was the issue with my original query?

ruakh Over a year ago

@MasterP: The issue is simply that it's wrong. You can't write FROM ... AS a, ... AS b INNER JOIN b ON ...; you have to write either FROM ... AS a INNER JOIN ... AS b ON ... (modern syntax) or FROM ... AS a, ... AS b WHERE ... (ancient syntax).

Matt Whitfield Over a year ago

+1 to @ruakh - you basically had a mix of ANSI-89 and ANSI-92 syntax. But the ANSI-89 syntax (comma separated list of tables, joins via WHERE) is deprecated - so think table join table on condition join table on condition...

Collectives™ on Stack Overflow

sql server issue with inner join

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related