Remove duplicate entries

toad78 · January 8, 2010, 8:21pm

PROBLEM: I need to retrieve all of the records from the two tables, GROUPED by ‘medal_id’. When I try the query below, it only retrieves one record from each ‘medal_id’ type: IE:

Platinum
BOS, Danielle NULL

Gold
Balls, Craig NULL

Can someone correct me in telling me why I cannot retrieve all of my records?

I have a tables with the following fields:

president_circle:
circle_id medal_id business employee position
1 6 BOS Danielle NULL
2 6 PRTY Mitch NULL
3 5 BALLS Craig NULL

ab_medal:
medal_id medal
5 Gold
6 Platinum

Query:

SELECT president_circle.business, president_circle.employee, president_circle.`position`, ab_medal.medal_id, ab_medal.medal
FROM president_circle, ab_medal
WHERE president_circle.medal_id = ab_medal.medal_id
GROUP BY ab_medal.medal_id

felgall · January 8, 2010, 8:49pm

You are grouping by medal_id and will therefore only get one entry per medal. If you want to get all the names then don’t group by just medal_id, include the name in what you are grouping by as well.

r937 · January 8, 2010, 9:13pm

the particular “grouping” that you are looking for is not provided by GROUP BY (the purpose of which is to aggregate all rows for each group into one row)

instead, the “grouping” you want is actually sorting

here, try this –

SELECT ab_medal.medal 
     , president_circle.business
     , president_circle.employee
     , president_circle.`position`
  FROM ab_medal 
LEFT OUTER
  JOIN president_circle
    ON president_circle.medal_id = ab_medal.medal_id 
ORDER
    BY ab_medal.medal_id

toad78 · January 8, 2010, 10:36pm

Here’s the result from your sample above r937:

PLATINUM:
name here

GOLD:
name here

GOLD:
name here
GOLD:
name here
GOLD:
name here

I’ve attached an image in case you wanted to see a sample of the live result.

Any way to put all those who are in the PLATINUM under PLATINUM, and so on without the repetitive?

r937 · January 8, 2010, 10:45pm

no, that is not quite what the query produces

every query produces a tabular result set, which in your case would look like this –


PLATINUM name here
PLATINUM name here
PLATINUM name here
PLATINUM name here
GOLD     name here
GOLD     name here
GOLD     name here
GOLD     name here

the results you are getting are due entirely to the way you’re processing the query result set with your application code

it is a trivial matter to change your application code so that it produces this output –

PLATINUM

name here
name here
name here
name here
GOLD
name here
name here
name here
name here

toad78 · January 8, 2010, 11:09pm

Which is the result I’ve been seeking

You say “trivial”, I say “I guess I have to work on this some more to get the result I want”.

r937 · January 8, 2010, 11:15pm

well, it’s certainly not a mysql issue

what programming language are you using? we’ll get this thread moved to the appropriate forum

toad78 · January 8, 2010, 11:17pm

Php

Dan_Grossman · January 9, 2010, 4:48am

A bit of logic to only display the heading once per group:


$sql = "SELECT ab_medal.medal 
     , president_circle.business
     , president_circle.employee
     , president_circle.`position`
  FROM ab_medal 
LEFT OUTER
  JOIN president_circle
    ON president_circle.medal_id = ab_medal.medal_id 
ORDER
    BY ab_medal.medal_id";

$result = mysql_query($sql);

$heading = "";
while ($row = mysql_fetch_array($result)) {

  //only print the heading if this row's medal is different from the last heading printed
  if ($row['medal'] != $heading) {
    echo "&lt;h1&gt;" . $row['medal'] . "&lt;/h1&gt;";
    $heading = $row['medal'];
  }

  //always print the current row's name/business/whatever
  echo $row['employee'] . " and " . $row['business'] . "&lt;br /&gt;";
}

toad78 · January 9, 2010, 9:58pm

Thank you, Dan. That worked out real nice!

Sitepoint Guru’s are great!

toad78 · January 10, 2010, 12:37am

I do have another question:
I have a table with the following:
pos_id position
1 Secretary

bep_id boardposition
1 Executive
2 Board

There’s a series of people that fall into one or both categories above. Let’s say that someone falls under ‘Executive’ and has the position ‘Secretary’. We would see:
Executive
Secretary: soandso

Secretary: soandso2
Secretary: soandso3

But if someone isn’t a Secretary (but is something else) and falls under ‘Board’, I receive the result as follows:

Board
: soandso

Treasurer: soandso
Director: soandso

I have this:

<p><?php if ($row_getBoard['position'] !=$position) { echo "<strong>".$row_getBoard['position'].":&nbsp;</strong>"; $position = $row_getBoard['position']; } ?><strong><?php echo $row_getBoard['name']; ?></strong><br />

What would be causing the unnecessary colon in the first record displayed?

Dan_Grossman · January 10, 2010, 1:22am

“: </strong>”

^ the colon

toad78 · January 10, 2010, 3:28am

Sorry?

Dan_Grossman · January 10, 2010, 4:57am

You asked what was causing the colon, and you have a colon in the string you echo out, is that not the one?

toad78 · January 10, 2010, 3:40pm

Yes. But why is it occurring in the first record of another category when it doesn’t have a position? If it doesn’t have a position, I don’t want it to appear. I thought the IF statement covered that. Or do I need to create an ‘else’?