Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Mar 2013
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts

    join, group by and having means?

    What does join, group by and having means in mysql? What can you do with they?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,230
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    http://www.lmgtfy.com/?q=mysql+join

    http://www.lmgtfy.com/?q=mysql+group+by

    http://www.lmgtfy.com/?q=mysql+having

    But, really, they mean the same thing in MySQL that they do in any other standard relational database. They are part of ANSI SQL. The only difference is that MySQL's GROUP BY does have some idiosyncrasies compared to ANSI SQL.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Mar 2013
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have read but still don't get it. I search for a direct answer, i don't want to read a whole book and in the end it says you can use group by to do that and that. I have google and google today. But my english suck.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,230
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Okay, I will try to keep it simple. I will try doing this BY EXAMPLE.

    So let's start by creating two tables and putting some data in them:
    Code:
    mysql> CREATE TABLE players (
        ->     playerid INT AUTO_INCREMENT PRIMARY KEY,
        ->     playername VARCHAR(50)
        -> ) ENGINE INNODB;
    Query OK, 0 rows affected (0.11 sec)
    
    mysql> INSERT INTO players ( playername )
        -> VALUES( 'Adam' ), ( 'Ben' ), ( 'Cindy' ), ( 'Deb' );
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    mysql> SELECT * FROM players;
    +----------+------------+
    | playerid | playername |
    +----------+------------+
    |        1 | Adam       |
    |        2 | Ben        |
    |        3 | Cindy      |
    |        4 | Deb        |
    +----------+------------+
    4 rows in set (0.00 sec)
    
    mysql> CREATE TABLE gamesplayed (
        ->     winner INT,
        ->     loser INT,
        ->     CONSTRAINT FOREIGN KEY (winner) REFERENCES players(playerid),
        ->     CONSTRAINT FOREIGN KEY (loser) REFERENCES players(playerid)
        -> ) ENGINE INNODB;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> INSERT INTO gamesplayed ( winner, loser )
        -> VALUES( 1, 2 ), ( 1, 3), ( 4, 1 ), ( 2, 4 ), ( 3, 2 ), ( 4, 2 );
    Query OK, 6 rows affected (0.01 sec)
    Records: 6  Duplicates: 0  Warnings: 0
    
    mysql> SELECT * FROM gamesplayed;
    +--------+-------+
    | winner | loser |
    +--------+-------+
    |      1 |     2 |
    |      1 |     3 |
    |      4 |     1 |
    |      2 |     4 |
    |      3 |     2 |
    |      4 |     3 |
    +--------+-------+
    6 rows in set (0.02 sec)
    So these tables represent a tournament where every player played every other player, riight?

    Okay, but it's hard to "see" who the winners and losers are, isn't it? You'd like to the NAMES of the winners and losers.

    For that, we need a JOIN:
    Code:
    mysql> SELECT *
        -> FROM players INNER JOIN gamesplayed
        -> ON players.playerid = gamesplayed.winner;
    +----------+------------+--------+-------+
    | playerid | playername | winner | loser |
    +----------+------------+--------+-------+
    |        1 | Adam       |      1 |     2 |
    |        1 | Adam       |      1 |     3 |
    |        2 | Ben        |      2 |     4 |
    |        3 | Cindy      |      3 |     2 |
    |        4 | Deb        |      4 |     1 |
    |        4 | Deb        |      4 |     2 |
    +----------+------------+--------+-------+
    6 rows in set (0.00 sec)
    
    mysql> SELECT *
        -> FROM players INNER JOIN gamesplayed
        -> ON players.playerid = gamesplayed.loser;
    +----------+------------+--------+-------+
    | playerid | playername | winner | loser |
    +----------+------------+--------+-------+
    |        1 | Adam       |      4 |     1 |
    |        2 | Ben        |      1 |     2 |
    |        2 | Ben        |      3 |     2 |
    |        3 | Cindy      |      1 |     3 |
    |        3 | Cindy      |      4 |     3 |
    |        4 | Deb        |      2 |     4 |
    6 rows in set (0.00 sec)
    That is a step in the right direction, but we'd really like to see the names of *BOTH* the winners and loser in the same results, right?

    For that, we need to use a small trick. We have to JOIN to the players table *TWICE*. And to do that we have to introduce the concept of an ALIAS:
    Code:
    mysql> SELECT P1.playername AS winnername, P2.playername AS losername
        -> FROM gamesplayed
        -> INNER JOIN players AS P1 ON gamesplayed.winner = P1.playerid
        -> INNER JOIN players AS P2 ON gamesplayed.loser  = P2.playerid;
    +------------+-----------+
    | winnername | losername |
    +------------+-----------+
    | Adam       | Ben       |
    | Adam       | Cindy     |
    | Deb        | Adam      |
    | Ben        | Deb       |
    | Cindy      | Ben       |
    | Deb        | Cindy     |
    +------------+-----------+
    6 rows in set (0.00 sec)
    Try all those out. Really *DO* create the tables in your database.
    Last edited by Old Pedant; 05-12-2013 at 01:27 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,230
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    GROUP BY is used when you want to group records together and use an "AGGREGATE FUNCTION" to combine the results.

    The common AGGREGATE FUNCTIONS in SQL are:
    COUNT( ) -- count the number of occurences of a record or non-null field
    SUM( ) -- sum the values of zero or more occurrence of a field
    MIN( ) -- find the lowest value in a group of fields
    MAX( ) -- find the highest value in a group of fields.
    There are other less-used aggregate functions, but if you learn those four you have a good start.

    For this example, and the next, we will use the COUNT( ) function, because it is the simplest.

    Code:
    mysql> SELECT winner, COUNT(*) as gameswon
        -> FROM gamesplayed
        -> GROUP BY winner;
    +--------+----------+
    | winner | gameswon |
    +--------+----------+
    |      1 |        2 |
    |      2 |        1 |
    |      3 |        1 |
    |      4 |        2 |
    +--------+----------+
    4 rows in set (0.00 sec)
    
    mysql> SELECT loser, COUNT(*) as gameswon
        -> FROM gamesplayed
        -> GROUP BY loser;
    +-------+----------+
    | loser | gameswon |
    +-------+----------+
    |     1 |        1 |
    |     2 |        2 |
    |     3 |        2 |
    |     4 |        1 |
    +-------+----------+
    4 rows in set (0.00 sec)
    And now you know how many games each person won and how many each person lost.

    Well, yes, you do. But it's not very easy to read it and figure it out, is it?

    So now let us COMBINE our GROUP BY with a JOIN:
    Code:
    mysql> SELECT players.playername, COUNT(*) as gameswon
        -> FROM gamesplayed
        -> INNER JOIN players ON players.playerid = gamesplayed.winner
        -> GROUP BY players.playername;
    +------------+----------+
    | playername | gameswon |
    +------------+----------+
    | Adam       |        2 |
    | Ben        |        1 |
    | Cindy      |        1 |
    | Deb        |        2 |
    +------------+----------+
    4 rows in set (0.00 sec)
    But wait! Suppose we are only interested in those players who won more than one game?

    AHA! That's where HAVING comes in:
    Code:
    mysql> SELECT players.playername, COUNT(*) as gameswon
        -> FROM gamesplayed
        -> INNER JOIN players ON players.playerid = gamesplayed.winner
        -> GROUP BY players.playername
        -> HAVING gameswon > 1;
    +------------+----------+
    | playername | gameswon |
    +------------+----------+
    | Adam       |        2 |
    | Deb        |        2 |
    +------------+----------+
    2 rows in set (0.00 sec)
    SO:

    JOIN to combine two or more tables.
    GROUP BY to group records with the same value in one or more fields togather.
    HAVING to select only certain records *AFTER* having done a GROUP BY.

    (Note: You *can* use HAVING even if you don't use GROUP BY, but there are usually better ways to get same results in those cases.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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