Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2014
    Posts
    3

    Unanswered: Join two tables and return both combined?

    Hey all!

    I have a table called 'GAMES' with only two fields (g_pk and game_name):

    Code:
    g_pk game_name
     0     Game 0
     1     Game 1
     2     Game 2
     3     Game 3
    And have another table called 'USER_GAMES' with three fields (ug_pk, user_pk and game_pk):

    Code:
    ug_pk user_pk   game_pk
     0       0         1
     1       0         2
    Joining the two tables like this:

    Code:
    SELECT * FROM user_games U inner join games G on U.game_pk = G.pk where U.user_pk = 0
    I will know that the user whose PK=0 'owns' the games pk=1 and pk=2.

    I need a query that returns me a complete list of all games in the games table with an extra field telling me which ones are owned by the user pk=0 for instances.

    Something like this:

    Code:
    g_pk game_name   owned
     0     Game 0      0
     1     Game 1      1
     2     Game 2      1
     3     Game 3      0
    Any idea?

    Thanks!


  2. #2
    Join Date
    Sep 2014
    Posts
    3
    So I suppose that it is not possible right?



    Thanks anyway guys! I will find a different solution! Cheers!


  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Just remove the where clause to return all rows that match the join. The where user =... Means only return the rows whose user equals the specified value
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2014
    Posts
    3
    Quote Originally Posted by healdem View Post
    Just remove the where clause to return all rows that match the join. The where user =... Means only return the rows whose user equals the specified value
    No problem! Your idea unfortunately doesn't solve my issue though...



    Anyway, since seems that there is not a way to do it in SQL I implemented a too much more simple solution.



    I needed to generate a screen list with all games names and a checkbox aside each name and depending on the logged user I needed that the checkbox be checked for the games that user owns.

    So, suppose that I have a list with 100 games and the user pk=0 'owns' the game 1, 2 and 3 for instances. So I should get a list of all the 100 games with the games 1, 2 and 3 checkboxes checked. Simple, uh?

    What I did:

    First I ran a query to get a complete list of all games. Then I ran a query to get the games that the user 'owns' and loaded it into an array. Then when generating the screen list, I looped through the array to see if the current list entry is in the array, and if yes, it gets checked.

    Don't know why I didn't think about this before! LoL!


  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try this.

    Code:
    SELECT G.*
         , CASE
           WHEN U.ug_pk IS NOT NULL THEN
                'Yes'
           ELSE 'No'
           END  AS owned
     FROM  games      G
     LEFT  OUTER JOIN
           user_games U
      ON   U.game_pk = G.g_pk
      AND  U.user_pk = 0
     ORDER BY
           g_pk
    ;
    Note: I displayed 'Yes' or 'No' for "owned" column instead of 1 or 0 in your example.
    Because, I puzzled the values 1 or 0 at first.

Posting Permissions

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