SQL query displaying one column and multiple values

Question

I have a couple of tables...one contains sites and another contains some users. There is a link binding specific users to specific sites. When I run my query:

select sites.site, users.fullname
from sites
inner join users
on sites.nameid = users.id;

I get the following output:

 SITE  | FULLNAME

site A | John Doe
site B | John Doe
site C | John Doe
site D | Roger Rabbit
site E | Roger Rabbit
site F | Batman

What I'm looking for is one FULLNAME with the list of sites below:

  SITE | FULLNAME

site A | John Doe
site B
site C
site D | Roger Rabbit
site E
site F | Batman

I don't know if this is even possible or if I have to use something else to get the format I'm looking for.

I'm going to add some more details here that I should have included....first off...my query has changed to this:

    SELECT completed.date, users.username, iata.code, tasks.task 
FROM completed     
JOIN users         
ON users.id = completed.name     
JOIN iata         
ON iata.id = completed.code     
JOIN tasks         
ON tasks.id = completed.task
WHERE MONTH(completed.date) = MONTH(NOW()) AND YEAR(completed.date) = YEAR(NOW());

That gives me everything I want...my php /html that I'm using to display looks like this:

<!DOCTYPE html>
<html>

   <head>
      <title>SPOC Report</title>
      <meta charset=utf-8>
   </head>

   <body>
        <?php 
        require 'db/spoc_config.php';
        ?>

        <?php

        $conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $sql_count = "
SELECT completed.date, users.username, iata.code, tasks.task 
FROM completed     
JOIN users         
ON users.id = completed.name     
JOIN iata         
ON iata.id = completed.code     
JOIN tasks         
ON tasks.id = completed.task
WHERE MONTH(completed.date) = MONTH(NOW()) AND YEAR(completed.date) = YEAR(NOW());
";  

        $result_count = $conn->query($sql_count);

        $count = mysqli_fetch_row($result_count);
        $str_count = implode($count);

         date_default_timezone_set('UTC');
         $today = date('l, jS F Y');
         $week = date('W');
         $to = "[email protected]";
         $subject = "SPOC: Monthly Report " . $today;        
         $heading = "<b>SPOC Report: </b>";

         $message = "<h2>" . $heading . " " . $today . "</h2>"; 
         $message .= '<table style="font-family: verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse: collapse;border-spacing: 2px;">';
         $message .= '
         <th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">Spoc</th> 
         <th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">Site</th>
         <th style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;">Task</th>';

         if ($result_count->num_rows > 0) {
         while($row = $result_count->fetch_assoc()) {
         $message .= '<tr style="white-space: normal;line-height: normal;font-weight: normal;font-variant: normal;font-style: normal;text-align: start;">
          <td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;display: table-cell;vertical-align: inherit;">'.$row["username"].'</td>
          <td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;display: table-cell;vertical-align: inherit;">'.$row["code"].'</br></td>
          <td style="border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;display: table-cell;vertical-align: inherit;">'.$row["task"].'</br></td>
          </tr>';
                }
            } else {
                echo "0 results";
            }
         $message .= '</table>';


         $header = "From:[email protected] \r\n";
         $header .= "MIME-Version: 1.0\r\n";
         $header .= "Content-Type: text/html; charset=ISO-8859-1\r\n";

         $retval = mail ($to,$subject,$message,$header,'[email protected]');


         $conn->close();
        ?>     
   </body>
</html>

The output however looks like this:

SPOC        | SITE   | TASK

JohnDoe     | SITE A | TASK1
JohnDoe     | SITE A | TASK2
JohnDoe     | SITE B | TASK1
JohnDoe     | SITE C | TASK1
ROGERRABBIT | SITE A | TASK1
ROGERRABBIT | SITE A | TASK2

What I'm looking for is:

JohnDoe     | Site A | TASK1, TASK2, TASK3
JohnDoe     | Site B | Task1, Task2
RogerRabbit | Site A | tASk1, Task2

Hope this clear it up a bit. Sorry folks for the confusion.

I am thinking you might try querying the user and then list the sites for that user instead of querying the sites and listing each site along with each entry to that site. I'm sorry I can't elaborate, I'm leaving the house right now. — ddonche
– ddonche, Commented May 31, 2016 at 18:02
Where is your current php code that creates your current layout? A simple step would be to use a variable to hold the current FULLNAME, i.e. $currentFULLNAME. If the value row matches the $currentFULLNAME value you don't echo out the row FULLNAME. If the row value doesn't match, you set $currentFULLNAME to the new name and echo out the row value — Sean
– Sean, Commented May 31, 2016 at 18:06
I would not try and get that result in SQL, that's gonna be rather tedious to accomplish. Simply do it while outputting those records via the PHP script - all you have to implement is just a basic one-level group break. — C3roe
– C3roe, Commented May 31, 2016 at 18:18

Lord Grosse Jeanine · Accepted Answer · 2016-05-31 18:36:30Z

1

This is not exactly what you want, but it might help:

SELECT users.fullname, GROUP_CONCAT(sites.site ORDER BY sites.site SEPARATOR ', ') 
FROM sites 
LEFT JOIN users ON sites.nameid = users.id
GROUP BY users.id;

This outputs:

John Doe     | site A, site B, site C
Roger Rabbit | site D, site E
Batman       | site F

To get exactly the result you want, I would do this in my PHP script.

answered May 31, 2016 at 18:36

Lord Grosse Jeanine

1,14110 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Xyz Over a year ago

I've edited the question and added the php above...sorry about that, should have done it in the first place.

Lord Grosse Jeanine Over a year ago

You should be able to get what you want with the GROUP_CONCAT function applied on task (with ', ' as separator), and GROUP BY applied on users and sites. Just give it a try using my previous answer and let us know if you run into big troubles. ^^

ugali soft · Accepted Answer · 2016-05-31 18:22:25Z

0

There must be a primary key column such as USER ID from both two tables so as to execute the following Query

select a.site,b.fullname from site a, users b where a.id = b.id

answered May 31, 2016 at 18:22

ugali soft

2,78730 silver badges25 bronze badges

Collectives™ on Stack Overflow

SQL query displaying one column and multiple values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related