+ Post New Thread
Results 1 to 11 of 11
Coding Thread, SQL Guru's I Need some guidance ( OR GLPI HELPDESK!! ) in Coding and Web Development; SQL Query help. We want to auto generate a refreshing webpage to show our NEW un-assigned helpdesk tickets. There is ...
  1. #1

    twin--turbo's Avatar
    Join Date
    Jun 2012
    Location
    Carlisle
    Posts
    2,334
    Thank Post
    1
    Thanked 381 Times in 340 Posts
    Rep Power
    150

    SQL Guru's I Need some guidance ( OR GLPI HELPDESK!! )

    SQL Query help.

    We want to auto generate a refreshing webpage to show our NEW un-assigned helpdesk tickets.

    There is a table in GLPI helpdesk

    "glpi_tickets_users"

    this contains a list fo ID's

    Each ID has a ticket number and a user type ( type 2 semes to be a supporter )

    Snippet example
    ID -- TICKET -- USER -- TYPE
    1900 -- 50 ------- 10 ----- 1
    1901 -- 50 ------- 05 ----- 2
    1902 -- 51 ------- 11 ----- 1
    1903 -- 55 ------- 11 ----- 1
    1904 -- 55 ------- 04 ----- 2

    ticket 50 has 2 users ( 5 and 10) a supporter and a normal user
    ticket 51 has 1 users ( 11 ) a normal user
    ticket 55 has 2 users ( 4 and 11) a supporter and a normal user

    I need an sql query that will pull out just those tickets with no type 2 user ( supporter)

    My SQL knowledge has deminished considerably over the last 20 years since uni...

    Anyone help with a query????


    Cheers

    Rob
    Last edited by twin--turbo; 23rd November 2012 at 02:00 PM.

  2. #2

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,816
    Thank Post
    272
    Thanked 1,138 Times in 1,034 Posts
    Rep Power
    350
    Depending on how you want to build it and display (i guess in an array using something like for each??)

    select * from ID where type = 2

    You would then need to put it into an array for it to display a row with each.

  3. #3
    detjo's Avatar
    Join Date
    Feb 2008
    Posts
    356
    Thank Post
    13
    Thanked 48 Times in 39 Posts
    Rep Power
    31
    "with no type 2 user" ??

    or WHERE type = 1 (if you dont want type 2 users)

  4. #4

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,816
    Thank Post
    272
    Thanked 1,138 Times in 1,034 Posts
    Rep Power
    350
    Quote Originally Posted by detjo View Post
    "with no type 2 user" ??

    or WHERE type = 1 (if you dont want type 2 users)
    Ah my bad! yes WHERE type = 2

  5. #5

    tmcd35's Avatar
    Join Date
    Jul 2005
    Location
    Norfolk
    Posts
    5,693
    Thank Post
    852
    Thanked 899 Times in 744 Posts
    Blog Entries
    9
    Rep Power
    329
    Is there only 2 types of user? If you want to return tickets from all possible types other than type 2 it would be...

    SELECT * FROM glpi_tickets_users WHERE TYPE != 2

  6. #6

    twin--turbo's Avatar
    Join Date
    Jun 2012
    Location
    Carlisle
    Posts
    2,334
    Thank Post
    1
    Thanked 381 Times in 340 Posts
    Rep Power
    150
    I know the baisc query.

    I need to pull out tickets with no type 2 user.

    SELECT * FROM glpi_tickets_users WHERE TYPE != 2 (or any of the other sugestions)

    would result in

    1900 -- 50 ------- 10 ----- 1
    1902 -- 51 ------- 11 ----- 1
    1903 -- 55 ------- 11 ----- 1

    which is incorrect as tickets 50 & 55 do have a type 2 user.

    the only result should be

    1902 -- 51 ------- 11 ----- 1

    as this is the only ticket that does not have a type 2 user.

    Rob

  7. #7

    tmcd35's Avatar
    Join Date
    Jul 2005
    Location
    Norfolk
    Posts
    5,693
    Thank Post
    852
    Thanked 899 Times in 744 Posts
    Blog Entries
    9
    Rep Power
    329
    CREATE VIEW numTwoIDs AS SELECT ID,TYPE FROM glpi_tickets_users WHERE TYPE = 2
    SELECT * FROM glpi_tickets_users, numTwoIDs WHERE ID != numTwoIDs.ID
    Not sure that's 100% correct, but I think it's the basic principle.
    Last edited by tmcd35; 23rd November 2012 at 03:16 PM.

  8. #8

    twin--turbo's Avatar
    Join Date
    Jun 2012
    Location
    Carlisle
    Posts
    2,334
    Thank Post
    1
    Thanked 381 Times in 340 Posts
    Rep Power
    150
    tried to do that just as I was leaving but it seemd to fail.. May haveanother go on Monday ... However lateral thinking may have solved it.

    select ticket,sum(type) as utp from glpi_tickets_users group by ticket;

    Gets me a sum of the user type per ticket.

    So
    a ticket from a user will be "1"
    a ticket with no user opend by a supporter will be "2"
    a ticket in progress with a user and supporter will be "3" or more with extra users.

    So if we wrap that query to find just the "1"

    select * from ( select ticket,sum(type) as utp from glpi_tickets_users group by ticket) as jobs where utp = 1;

    returns just the jobs with an end user

    Job Jobed hopefully.

    Rob

  9. #9

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    22
    Cant test it but this looks right to me:

    Code:
    SELECT glpi_tickets_users.*
    FROM glpi_tickets_users
    LEFT OUTER JOIN (
        SELECT DISTINCT ticket
        FROM glpi_tickets_users
        WHERE TYPE = 2
    ) TicketsWithType2User
    ON glpi_tickets_users.ticket = TicketsWithType2User.ticket
    WHERE (TicketsWithType2User.ticket IS NULL)

  10. #10
    detjo's Avatar
    Join Date
    Feb 2008
    Posts
    356
    Thank Post
    13
    Thanked 48 Times in 39 Posts
    Rep Power
    31
    Quote Originally Posted by ChrisMiles View Post
    Cant test it but this looks right to me:

    Code:
    SELECT glpi_tickets_users.*
    FROM glpi_tickets_users
    LEFT OUTER JOIN (
        SELECT DISTINCT ticket
        FROM glpi_tickets_users
        WHERE TYPE = 2
    ) TicketsWithType2User
    ON glpi_tickets_users.ticket = TicketsWithType2User.ticket
    WHERE (TicketsWithType2User.ticket IS NULL)
    Tested and working

  11. #11

    Join Date
    Jun 2007
    Location
    Uttoxeter, Staffordshire
    Posts
    18
    Thank Post
    0
    Thanked 4 Times in 4 Posts
    Rep Power
    0
    Much easier solution :

    SELECT * FROM glpi_tickets_users WHERE type = 1 AND id NOT IN (SELECT id FROM glpi_tickets_users WHERE type = 2)

    Hope I'm not too late to the party! Edit : just looked at the date, November 2012 - oops.
    Last edited by gavcradd; 7th March 2013 at 07:50 AM.

SHARE:
+ Post New Thread

Similar Threads

  1. Xirrus or Ruckus.. need some help from people with the know how
    By luke240778 in forum Wireless Networks
    Replies: 3
    Last Post: 25th July 2011, 11:16 PM
  2. Replies: 14
    Last Post: 5th August 2010, 02:25 PM
  3. Drivers needed: some help please
    By contink in forum General Chat
    Replies: 4
    Last Post: 14th March 2007, 03:47 PM
  4. Need some Coding
    By russdev in forum Web Development
    Replies: 5
    Last Post: 11th November 2006, 10:49 PM
  5. Replies: 14
    Last Post: 7th September 2006, 01:14 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •