SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    267
    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,509
    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
    267
    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,341
    Mentioned
    63 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
    267
    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,341
    Mentioned
    63 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"


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
  •