SELECT and SELECT DISTINCT in a single query?

[wright67uk]

In the below query, how can I only select distinct values for races.name?

ie.  obviously this won't work, but to get my point across...

SELECT phpbb_users.user_email, races.date, DISTINCT races.name

 

my sql as it stands...

SELECT phpbb_users.user_email, races.date, races.name, races.odds, races.race, actions.action, actions.user_id 
FROM races 
RIGHT JOIN actions ON races.name = actions.name 
RIGHT JOIN phpbb_users ON actions.user_id = phpbb_users.user_id
WHERE races.date = $date" ;

[Barand]

Could you describe the relationships between those tables?

 

eg

A : B  = 1 A record has 0,1, or many B records

A : C = 1 A record has 1 or many C records

[wright67uk]

 
races has 3000 records and contains the column 'race', 'odds', 'name'
php_users has many records and contains 'user_id', 'user_email'
actions has only a few records at the moment and contains 'action', 'user_id', 'name'
 
in total
 
races has 4 columns, php_users has 84 columns, and actions has 4 columns

 

I would like to have a result where 'Hedy' only appears once. (Not fussed which of the two records remains)

Edited May 7, 2013 by wright67uk

[Psycho]

So, I assume you want all the data from that query - even though many records will have the same race name, correct? But, you also want a list of the unique race names. It would be helpful to know how you want to output the data as that might affect the best solution to implement. But, there's really no way to do this with a single query.

 

If you don't need all the data, then you could simply do a GROUP BY. But, if you do need all the data, I would do this in the PHP code that processes the results. Something such as

 

$query = "SELECT phpbb_users.user_email, races.date, races.name, races.odds, races.race, actions.action, actions.user_id 
          FROM races
           RIGHT JOIN actions ON races.name = actions.name 
          RIGHT JOIN phpbb_users ON actions.user_id = phpbb_users.user_id
          WHERE races.date = $date";
$result = mysql_query($query);
 
$uniqueRaceNames = array();
while($row = mysql_fetch_assoc($result))
{
    //Check if current name has been added to array, if not add it
    if(!in_array($row['name'], $uniqueRaceNames))
    {
        $uniqueRaceNames[] = $row['name'];
    }
 
    //Process the rest of the data
}

[wright67uk]

Hi thanks for the reply,

I'm pasting my code, using my cell phone, so i hope it looks ok.

 

 <?php
include 'connect.php';

$date = date('Ymd'); 
$query= "SELECT phpbb_users.user_email, races.date, races.name, races.odds, races.race, actions.action, actions.user_id 
         FROM races 
         RIGHT JOIN actions ON races.name = actions.name 
         RIGHT JOIN phpbb_users ON actions.user_id = phpbb_users.user_id
         WHERE races.date = $date" ;
		 
                if(!$result = $mysqli->query($query)){
                die('There was an error running the query [' . $mysqli->error . ']');
				                                     }													 
                 while($row = $result->fetch_assoc())
                        {
                        $horse_name = $row['name']; 
						$user_id = $row['user_id']; 
						$race = $row['race'];                    
						 
$user_email = $row['user_email'];
echo $info_msg = "just to let you know a horse you are following is racing soon! <br><b> " . $horse_name . " </b> will be racing in the " . $race;

$to      = $user_email;
$subject = 'A reminder from TNT';
$message = $info_msg;
$headers = 'From: reminder@reminder.com' . "\r\n" .
   		   'Reply-To: reminder@reminder.com' . "\r\n" .
  		   'X-Mailer: PHP/' . phpversion();
		    mail($to, $subject, $message, $headers);         }
                    

?>

As you can see, I'm emailing a reminder, when a horse is due to run. I don't want to send an email if the horse name appears twice in my database.

[wright67uk]

I'm really not very experienced with sql, I was trying to read up on aliases , in the hope I could do a select, followed by a select distinct on that result set.