Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Unanswered: 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.
    Last edited by Henxian; 03-06-07 at 15:30.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You could use a UNION operator for starters.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use a UNION, or try this:
    Code:
    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:
    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    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.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jun 2006
    Posts
    4
    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.
    Last edited by Henxian; 03-06-07 at 15:54.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    How 'bout this:

    Code:
    
    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
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Henxian Dude, learn some basic SQL....
    Code:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by blindman
    Henxian Dude, learn some basic SQL....
    Code:
    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?
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jun 2006
    Posts
    4
    Quote Originally Posted by blindman
    Henxian Dude, learn some basic SQL....
    Code:
    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!!!.

    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 )

    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!
    Last edited by Henxian; 03-06-07 at 19:11.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by RedNeckGeek
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •