Msg 4104 using INNER JOINS - SQL SERVER 2012

Question

I have these tables:

I am trying to get the article_id, article_name, article_title, article_description and article_date from articles and username from users by using a connecting table, user_articles.

I have tried this code:

SELECT E.article_id, E.article_title, E.article_description, E.article_date, u.username
FROM articles AS E, users as u
INNER JOIN user_articles as A ON A.article_id = E.article_id
INNER JOIN users as u2 ON u2.user_id = A.user_id
WHERE E.article_status = 1

And I am getting this error message:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "E.article_id" could not be bound.

enter image description here

I am using SQL Server 2012 Express.

Possible duplicate of [The multi-part identifier could not be bound](https://stackoverflow.com/questions/7314134/the-multi-part-identifier-could-not-be-bound) — philipxy, Sep 26 '19 at 18:46

score 2 · Accepted Answer · answered Apr 21 '13 at 00:06

2

You can not select from one table and apply a join on the other. Try this query:

SELECT E.article_id, E.article_title, E.article_description, E.article_date, u.username
FROM articles AS E
    INNER JOIN user_articles as A ON A.article_id = E.article_id
    INNER JOIN users as u ON u.user_id = A.user_id
WHERE E.article_status = 1

answered Apr 21 '13 at 00:06

Linus Caldwell

10,908
12
46
58

That works, however, if there are 5 inputs with article_status 1, only 1 is being displayed – Brian Apr 21 '13 at 00:26
Are all of these articles associated with users? If not, you could use `left outer join`, but then you'll get `null` for `u.username` of course. – Linus Caldwell Apr 21 '13 at 00:29
yes, they are associated by the `user_articles` table with `user_id` and `article_id` – Brian Apr 21 '13 at 00:30
What does "select from one table and apply a join on the other" mean? The problem in the question is that comma has lower precedence than keyword joins. – philipxy Sep 26 '19 at 18:44

score 2 · Answer 2 · answered Apr 21 '13 at 00:06

The problem is that you are mixing JOIN types, you have both comma separated implicit JOINs and then explicit JOINs. Your code should be:

SELECT E.article_id, 
  E.article_title, 
  E.article_description, 
  E.article_date, 
  u.username
FROM articles AS E
INNER JOIN user_articles as A 
  ON A.article_id = E.article_id
INNER JOIN users as u
  ON A.user_id = u.user_id
WHERE E.article_status = 1;

score 0 · Answer 3 · answered Apr 21 '13 at 00:42

0

im thinking why do u even need such a table as : users_articles ? wouldnt it be more efficient to add a column named (article_author) to the articles table which is populated with the id of the user ?? then u can inner join on that value.just saying

answered Apr 21 '13 at 00:42

Hussam Sibai

97
2
9

Msg 4104 using INNER JOINS - SQL SERVER 2012

3 Answers3