MySQL Join Syntax

Question

I'm having problems understanding the MYSQL join syntax. I can't seem to manipulate it for my purpose. I have had to use work arounds for months which have resulted in extra queries just for retrieving one extra column.

Scenario: I have two tables..

Table.Stories - containing stories and the user ID of which whom added it.

id, story, userid

Table.Users - containing user details and the users ID

id, username

I'd like to retrieve the stories table including the users id along with a new column (which is generated on the fly I guess) called username which holds the corresponding username from Table.Users.

I'm guessing I would use a mix of the AS and LEFT JOIN syntax. But I really don't know anymore..

Answer 1

SELECT s.id, s.story, s.userid, u.username FROM stories AS s INNER JOIN users AS u ON s.userid=u.id

Explanation to follow.

Answer 2

Following query will help you:

SELECT s.story, u.username FROM Stories s 
LEFT JOIN Users u ON (u.id=s.userid)

It returns value of story field of a story record and username of corresponding user.

Answer 3

This should work:

SELECT s.id, s.story, u.username
FROM stories s
INNER JOIN users u
ON u.id = s.userid

This basically says: "Give me the story id, story name, and the user from the Stories table, looking up the username from the Users table where the Users table ID matches the Stories table's userid."♦

Answer 4

SELECT st.*, COALESCE(u.username, 'unknown') AS username
FROM Table.Stores st
LEFT JOIN Table.Users u ON st.userid = u.id

Simple really; make sure you have an index on id within Table.users. The COALESCE function returns the first argument which isn't null so you can define the value for a story where no user if found.

If you're only interested in stories that have a corresponding user, then use

SELECT st.*, u.username
FROM Table.Stores st
JOIN Table.Users u ON st.userid = u.id

The inner join is implicit here and doesn't need to be specified.

Answer 5

select stories.*, Users.username from stories inner join Users On Stories.userid = Users.id

OR

select * from stories inner join Users using(userID) (if you keep consistent ID columns)

Answer 6

If there is always a one-to-one relation, i.e. each story always has a user, then you should use an inner join:

select t.id, t.story, t.userid, u.username
from Table.Stories t
inner join Table.Users u on u.id = t.userid

If the userid field can be null, indicating that a story can have a user or not, you should use a left join:

select t.id, t.story, t.userid, u.username
from Table.Stories t
left join Table.Users u on u.id = t.userid

For the story records where there is no user, the username field will be returned as null.