Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL Query - Find Duplicates

Options
  • 02-05-2018 11:45am
    #1
    Diceicle
    Registered Users Posts: 1,799 ✭✭✭


    I have an SQL problem I'm trying to solve.

    I have a table of records and need to find duplicates based off of 2 areas.
    For example:

    Region | Product
    Leinster | Tyres
    Leinster | Brakes
    Leinster | Windscreen
    Leinster | Windscreen
    Munster | Brakes
    Munster | Windscreen
    Munster | Tyres
    Ulster | Tyres
    Ulster | Tyres
    Ulster | Windscreen
    Connaught| Brakes
    Connaught| Brakes
    Connaught| Windscreen
    Connaught| Tyres
    Connaught| Aerial

    I would like to return distinct matching products for 2 specific areas (lets say, Leinster and Connaught)

    Whats the most effective way to achieve this?

    Thanks in advance.


Comments

  • Options
    #2
    Sesame
    Registered Users Posts: 508 ✭✭✭Sesame


    Diceicle wrote: »
    I have an SQL problem I'm trying to solve.

    I have a table of records and need to find duplicates based off of 2 areas.
    For example:

    Region Product
    Leinster Tyres
    Leinster Brakes
    Leinster Windscreen
    Leinster Windscreen
    Munster Brakes
    Munster Windscreen
    Munster Tyres
    Ulster Tyres
    Ulster Tyres
    Ulster Windscreen
    Connaught Brakes
    Connaught Brakes
    Connaught Windscreen
    Connaught Tyres
    Connaught Aerial

    I would like to return distinct matching products for 2 specific areas (lets say, Leinster and Connaught)

    Whats the most effective way to achieve this?

    Thanks in advance.

    Select product from tablename where region in ('leinster', 'munster')

    Would that be it?


  • Options
    #3
    Sesame
    Registered Users Posts: 508 ✭✭✭Sesame


    Sesame wrote: »
    Select product from tablename where region in ('leinster', 'munster')

    Would that be it?

    For distinct lines put the word distinct after select


  • Options
    #4
    Diceicle
    Registered Users Posts: 1,799 ✭✭✭Diceicle


    I thought I would need to do a sub-query?


  • Options
    #5
    Sesame
    Registered Users Posts: 508 ✭✭✭Sesame


    I may have misunderstood, what do you mean find duplicates?


  • Options
    #6
    igCorcaigh
    Moderators, Recreation & Hobbies Moderators Posts: 11,161 Mod ✭✭✭✭igCorcaigh


    SELECT
    Region, Product, COUNT(*)
    FROM
    Products
    GROUP BY
    Region, Product
    HAVING
    COUNT(*) > 1


  • Advertisement
  • Options
    #7
    Diceicle
    Registered Users Posts: 1,799 ✭✭✭Diceicle


    Sesame wrote: »
    I may have misunderstood, what do you mean find duplicates?

    From the table above and comparing records for Leinster and Connaught - I would expect/want to return :

    Region | Product
    Leinster | Tyres
    Leinster | Brakes
    Leinster | Windscreen
    Leinster | Windscreen
    Munster | Brakes
    Munster | Windscreen
    Munster | Tyres
    Ulster | Tyres
    Ulster | Tyres
    Ulster | Windscreen
    Connaught| Brakes
    Connaught| Brakes
    Connaught| Windscreen
    Connaught| Tyres
    Connaught| Aerial

    Product
    Brakes
    Windscreen
    Tyres


    But not Aerial


  • Options
    #8
    igCorcaigh
    Moderators, Recreation & Hobbies Moderators Posts: 11,161 Mod ✭✭✭✭igCorcaigh


    SELECT
    id, Region, Product
    FROM
    Products
    where id in
    (
    select id
    from Products p2
    where p2.Region = Region
    and p2.Procuct = Product
    and not p2.id = id
    )


  • Options
    #9
    diomed
    Closed Accounts Posts: 4,744 ✭✭✭diomed


    Do you want to do anything with the duplicates?


Advertisement