DB Query (complex maybe)

I have database like below.

BLOG
--POSTER_ID
--POSTED_BY (a FK refer to USERID)
--UPDATED_BY (a FK refer to USERID)

USER
--USER_ID
--NAME

I want to retrieve the info for all posts that posted and updated by different users, also the query returns both user names for every posted and updated action.

I can write two queries to do that

select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.POSTED_BY = U.USER_ID

select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.UPDATED_BY = U.USER_ID

Then write some code to combine the result for the posted and updated user name. But this is not efficient.

I am wondering how can I write one query to archeive that?

Thanks.You could use a UNION operator for starters.|||Use a UNION, or try this:
select B.POSTER_ID,
U.NAME
from Blog B
inner join USER U
ON B.POSTED_BY = U.USER_ID
OR B.UPDATE_BY = U.USER_ID
...but I'm guessing UNION will give you more functionality, as it makes it easy to determine what the user's role was regarding the thread:
select B.POSTER_ID,
'POSTER' as Role,
U.NAME
from Blog B
inner join USER U ON B.POSTED_BY = U.USER_ID
UNION
select B.POSTER_ID,
'RESPONDER' as Role,
U.NAME
from Blog B
inner join USER U ON B.UPDATED_BY = U.USER_ID|||select B.POSTER_ID,
U.NAME
from Blog B
inner join USER U
ON U.USER_ID in (B.UPDATE_BY,B.POSTED_BY)

I am not sure if this will work .. not close to a SQL machine to test.|||Thanks ya'll quick replies.

The UNION function still not perfect for what I expected.

In this case, what I exactly want to return is three fields:

POSTER_ID (in the blog), User Name (who posted it), User Name (who last modified it)

Suppose, I have POSTER_ID 1 in the BLOG table, John Doe posted it, and Mike Smith modified it.

I wish I could write a query to return a row like this:

POSTER_ID--|--POSTED_BY--|--MODIFIED_BY
---1---|--John Doe---|--Mike Smith

If I use UNION (ALL), the best case I can get is

POSTER_ID--|--USER NAME
---1---|--John Doe---
---1---|--Mike Smith---

Just wondering is that possible to use query to acheive that or use more than one query is the better idea?

Thank you.|||How 'bout this:

CREATE TABLE #BLOG (
POSTER_ID INTEGER,
POSTED_BY INTEGER,
UPDATED_BY INTEGER
)

INSERT INTO #BLOG
SELECT 1, 1,1
UNION ALL
SELECT 2,2,1
UNION ALL
SELECT 3,1,2
UNION ALL
SELECT 4,2,2

CREATE TABLE #USER (
USERID INTEGER,
USERNAME VARCHAR(20)
)

INSERT INTO #USER
SELECT 1, 'JOHN DOE'
UNION ALL
SELECT 2, 'JANE SMITH'

SELECT POSTER_ID, US1.USERNAME, US2.USERNAME
FROM #BLOG
INNER JOIN #USER US1 ON #BLOG.POSTED_BY=US1.USERID
INNER JOIN #USER US2 ON #BLOG.UPDATED_BY=US2.USERID
WHERE POSTED_BY<>UPDATED_BY

DROP TABLE #BLOG
DROP TABLE #USER|||Henxian Dude, learn some basic SQL...
select Blog.POSTER_ID,
Posters.NAME as Poster,
Updaters.NAME as Updater
from Blog
left outer join USER Posters ON Blog.POSTED_BY = Posters.USER_ID
left outer join USER Updaters ON Blog.UPDATED_BY = Updaters.USER_ID
Please read the Books Online section on SELECT statements, and we can answer any further questions you have.|||Henxian Dude, learn some basic SQL...
select Blog.POSTER_ID,
Posters.NAME as Poster,
Updaters.NAME as Updater
from Blog
left outer join USER Posters ON Blog.POSTED_BY = Posters.USER_ID
left outer join USER Updaters ON Blog.UPDATED_BY = Updaters.USER_ID
Please read the Books Online section on SELECT statements, and we can answer any further questions you have.

Why is left outer needed instead of inner? Did I miss something?|||Henxian Dude, learn some basic SQL...
select Blog.POSTER_ID,
Posters.NAME as Poster,
Updaters.NAME as Updater
from Blog
left outer join USER Posters ON Blog.POSTED_BY = Posters.USER_ID
left outer join USER Updaters ON Blog.UPDATED_BY = Updaters.USER_ID
Please read the Books Online section on SELECT statements, and we can answer any further questions you have.

Wow Blindman you rock!!!. :shocked:

I figure out another way that use CASE, WHEN, THEN clause to acheive the same result as yours. (It take me several hours to figure out that way) But compare to yours, my query is too much longer and complicate. (too shy to post my SQL here:p )

Yeah, I nerver use outer join in my queries before. Will look at it in the furture.

Appreicate you all's help. This is a great forum!|||Why is left outer needed instead of inner? Did I miss something?
I'd recommend outer joins here to cover cases where either the POSTED_BY column is null (unlikely) or the UPDATED_BY column is null (more than likely).

You could get away with an inner join on the POSTED_BY column, and I suppose this may even be preferable if you only want to show users that have posted.