Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    May 2008
    Location
    Michigan
    Posts
    66
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Is There A Way To Remove Similar Results From Query?

    For example, if I want to select all bands from my "band" table that begin with the letter 'N' - several of these bands have multiple albums in the table. To see these albums, so you know where I'm going with this, we'll do a simple SELECT:

    Code:
    SELECT bandName,bandAlbum FROM bands WHERE bandName LIKE 'N%' ORDER BY bandName ASC;
    What I get is:

    Code:
    Null Factor - Calculating The Human Form
    Null Factor - Human Analog
    Null Factor - Purity
    I want to just display the bands that begin with 'N' without the album names (just to get a list of bands in this section). To do this, I do a MySQL query:

    Code:
    SELECT bandName FROM bands WHERE bandName LIKE 'N%' ORDER BY bandName ASC;
    Since my result "Null Factor" has 3 albums in the database, I get the result:

    Code:
    Null Factor
    Null Factor
    Null Factor
    What I am aiming to do is to display each band name ONCE, even if they have multiple albums listed in the table. Is this possible? If so, how do I go about doing so?

    Thanks in advance!
    Last edited by dharvell; 01-06-2009 at 02:56 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Handle it in your front end application, see this article for clarification.

  • #3
    New Coder
    Join Date
    May 2008
    Location
    Michigan
    Posts
    66
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks for the reply, GUELPHDAD, but I found a MUCH easier solution after about an hour of thumbing through Google results... Simply add a GROUP BY in my query:

    Code:
    SELECT bandName FROM bands WHERE bandName LIKE 'N%' GROUP BY bandName ORDER BY bandName ASC;
    Apparently, the GROUP BY will eliminate the repeating band names... just what I was hoping for!

    Thanks anyway.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    the reason I didn't tell you to use a GROUP BY is you seemed to be looking at getting more than one column out of your SELECT statement. I realize in the one example you only asked for band names, but if you expand on that and ask for band names and then albums then using the GROUP BY clause is incorrect.

    you don't even need the GROUP BY if you are only selecting one column, use DISTINCT instead, GROUP BY is used when you want to perform an aggregate function on a group of like data (all your bands you added in March for example).

    If you use the GROUP BY when selecting more than one column you can run into incorrect results.


  •  

    Posting Permissions

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