SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    duplicate record

    Hi the below query returns the records i want but I also need it to return distinct values based upon one field.

    I am using MSAccess

    Code:
    SELECT videovault.Path, ColourType.ColourPath, videovault.EffectType
    FROM ColourType
    INNER JOIN VideoVault ON ColourType.ColourName = VideoVault.EffectType
    WHERE videovault.productCode = 'J72673'
    There is a field called EffectType that needs to be distinct no matter what.

    I have tried the distinct clause and group by function but it still returns the records with the field EffectType having duplicate entries.

    Any ideas on what to do please?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by robert475 View Post
    Any ideas on what to do please?
    yes, i do, but before we get into it, could you give some sample data for each table please

    i will be asking you how to decide which values you want for the duplications

    if you don't really care, then you can simply run this --
    Code:
    SELECT MIN(VideoVault.Path) AS Path
         , MAX(ColourType.ColourPath) AS ColourPath
         , VideoVault.EffectType
      FROM VideoVault
    INNER
      JOIN ColourType
        ON ColourType.ColourName = VideoVault.EffectType
     WHERE VideoVault.productCode = 'J72673'
    GROUP
        BY VideoVault.EffectType
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, i do, but before we get into it, could you give some sample data for each table please

    i will be asking you how to decide which values you want for the duplications

    if you don't really care, then you can simply run this --
    Code:
    SELECT MIN(VideoVault.Path) AS Path
         , MAX(ColourType.ColourPath) AS ColourPath
         , VideoVault.EffectType
      FROM VideoVault
    INNER
      JOIN ColourType
        ON ColourType.ColourName = VideoVault.EffectType
     WHERE VideoVault.productCode = 'J72673'
    GROUP
        BY VideoVault.EffectType
    Hi

    Here is some sample data from each table:

    Code:
    VideoVault.Path:
    assets/video/Fireballspiral/fireballSpiral2.swf
    
    ColourType.ColourPath:
    assets/colour/red.png
    
    videovault.EffectType:
    Red
    The query i posted returns the necessary records except it returns duplicate records for the field ColourType.ColourPath and videovault.EffectType.

    For example two records that will display red in the field videovault.EffectType which means ColourType.ColourPath will display the absolute path to the red image button twice.

    I hope this makes sense what I am trying to say.

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i understand that you're having a problem but you haven't given enough information to solve it

    did you try my query with the MIN and MAX?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Yes I have tried it with your query now and it seems to work.

    I am going to test it further against more products in the database. Where did I go wrong? was it with the group by clause or min and max.

    I spent all of yesterday trying different combos of queries and still couldnt get it

    Sorry about the lack of information i must of misunderstood what you meant. Did you mean a record containing all fields from the tables in the query?

    Thanks for your help, was there another query you was thinking of?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what i was looking for was to see actual sample data that would illustrate the duplicates problem

    if the query i gave you works for you, then that's good, yes?
    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
  •