LEFT JOIN in MySQL query

Question

I have two database tables, one is named "articles", and the other one is "users"

the structure of articles is as follows:

article_id    article_title    user_id

The structure of users is:

user_id    user_fullname     password

I wish to retrieve a list of all articles from the table "articles", but would like to attach each article's user_fullname. I think this may require "LEFT JOIN", so I made the following attempt in MySQL prompt.

> SELECT * FROM articles A LEFT JOIN users U on U.user_id = A.user_id;

but somehow I don't see the user_fullname printed out with this command. I need some help with the correct syntax. Thank you!

You only need a left join if there can be rows in the first table that have no matching row in the second table. Can an article really have a user_id that isn't in the users table? — Barmar
– Barmar, Commented Jun 12, 2014 at 21:06
But either way, the query you wrote should have worked. Can you make a sqlfiddle that demonstrates the problem? — Barmar
– Barmar, Commented Jun 12, 2014 at 21:07
We can assume "articles" contain user_id's that are always available in users table. — TonyW
– TonyW, Commented Jun 12, 2014 at 21:16
If you want help with this, you need to provide an example of the failure. What you wrote should work, even though it's not the optimal way to write it. — Barmar
– Barmar, Commented Jun 12, 2014 at 21:17

Hunter McMillen · Accepted Answer · 2014-06-12 21:04:39Z

3

A full join seems more appropriate, each article must have an author, so there should be a corresponding entry in the users table:

SELECT *
FROM articles a 
   JOIN users u USING(user_id);

Note: USING here is the same as ON a.user_id = b.user_id and can only be used if the column name is the same in both tables.

answered Jun 12, 2014 at 21:04

Hunter McMillen

61.8k25 gold badges124 silver badges176 bronze badges

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

6 Comments

Christian Gollhardt Over a year ago

Thanks for clarifying the using. Did have had a goole search for it, before i see the updated answer. This link explain it good: lornajane.net/posts/2012/sql-joins-with-on-or-using

Barmar Over a year ago

While an inner join may be better, a left join should still work. This answer doesn't fix whatever problem he had with the original query.

Hunter McMillen Over a year ago

The left join should work, but I would argue that it is incorrect. I couldn't re-create the issue they were having.

TonyW Over a year ago

A further question: if I want to retrieve a single article from table "articles" with a given article_id, I would also like to retrieve its author from the table "users". I did this: SELECT * FROM articles A WHERE A.article_id=1 JOIN users U ON U.user_id = A.user_id, but it didn't work

Hunter McMillen Over a year ago

You would need to add a WHERE clause that limits the result set to that article_id

|

Collectives™ on Stack Overflow

LEFT JOIN in MySQL query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related