1

I work for the IT support desk of a particular organisation. Coding is something I don't do professionally but am happy to broaden my skills a little. Any help here would be gladly appreaciated!!

We've recently had to dole out lots of new iPhones, as the older standard model couldn't run the newest version of iOS (and therefore a certain app).

I look after one particular location, my colleagues tend to the other locations. I would like to see if we can find out which staff members who have not yet returned their original device. Dozens and dozens of tickets for replacements combined with most staff working from home means devices can be hard to track down using the front-end of the database.

The main columns are the "Employee_Name", "Device_Name" (e.g iPhone 5C), "Device_Serial" and "Status". The two states that a device can have for this particular column are either "With employee" or "With help desk"

Here is some SQL code use to create a sample table...

    CREATE TABLE IF NOT EXISTS helpdesk (
    `Employee_Name` VARCHAR(11) CHARACTER SET utf8,
    `Device_Name` VARCHAR(9) CHARACTER SET utf8,
    `Device_Serial` VARCHAR(9) CHARACTER SET utf8,
    `Status` VARCHAR(14) CHARACTER SET utf8
     );

    INSERT INTO helpdesk VALUES
    ('John Murphy','Apple iPhone SE','QWERTY123','With employee'),
    ('John Murphy','iPhone 8','TREWQ345','With employee'),
    ('IT Service','iPhone 6S','GHJKKJ45','With help desk'),
    ('Tammy Top','iPhone X','GFDSDC423','With employee'),
    ('Bugs Bunny','Brother Printer','TYU1267','With employee'),
    ('IT Service','iPhone XR','FDSASF456','With help desk');

I only want to get returns for employees that are currently in possession of TWO or more iPhones. Here "John Murphy" would need to be returned, but not "Tammy Top" (as she has only got one). Any entries with the employee name 'IT Service' can be left out - these items are already back with the IT desk.

Very glad of any help given!

1 Answer 1

0

As you can see the WHERE clause removes all rows that are not valid for the purpose

In your case

  • Employee_Name can't be 'IT Service'
  • Device_Name has to be an '%IPHONE%'
  • Status has to be 'With employee'

The Rest is a GROUP BY AND HAVING

    CREATE TABLE IF NOT EXISTS helpdesk (
    `Employee_Name` VARCHAR(11) CHARACTER SET utf8,
    `Device_Name` VARCHAR(20) CHARACTER SET utf8,
    `Device_Serial` VARCHAR(9) CHARACTER SET utf8,
    `Status` VARCHAR(14) CHARACTER SET utf8
     );
    INSERT INTO helpdesk VALUES
    ('John Murphy','Apple iPhone SE','QWERTY123','With employee'),
    ('John Murphy','iPhone 8','TREWQ345','With employee'),
    ('IT Service','iPhone 6S','GHJKKJ45','With help desk'),
    ('Tammy Top','iPhone X','GFDSDC423','With employee'),
    ('Bugs Bunny','Brother Printer','TYU1267','With employee'),
    ('IT Service','iPhone XR','FDSASF456','With help desk');
SELECT `Employee_Name` 
FROM helpdesk 
WHERE `Employee_Name`  <> 'IT Service'
    AND `Device_Name`  LIKE '%IPHONE%'
    AND `Status` = 'With employee'
GROUP BY `Employee_Name`
HAVING COUNT(*) > 1
| Employee_Name |
| :------------ |
| John Murphy   |

db<>fiddle here

1
  • Excellent. That worked perfectly! Thanks. Commented Jul 4, 2021 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.