• 0

[SQL] Replace UserID with Username


Question

Hello again all,

Another issue now with displaying news articles. I have two tables:

tblUsers

UserID	Username
1		name1
2		name2

tblNews
UserID	ArticleID	Article	Title
1		1		text		text
1		2		text		text

I want to display the Username for the news poster instead of the UserID and cannot work it out. I've looked into "inner joins" which I think is what I need to use but still can't work it out.

Thanks a lot

Link to comment
Share on other sites

18 answers to this question

Recommended Posts

  • 0

Try this:

SELECT u.Username, n.*
FROM tblNews n
LEFT JOIN tblUers u
ON n.UserID = u.id

Edit: I would advise against using Adan0s' query, as it is quite more performance intensive than a proper join.

Link to comment
Share on other sites

  • 0

um, something along the lines of...

select tblUsers.UserID, tblusers.Username, tblNews.ArticleID FROM tblNews LEFT JOIN tblUsers ON tblUsers.UserID=tblNews.UserID

But i'm rushing

  • Like 1
Link to comment
Share on other sites

  • 0

Try this:

SELECT u.Username, n.*
FROM tblNews n
LEFT JOIN tblUers u
ON n.UserID = u.id

Edit: I would advise against using Adan0s' query, as it is quite more performance intensive than a proper join.

Not sure I agree with there being a performance hit. The type of join he uses, although may be a little confusing is no more than an inner join. Most DB engines would optimize it the same was as it optimizes an inner join.

Link to comment
Share on other sites

  • 0

Thanks all for the responses. It is now working very well. (MarkusDarkus was the suggestion that worked best for my needs.) :)

EDIT: While I'm at it I may aswell plug my other post...same news table has a DateTime for when the article is posted. Want it to auto input the current DateTime when adding a new article (Am using the standard ListView template for adding/editing items) and also when displaying the article I only want the date to show, not the full date and time. Is this possible? whistle.gif

Link to comment
Share on other sites

  • 0

:ninjapirate:

The answer to your second question is yes. Show me an example of the stored and the desired display format. It makes it easier for me lol.

P.S. Vote me, Vote me Vote me!

Link to comment
Share on other sites

  • 0

How come nobody ever takes advantage of the USING clause? There's no need for repetitive conditional expressions if both of your tables use the same column name.

SELECT * FROM t1 JOIN t2 USING (column)

Is the same as...

SELECT * FROM t1 JOIN t2 ON t1.column = t2.column

Link to comment
Share on other sites

  • 0

i just looked in BOL for USING.. can't see it :o

where is the doco for that statement? i don't think i've ever seen it!

Maybe too many people using MSDN as their reference is the problem. :p

It is part of the SQL-2003 standard.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.