SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need a Selective GROUP BY in My Query

    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.

    Code:
    $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. #2
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    You may need HAVING but I can't tell. Something like:
    Code:
    ...
    GROUP BY G3.Symbol
    HAVING G2.DesigGroup <> 'Vex'
    Add the CREATE TABLE structure so we can understand what you need better.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Chavista View Post
    I've discovered that my information displays best if I group the values in the field "Symbol"
    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. #4
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, this is the same table I asked about at http://www.sitepoint.com/forums/show...17#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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Chavista View Post
    It looks like I'm going to have to make some changes in my database table design.
    may we see this table design?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Chavista View Post
    is there another command I can type into SQL Query to display the design?
    yes
    Code:
    SHOW CREATE TABLE tablename
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. This is the first table, which does nothing but display URL's to display dynamic pages...

    Code:
    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.

    Code:
    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:

    Code:
    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. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    thanks, not sure i understand all your columns, but could you now please explain why you need to collapse multiple rows with GROUP BY?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •