SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Bringing back no duplicates from mysql

    I have a database with records similar to below:

    Code:
    ID   | Resource Type ID | Name
    -----------------------------------
    1    | 1                | Record 1
    2    | 3                | Record 1
    3    | 7                | Record 1
    4    | 1                | Record 2
    5    | 5                | Record 3
    6    | 5                | Record 3
    I need to bring back the results of this database, however if there are 3 "Record 1" results as you can see above, it only brings back the one,

    The results I want are as follows:
    Code:
    Name
    ------
    Record 1
    Record 2
    Record 3
    I can't use the "select distinct" as the resource type id is different therefore making the record different. is this possible??

    [ I do realise that I should have created a link table in between the resource type table and the resource table however I have come into the project where it is already done like this and we are limited with time ]

    Cheers

    Kev
    Last edited by KevinM2k; Mar 30, 2005 at 06:07.

  2. #2
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select distinct `name` from `table`

  3. #3
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I think that it can be solved by using GROUP BY:

    Code:
    SELECT name FROM table GROUP BY name
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  4. #4
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    distinct would be good if i could say

    select * from resources where (Name is Distinct)

    As I need more information returned than just name.

    Kev

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    either of the above two solutions will give you distinct names

    however, if you want any other column data to go along with the distinct names, then you will need something else

    for example, if you want ResourceTypeID as well, then you have to have a rule to determinie which ResourceTypeID value you want for each name

    here's an example to get the highest ResourceTypeID for each name --
    Code:
    select max(ResourceTypeID) as maxResourceTypeID 
         , Name
      from yourtable
    group
        by Name
    but now if you also want the ID that goes along with the highest ResourceTypeID, it gets even more complicated --
    Code:
    select ID
         , ResourceTypeID as maxResourceTypeID 
         , Name
      from yourtable as X
     where ResourceTypeID
         = ( select max(ResourceTypeID)
               from yourtable
              where Name = X.Name )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there are 2 fields I need returning from the table:

    national_document_name, national_document_link

    the structure of my mysql database is

    national_document_ID, (UNIQUE KEY)
    rsa_id, (Resource Area ID)
    rst_id, (Resource Type ID)
    national_document_name, (Document Name)
    national_document_link (The link to the document)

    The first post shows an example of entries into the database.

    I don't want any duplicate entries to show up as I'm only showing the document_name which can be clicked on which links to the document_link.

  7. #7
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Thanx everyone, I managed to get it working with the following sql:

    SELECT DISTINCT * FROM [table name] GROUP BY national_document_name ORDER BY national_document_name ASC

    I'll have to look up "GROUP BY".

    Cheers

    Kevin

  8. #8
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I think that the following should give the desired result:

    Code:
    SELECT national_document_name
         , national_document_link
    FROM table_name
    GROUP BY national_document_name
    Edit:

    I didn't see your reply until now... I'm always too late.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    kevin, how is it possible that the same national_document_name can have more than one national_document_link?

    and if it is possible, how do you decide which link you want for each name?????


    please be aware that GROUP BY has certain rules and among them is that every non-aggregate in the SELECT list must also be in the GROUP BY clause

    in particular this means that you cannot use GROUP BY if you write SELECT *

    mysql is the single exception, it allows the rule for GROUP BY to be broken with reckless abandon

    i caution you to learn how GROUP BY really works, or else you are in for some rude surprises if you ever try to write a GROUP BY in any other database

    yours is wrong too, lilleman :-)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT DISTINCT
          name, link
    FROM table_name
    returns all exisiting combinations of name and link. Dont know if this is portable...

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    stereofrog, that is extremely portable, every database will support that query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, I took what you had to say in, and i've used stereofrog's example and got it working with the following:

    Code:
    SELECT DISTINCT national_document_name, national_document_link FROM [table name] ORDER BY national_document_name ASC LIMIT 0, 10
    Is this OK to use then or may I find other problems along the way??

    Cheers

    Kevin

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, that's fine

    have you examined the output? do you have any names which have more than one link?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by r937
    yours is wrong too, lilleman :-)
    Aww, crap. How would a correct version of my query look?
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  15. #15
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I've checked R937 and that is fine, it did bring the same number of results back as lilleman's but as you said, that way probably wasn't the correct way of doing things.

    Cheers

    Kevin

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by lilleman
    How would a correct version of my query look?
    that depends on which link you wanted for each name

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by r937
    that depends on which link you wanted for each name
    Then let us assume that a name can only have one link.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, that kinda goes against the entire thread, but if that's the case, then your query would be
    Code:
    SELECT national_document_name
         , national_document_link
    FROM table_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by r937
    well, that kinda goes against the entire thread [...]
    Yes, you're right. My bad.

    Quote Originally Posted by r937
    [...], but if that's the case, then your query would be
    Code:
    SELECT national_document_name
         , national_document_link
    FROM table_name
    Well, the thing I didn't understand (and I wanted an example of) is the part you said about GROUP BY, and that all non-aggregates must be in it. Would this be correct to use, or am I still not understanding how it's done?

    Code:
    SELECT first_field
         , second_field
    FROM table_name
    GROUP BY first_field
           , second_field
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, examples

    the following are all good --

    select a ...
    select sum(m) ...
    select a ... group by a
    select a,sum(p) ... group by a
    select a,b ... group by a,b
    select a,b,sum(q) ... group by a,b

    the following are also good but not useful --

    select a ... group by a,b
    select a,sum(p) ... group by a,b
    select a,b ... group by a,b,c
    select a,b,sum(r) ... group by a,b,c

    the following are not good --

    select a,sum(p) ...
    select a,b ... group by a
    select a,b,sum(r) ... group by a

    mysql allows these, and in my opinion shouldn't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    well, that kinda goes against the entire thread, but if that's the case, then your query would be
    Code:
    SELECT national_document_name
         , national_document_link
    FROM table_name
    The name has only one link, but nobody said there is only one row for each name (== name-link combination). Therefore, DISTINCT is neccessary.

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if there is more than one row with the same name and link combination, then there is a problem, isn't there ...

    ... specifically, how that's possible when the document itself is unique

    or do i not understand the reason for the unique key in this table?
    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
  •