sql join repeating results

Question

I need to make a list printing all the clients ordered by the gym name, but it's repeating the gym name the same number of gym's clients. If gym1 have 4 clients, the echo is printed 4 times.

The tables/columns are:

members (id, gym, name, etc...)

and

gym (gymID, gym_name, etc...).

member.gym is to know to what gym the client belongs (gym.gymID)

if ($stmt = $mysqli->prepare("  SELECT DISTINCT g.*, m.*
                                FROM gym g
                                INNER JOIN members m ON m.gym = g.gymID")) {

    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_array()) {
        echo 'Sport center: ' . $row['gym_name'] . '<br>';
        // here print the gym's clients list
    }

}

DISTINCT is not working... What is the problem??

[Maybe this helps](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join). — CodeAngry, Nov 05 '14 at 15:36
DISTINCT will report unique rows, not unique column values. So if gym 1 has 4 users (1,2,3,4) then it will report 1,1 then 1,2 then 1,3 then 1,4 since there are no duplicates in the rows returned. — simon at rcl, Nov 05 '14 at 15:38
The SQL is working correctly. That is *exactly* what the query should be returning. You are misunderstanding the SQL language and specifically what `DISTINcT` does. — Gordon Linoff, Nov 05 '14 at 15:38
@ChazyChaz Which layout you want? Because what you're getting is exactly what it's supposed to happen — Barranka, Nov 05 '14 at 15:43

Barranka · Accepted Answer · 2014-11-05T16:30:11.640

That's the normal behavior.

Example.

Consider the following tables

Table "gym"
-----------
gym_id | gym_name
-------+----------
  1    |  Gym A
  2    |  Gym B

Table "members"
---------------
member_id | gym_id | member_name
----------+--------+------------
  1       |  1     | Bob
  2       |  1     | Jeff

And now, execute this query:

select g.gym_id, g.gim_name, m.member_id, m.member_name
from gym as g
     inner join members as m on g.gym_id = m.gym_id;

Result:

gym_id | gym_name | member_id | member_name
-------+----------+-----------+-------------
  1    |  Gym A   |   1       |   Bob
  1    |  Gym B   |   2       |   Jeff

That happens because each row in the gym table is matched with a row in the members name. Even if you use select distinct, the result would be the same, because every row is different.

I think what you want is an output like this:

 Gym A
      Bob
      Jeff

Although that can be done directly in SQL, it's easier to handle it directly with PHP, because doing it in SQL ~~would be a real pain in the neck~~ would require writing quite a complex query. I'm not quite good with PHP, but it could be something like this:

/*
 You don't need "DISTINCT", but you need "ORDER BY" to make this work
 */
if ($stmt = $mysqli->prepare("  SELECT g.*, m.*
                                FROM gym g
                                INNER JOIN members m ON m.gym = g.gymID
                                ORDER BY g.gymID")) {

    $stmt->execute();
    $result = $stmt->get_result();
    $gym = "";
    while ($row = $result->fetch_array()) {
        if($row['gym_name'] != $gym)
            echo 'Sport center: ' . $row['gym_name'] . '<br>';
        echo '   Member: ' . $row['member_name'] . '<br>';
        $gym = $row['gym_name'];
    }

}

@ChazyChaz Happy to help. Of course, if you want to list also the gyms without members, you can use `LEFT JOIN` instead of `INNER JOIN`. — Barranka, Nov 05 '14 at 16:33

score 0 · Answer 2 · answered Nov 05 '14 at 15:43

0

First, drop the DISTINCT and slap in an ORDER BY:

SELECT g.*, m.*
FROM gym g
INNER JOIN members m ON m.gym = g.gymID
ORDER BY g.name;

Now, adjust your PHP code to only print the gym name if it's different from the last gym you printed.

answered Nov 05 '14 at 15:43

Kevin

28,963
9
62
81

score 0 · Answer 3 · answered Nov 05 '14 at 15:48

0

SELECT g.*, m.*
FROM gym g
INNER JOIN members m ON m.gym = g.gymID
ORDER BY g.name,gym_clients;

I dont know the name of Gym Name column & gym Client Column So, Please Change the name if they are not correctly spelled. I hope this will work for you

answered Nov 05 '14 at 15:48

Hardik Parmar

1,053
3
15
39

score 0 · Answer 4 · 2014-11-05T16:01:50.313

0

This should work:

SELECT *
FROM gym g
LEFT JOIN members m 
     ON g.gym_ID=m.gym_id 
GROUP BY g.gym_name

edited Nov 05 '14 at 16:01

answered Nov 05 '14 at 15:49

Is that even valid MySQL syntax? I'm asking you because I'm trying to test it in [this SQL fiddle](http://sqlfiddle.com/#!2/d020a/3) and it doesn't work – Barranka Nov 05 '14 at 15:57
@Barranka I answered according to what I saw on your question. The date on your question is not the same as on your SQL fiddle. Now I checked your SQL fiddle. Please try my update. – Nov 05 '14 at 16:06
It's not *my* question (check the OP's name and mine)! I posted my comment because I'm almost sure that it was not a valid syntax for MySQL (or any SQL dialect). But now you've edited your answer and removed that weird thing. Anyway... `LEFT JOIN` would make no difference to the OP, since the "problem" is that the output is not what he expected, but that has nothing to do with the SQL, but with the front-end program – Barranka Nov 05 '14 at 16:27

sql join repeating results

4 Answers4