Need a Selective GROUP BY in My Query

Databases
1

Is there a way to write a GROUP BY command that doesn’t affect certain rows? I’ve discovered that my information displays best if I group the values in the field “Symbol” (GROUP BY G3.Symbol), where Symbol is simply the name of a state symbol (e.g. robin, moose, brook trout, etc.).

However, it doesn’t work for symbols that have no names - flags and state seals. Neither one has a value in the Symbol field, and therefore they’re treated as equal values, which means only the first one (flag) displays.

So is there some way to say GROUP BY G3.Symbol…unless G2.DesigGroup = ‘Vex’?

Thanks.


$res = mysql_query ("SELECT G2.N, G2.IDArea GID, G2.URL, G2.PostURL,
   G2.Title, G2.MyKind2, G2.DesigGen, G2.DesigGroup,
   G3.N, G3.URL, G3.Symbol, G3.Desig, G3.Latin, G3.Date
   FROM gs2 G2
   LEFT JOIN gs3 AS G3 ON G3.URL = G2.URL
   WHERE G2.IDArea = '$MyID' AND G2.MyKind2 = 3
   GROUP BY G3.Symbol ORDER BY G2.N") or die (mysql_error());
2

You may need HAVING but I can’t tell. Something like:

...
GROUP BY G3.Symbol
HAVING G2.DesigGroup <> 'Vex'

Add the CREATE TABLE structure so we can understand what you need better.

3

Ah, but the way you extract your data and the way you display it are two different things.
Can you explain what the query should do, and give us an example of the data in the tables and how you want to display it?

4

Sorry, this is the same table I asked about at http://www.sitepoint.com/forums/showthread.php?968724-Need-a-static-(non-repeating)-row-in-a-while-loop&p=5310817#post5310817

I was just moving on to another problem I’m trying to solve, but it sounds like I should wait until the first question is resolved. It looks like I’m going to have to make some changes in my database table design. :wink:

5

may we see this table design?

6

Sure, what’s the best way to do that - echo var_export($row, 1); or var_dump($row); …or is there another command I can type into SQL Query to display the design?

It’s actually three primary tables. The first table simply displays a single column of URL’s I use to create dynamic pages (with no duplicate URL’s). The second table includes the same URL field, along with a few other fields adding basic additional information. The third table includes a little more information and lists EVERY URL, even duplicates. For example, if a state has two state songs, they would require two rows, even if they’re displayed at the same URL (State/Song).

7

yes :slight_smile:

SHOW CREATE TABLE [I]tablename[/I]
8

Thanks. This is the first table, which does nothing but display URL’s to display dynamic pages…


CREATE TABLE `gs1` (
 `N` mediumint(4) NOT NULL AUTO_INCREMENT,
 `URL` varchar(75) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 PRIMARY KEY (`N`),
 KEY `URL` (`URL`)
) ENGINE=MyISAM AUTO_INCREMENT=8942 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

This is the second table. Like the first, it doesn’t contain any duplicate URL’s.


CREATE TABLE `gs2` (
 `N` mediumint(4) NOT NULL AUTO_INCREMENT,
 `IDArea` varchar(7) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `URL` varchar(75) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `PostURL` varchar(44) COLLATE latin1_general_ci DEFAULT NULL,
 `Title` varchar(75) COLLATE latin1_general_ci DEFAULT NULL,
 `MyKind2` tinyint(1) NOT NULL DEFAULT '0',
 `DesigGen` char(25) COLLATE latin1_general_ci DEFAULT NULL,
 `DesigGroup` varchar(7) COLLATE latin1_general_ci NOT NULL,
 `Date` char(4) COLLATE latin1_general_ci DEFAULT NULL,
 PRIMARY KEY (`N`),
 KEY `IDArea` (`IDArea`),
 KEY `URL` (`URL`),
 KEY `Title` (`Title`)
) ENGINE=MyISAM AUTO_INCREMENT=6165 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

And this is the third table, which includes ALL URL’s, including duplicates:


CREATE TABLE `gs3` (
 `N` mediumint(4) NOT NULL AUTO_INCREMENT,
 `URL` varchar(75) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `Symbol` text COLLATE latin1_general_ci,
 `Desig` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
 `Latin` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
 `Date` char(4) COLLATE latin1_general_ci DEFAULT NULL,
 PRIMARY KEY (`N`),
 KEY `URL` (`URL`),
 KEY `Title` (`Desig`)
) ENGINE=MyISAM AUTO_INCREMENT=5937 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

There may be a few redundant fields, because I haven’t yet figured out which table would be the best for a particular field - or I may move that field into a fourth table.

Thanks.

9

thanks, not sure i understand all your columns, but could you now please explain why you need to collapse multiple rows with GROUP BY?

10

Yikes - I think you just shot down my entire question (or solved it). I deleted the GROUP BY clause, and it seems to display just fine. I guess that must have been left over from an earlier query, and I didn’t even realize it’s no longer serving its original purpose.

I think my table’s actually displaying better than before. Now I’ll start deleting any duplicate fields I don’t need.

I started working with MySQL several years ago but haven’t done much with it for some time. I now understand it a little better, and things are starting to fall into place.

Thanks!