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 - Multiple Entries

Options
  • 30-11-2018 10:48am
    #1
    Diceicle
    Registered Users Posts: 1,799 ✭✭✭


    Hello,

    I would appreciate some help on the following SQL query:-

    I have a table - Employees

    Name || Office
    John || Dublin
    Mary || Cork
    Wayne || Kerry
    Sean || Dublin
    John || Louth
    Mary || Dublin

    How would you determine which employees have more than 1 office?


Comments

  • Options
    #2
    sheroman01
    Registered Users Posts: 4,650 ✭✭✭sheroman01


    I'll give you a hint...Select your names, with a count(office) and then you need to GROUP ;)


  • Options
    #3
    Blazer
    Registered Users Posts: 17,416 ✭✭✭✭Blazer


    Diceicle wrote: »
    Hello,

    I would appreciate some help on the following SQL query:-

    I have a table - Employees

    Name || Office
    John || Dublin
    Mary || Cork
    Wayne || Kerry
    Sean || Dublin
    John || Louth
    Mary || Dublin

    How would you determine which employees have more than 1 office?

    Use count.
    This is basic sql stuff. A quick google should show you the exact query to use.
    More complicated scripts will only return the names of employees with more than one office.


  • Options
    #4
    Pelvis
    Closed Accounts Posts: 1,758 ✭✭✭Pelvis


    If you only want to return those with more than 1 office, then you'd need a HAVING in there too.

    SELECT
    FROM
    GROUP BY
    HAVING

    Fill in the blanks...


  • Options
    #5
    Tweeter
    Registered Users Posts: 245 ✭✭Tweeter


    You have a database design problem which you should address first.
    You have duplicate employees and duplicate locations.
    You need to create 3 tables to avoid this, an employee table, a location table and an assignment table


  • Options
    #6
    Pelvis
    Closed Accounts Posts: 1,758 ✭✭✭Pelvis


    Tweeter wrote: »
    You have a database design problem which you should address first.
    You have duplicate employees and duplicate locations.
    You need to create 3 tables to avoid this, an employee table, a location table and an assignment table
    Okay, maybe getting a little ahead of yourself.


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


    Thanks for all the replies.
    They have been very helpful and have put me on the right path.

    If I wanted to output the 'duplicates' but in a more tabular style format

    Name Office1 Office2
    John Dublin Louth
    Mary Cork Dublin

    Would setting up cases be the optimum solution do you think.


  • Options
    #8
    amen
    Registered Users Posts: 2,781 ✭✭✭amen


    That's a bit more complicated. You are looking at using PIVOT


  • Options
    #9
    Brussels Sprout
    Registered Users Posts: 6,520 ✭✭✭Brussels Sprout


    Diceicle wrote: »
    If I wanted to output the 'duplicates' but in a more tabular style format

    Name Office1 Office2
    John Dublin Louth
    Mary Cork Dublin

    Would setting up cases be the optimum solution do you think.

    Join the table to itself with the join being on the basis of equal names but not equal offices. This is assuming that the Employees table only has the two columns mentioned above (joining on non-numeric values is kinda gross).


  • Options
    #10
    Berserker
    Closed Accounts Posts: 9,046 ✭✭✭Berserker


    Tweeter wrote: »
    You have a database design problem which you should address first.

    Doubt he or she designed it.


  • Options
    #11
    ELM327
    Registered Users Posts: 21,373 ✭✭✭✭ELM327


    Brussels Sprout wrote: »
    Join the table to itself with the join being on the basis of equal names but not equal offices. This is assuming that the Employees table only has the two columns mentioned above (joining on non-numeric values is kinda gross).
    That's a very messy join, as a rule you should only join on unique numeric ID imo.


  • Advertisement
Advertisement