SQL Question ... should I use a Group By???

Question

I have a situation where I need to group data by a portfolio name, then sort through the data and display check boxes. Here's the table structure.

Table A
--------
portfolio_id
portfolio_name

Table B
-------
file_id
portfolio_id (Foreign Key fk_port_id references portfolio_id in table A)
file_name

Basically Table A links to table B in a "one-to-many" relationship.

Here's what i want to be able to do and years ago I was able to accomplish it with an informix database, but I don't remember how to do it or even if its possible with MySQL. I'm using php and mysql. I want to use SQL to group the filenames (table b) under their respective portfolio (table A).

So, for my output I want it to look like:

 **Portfolio 1**
      Jack's File
      Robyn's File
 **Portfolio 2**
      Joey's file
 **Portfolio 3**
      John's File

Is there some SQL I can use that would group the files underneath the portfolio, then allow me to use PHP to loop through the "GROUPS" and display the data?

I tried using two different loops one for the portfolio, then inside that using another loop to get all the files by doing an "if" statement to compare if it belonged to the portfolio, but I would prefer to find out if I can get it out of MySQL first.

When I used Informix, I did something like a Group By in the sql, then in the shell script I used a foreach "Group By Header" to get the name of the "Group", then used something like "Group By Data" to display contents of the data that belonged to the Header....obviously my informix syntax isn't correct, ...it was a great feature...can I accomplish this with MySQL, or something like it?

Thanks.

Answer 1

There is no vanilla SQL construct that will do this for you naturally. My advice would be to pull out the rows in a standard statement:

Portfolio 1, Jack's file
Portfolio 1, Robyn's File
Portfolio 2, Joey's file
...

and then implement the break condition you want in code. In PHP this would look something like:

...
$prevPortfolio = '';
while ($row = mysql_fetch_assoc($query)) {
  $portfolio = $row['portfolio'];
  $file = $row['file'];
  if (prevPortfolio != $portfolio) {
    echo "**$portfolio**\n";
  }
  echo "  $file\n";
  $prevPortfolio = $portfolio;
}

Answer 2

This isn't what GROUP BY is for.

GROUP BY is for computing summaries (sums, averages, counts, etc.)

You have an ordinary nested query between table A and table B.

Answer 3

Just select all the data elements and loop through for each portfolio_id. Then output portfolio_id, and file info using comparison logic in PHP.

SELECT a.portfolio_name, b.file_id, b.file_name 
FROM a, b 
WHERE a.portfolio_id = b.portfolio_id

No GROUP BY needed.

Answer 4

From your description it appears that you're already doing something similar to what has been suggested in other answer, what you may be missing is ensuring that you have the data in the correct order to allow your php loops to succeed...

SELECT
   [A].portfolio_name,
   [B].file_name
FROM
   [A]
INNER JOIN
   [B]
      ON [B].portfolio_id = [A].portfolio
ORDER BY
   [A].portfolio_name,
   [B].file_name

If for any reason your data has duplicates, then you can use GROUP BY to eliminate those, or you could use DISTINCT, but I tend to use GROUP BY...

SELECT
   [A].portfolio_name,
   [B].file_name
FROM
   [A]
INNER JOIN
   [B]
      ON [B].portfolio_id = [A].portfolio
GROUP BY
   [A].portfolio_name,
   [B].file_name
ORDER BY
   [A].portfolio_name,
   [B].file_name

Answer 5

Thanks for all your input guys. I ended up modifying my existing loop with some of your suggestions to make it work right. I really didn't want to do all that code, but as you have pointed out that was the best way in php to accomplish it. Thanks for your help.