Innerjoin and only return 1 result for each row

[Monkuar]

Is that the difference between INNER Join and LEFT JOIN?

 

I am trying to LEFT/INNER join another table on the same column, but the table that I am joining has multiple values on that column.... I only want it to join once for each column possible?

[lemmin]

You might try GROUPing by a field that is duplicated (id).

 

Post more information about your schema and query if that isn't what you are looking for.

[Barand]

Is that the difference between INNER Join and LEFT JOIN?

 

I am trying to LEFT/INNER join another table on the same column, but the table that I am joining has multiple values on that column.... I only want it to join once for each column possible?

 

Inner joins will get data from matching rows only.

Left joins will get all rows from the left table (ie on the left of the words "LEFT JOIN") and matching data from the other table where a row exists

 

eg

tableA              tableB

-----|---------    -----|--------|---------

ID    NAME        ID    A_ID    COLOUR

-----|---------    -----|--------|---------

  1  |  Fred          1  |  2    |  Orange

  2  |  Jane          2  |  3    |  Yellow

  3  |  Bob

  4  |  Mary

 

SELECT a.name, b.colour

FROM tableA a

    INNER JOIN tableB b ON a.id = b.a_id

   

Name    |  Colour

---------|----------

Jane    |  Orange

Bob      |  Yellow

 

SELECT a.name, b.colour

FROM tableA a

    LEFT JOIN tableB b ON a.id = b.a_id

   

Name    |  Colour

---------|----------

Fred    |  null

Jane    |  Orange

Bob      |  Yellow

Mary    |  null

[Monkuar]

Aww Okay, Yeah I tried group by id and name for my column but it's still not working.  Man I  feel like a idiot posting this but here it is:

 

 

This is my main table:

 

 

 

This is the table I am joining:

 

 

 

 

 

This is my query:

 

SELECT u.username as champion, u.star, games.*, highscores.game from arcade_games as games
INNER JOIN arcade_highscores as highscores on games.name=highscores.game
LEFT JOIN users as u on highscores.user_id=u.id


    group by games.name ORDER by games.last_played DESC LIMIT 10

 

and I am calling my users table so I can get the username to determine who is the Champion from the "user_id" row in the arcade_highscores table.

 

Okay, see how I am INNER JOINING the arcade_highscores as highscores on games.name=highscores.game? As you can see in my screenshot it's STILL retuning more than 1 ROW for each value when I don't even use the group by games.name? Isn't inner join supposed to only match 1 value of each?

 

Nonetheless the results are not correct, because this is where I am stuck at, I am trying to figure out who has the highest score for each game. (Those results come from arcade_highscores) column = score represents for each game

 

Hope someone can help

 

The problem I believe is the "game" column under arcade_highscores as multiple values of the same name... that's why I thought innerjoin would work, apparently not..

[jazzman1]

Where is the users table?

[Monkuar]

Where is the users table?

 

user table just holds the username, user_id and other rows that are not called, it has like 300 rows

 

im just using it so i can grab the username really

SELECT u.username as champion, u.star, games.*, highscores.user_id, highscores.game from arcade_games as games
INNER JOIN arcade_highscores as highscores on games.name=highscores.game
LEFT JOIN users as u on highscores.user_id=u.id


    group by highscores.score ORDER by games.last_played,highscores.score DESC LIMIT 10

 

this is working partly, but it still spits rows for each game.... for each highscore, so like if a game has 10 highscores, it will show 10 rows of that game, really annoying

 

 

 

 

[jazzman1]

Post some data of these sql files, to make a query test with a real data, just to avoid from multiple posts.

[Barand]

You can use a subquery to find the highest highscore for each game then JOIN to that to find the matching arcade_highscores row. This will give you the player who scored highest.

 

SELECT u.username as champion, u.star, games.* 
FROM arcade_games as games
INNER JOIN arcade_highscores as highscores on games.name=highscores.game
INNER JOIN
(
    SELECT game, MAX(highscore) as highest
    FROM arcade_highscores
    GROUP BY game
) as X ON highscores.game = X.game AND highscores.highscore = X.highest
LEFT JOIN users as u on highscores.user_id=u.id
ORDER by games.last_played DESC LIMIT 10

[Monkuar]

You can use a subquery to find the highest highscore for each game then JOIN to that to find the matching arcade_highscores row. This will give you the player who scored highest.

 

SELECT u.username as champion, u.star, games.* 
FROM arcade_games as games
INNER JOIN arcade_highscores as highscores on games.name=highscores.game
INNER JOIN
(
    SELECT game, MAX(highscore) as highest
    FROM arcade_highscores
    GROUP BY game
) as X ON highscores.game = X.game AND highscores.highscore = X.highest
LEFT JOIN users as u on highscores.user_id=u.id
ORDER by games.last_played DESC LIMIT 10

 

Wow, having the sql separated like that makes it so much easier and comprehend.  I did the subquery and it works nice.  I been struggling for the past 2days now trying to get my current query to work, I obviously need to learn more about sub queries if I don't want to run into this problem again, appreciate your help immensely, thank you.

 

solved

[jazzman1]

Wow, having the sql separated like that makes it so much easier and comprehend.  I did the subquery and it works nice.  I been struggling for the past 2days now trying to get my current query to work, I obviously need to learn more about sub queries if I don't want to run into this problem again, appreciate your help immensely, thank you.

 

He is the best and knows to much. I like him (i'm not a gay)