Announcement

Collapse
No announcement yet.

Group by with WHERE or HAVING?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Group by with WHERE or HAVING?

    Scenario... i have a DB of music files with multiple songs from one artist... i want to list out all the songs and group them by the artist name.

    When I include "HAVING" it just returns one value... WHen I just have "GROUP BY", then the artist name is repeated.... any suggestions?

    SELECT a.filename, a.title, a.year, a.country, a.artistid, b.artistid, b.name
    FROM avroom a, artists b
    GROUP BY b.name
    // HAVING a.artistid = b.artistid
    ORDER BY b.name ASC LIMIT 0 , 30

  • #2
    Hrrrm. Try:

    "SELECT DISTINCT a.filename, a.title, a.year, a.country, a.artistid, b.artistid, b.name
    FROM avroom a, artists b
    ORDER BY b.name ASC LIMIT 0 , 30
    Pluh.com (random nonsense) - Psychostick.com (my stupid band) - Collectiveunderground.com (AZ local music site) - Evacuatechicago.com

    Comment


    • #3
      Code:
      SELECT a.filename, a.title, a.year, a.country, a.artistid, b.name
      FROM avroom a
      LEFT JOIN artists b ON (avroom.artistid = artists.artistid)
      ORDER BY b.name AST LIMIT 0,30
      Maybe?

      I dont see why you need group by

      Comment


      • #4
        What merk said is probably it. Whatever you do, though, you don't need to group the actual query - the grouping will have to be done in PHP or whatever programming language you are using.
        Chen Avinadav
        Better to remain silent and be thought a fool than to speak out and remove all doubt.

        גם אני מאוכזב מסיקור תחרות לתור מוטור של NRG הרשת ע"י מעריב

        Comment

        widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
        Working...
        X