0

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..

Improve this question
4
  • Also please provide the queries you tried Commented Nov 2, 2009 at 21:34
  • Most of my attempts have resulted in errors. The ones which have worked have combined the two tables giving me a bunch of 'unnecessary columns'. I only want a new username column from Table.Users. Commented Nov 2, 2009 at 21:37
  • Attempts have looked somewhat similar to this. SELECT * FROM stories LEFT JOIN users ON stories.id = siteusers.id AS username Excuse my laughable syntax. Commented Nov 2, 2009 at 21:41
  • Thank you all kind sirs. You have all surprised me by your timely responses. Peace be on you. Commented Nov 2, 2009 at 21:54

6 Answers 6

Reset to default
4 
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.

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

1 Comment

Assuming that there is a user for each story, there is no need for a LEFT JOIN. An INNER JOIN requires that there is a row in the tables on each "side" of the JOIN, while a LEFT JOIN just returns NULL values for each column in the second table, if there is no row matching the ON criteria.
1

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.

Improve this answer

1 Comment

Yes, but what is the problem with null?
0

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."♦

Improve this answer

Comments

0 
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.

Improve this answer

Comments

0

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)

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.