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

[twin--turbo]

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

Edited November 23, 2012 by twin--turbo

[glennda]

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.

[detjo]

"with no type 2 user" ??

 

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

[glennda]

"with no type 2 user" ??

 

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

 

Ah my bad! yes WHERE type = 2

[tmcd35]

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

[twin--turbo]

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

[tmcd35]

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.

Edited November 23, 2012 by tmcd35

[twin--turbo]

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

[ChrisMiles]

Cant test it but this looks right to me:

 

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)

[detjo]

Cant test it but this looks right to me:

 

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

[gavcradd]

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.

Edited March 7, 2013 by gavcradd