we have two entries for each user in USER table.  One user row with AD details other user row with email id details, I  want to pull both entries in USER_NAME column based on the FULL_NAME given   in ticketing page. since both entries will be having  same entries in FULL_NAME.

CwRlVT.jpeg
7 Comments   [ + ] Show Comments

Comments

  • select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
    HD_STATUS.NAME AS STATUS_NAME,
    HD_STATUS.ORDINAL as STATUS_ORDINAL,
    HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
    HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
    HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
    STATE,
    if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
    if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
    if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
    if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
    if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
    if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
    if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
    if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
    case upper(STATE)
    when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
    when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
    else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
    if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
    U1.FULL_NAME as OWNER_FULLNAME,
    U1.EMAIL as OWNER_EMAIL,
    if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
    if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
    U2.FULL_NAME as SUBMITTER_FULLNAME,
    U2.EMAIL as SUBMITTER_EMAIL,
    U2.WORK_PHONE as SUBMITTER_PHONE,
    if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
    if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
    if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
    Q.NAME as QUEUE_NAME
    from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
    LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
    LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
    LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
    LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID







    LEFT JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER SUBMITTER2 on SUBMITTER2.FULL_NAME = SUBMITTER.FULL_NAME







    where HD_PRIORITY.ID = HD_PRIORITY_ID
    and HD_STATUS.ID = HD_STATUS_ID
    and HD_IMPACT.ID = HD_IMPACT_ID
    and HD_CATEGORY.ID = HD_CATEGORY_ID
    and (( HD_TICKET.CUSTOM_FIELD_VALUE14 = 'Set on Save') and HD_TICKET.HD_QUEUE_ID = 16 );










    is this correct ?
    • Those are the correct join statements. If you want to use any of the column names in an email then you need to also add those to the select portion of the statement. The last column selected is Q.NAME as QUEUE_NAME. After that statement you would add the other columns you want to select, like SUBMITTER.FULL_NAME, SUBMITTER2.EMAIL, etc. The list is separated by commas.
  • what should be the Update query to pull the user details having two entries for each user but Full_name is common for both entries .???



    update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUE14 = (select USER_NAME FROM USER where SUBMITTER_NAME = SUBMITTER_ID) where
    T.ID = <TICKET_IDS>;


    below is the two entries for same user , I would like to pull both user_name entries based on FULL_NAME in ticketing page.


    ID USER_NAME EMAIL FULL_NAME

    2289 JohnAL john@fx.com JOHN AL

    40524 John@ABC.com John@ctx.com JOHN AL
    • I'm not sure what you want to set the custom field value to equal.
  • i want to set Custom value field to pull both USER_NAME ,since each user has AD and email details in USER_NAME column ,

    All users have two entries with common FULL_NAME --> "JOHN AL"

    when user is creating ticket i want his/her AD and email details ..since these are seperated in each row one with AD and other with email ID, both row have same FULL_NAME, i would like to pull two diff entries of same user using his FULL_NAME value
  • thanks alot for all ur help
  • 04/28/2016 00:47:30> Starting: 04/28/2016 00:47:30 04/28/2016 00:47:30> Executing Select Query... 04/28/2016 00:47:30> selected 6 rows 04/28/2016 00:47:30> Executing Update Query... 04/28/2016 00:47:30> mysqli error: [1054: Unknown column 'HD_TICKET.SUBMITTER_ID' in 'on clause'] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE7 = (SELECT GROUP_CONCAT(USER.USER_NAME) FROM USER JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME GROUP BY USER.FULL_NAME) WHERE HD_TICKET.ID in (119,120,121,119,120,121)") 04/28/2016 00:47:30> Ending: 04/28/2016 00:47:30
  • 04/28/2016 00:47:30> Starting: 04/28/2016 00:47:30 04/28/2016 00:47:30> Executing Select Query... 04/28/2016 00:47:30> selected 6 rows 04/28/2016 00:47:30> Executing Update Query... 04/28/2016 00:47:30> mysqli error: [1054: Unknown column 'HD_TICKET.SUBMITTER_ID' in 'on clause'] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE7 = (SELECT GROUP_CONCAT(USER.USER_NAME) FROM USER JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME GROUP BY USER.FULL_NAME) WHERE HD_TICKET.ID in (119,120,121,119,120,121)") 04/28/2016 00:47:30> Ending: 04/28/2016 00:47:30
  • I forgot that you can't reference the table you are updating in a sub select statement that way. I'll have to put some more thought into how this can be done.
Please log in to comment

Answers

0
This will require two joins to the USER table. Assuming that you are looking for information on the submitter the first join relates the submitter's ID to the USER table like this:
JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

Note that we are creating an alias for the USER table called SUBMITTER. We can now make a second relationship with the USER table based on the information in the table. I'm going to call this SUBMITTER2:
JOIN USER SUBMITTER2 on SUBMITTER2.FULL_NAME = SUBMITTER.FULL_NAME

Once you have these joins in your statement you can select fields based on those table names.

Answered 04/15/2016 by: chucksteel
Red Belt

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • Hi Chuck,

    Need help.. I want to pull all the USER_NAME details from USER table in ticketing page

    based on the SUBMITTER_FULLNAME... where Submitter name in ticketing page is similar to SUBMITTER_FULLNAME in USER table
Please log in to comment
0
I've been trying to think about the best way to do this and I think this would work.
UPDATE HD_TICKET
SET CUSTOM_FIELD_VALUE14 = 
(SELECT GROUP_CONCAT(USER_NAME) FROM USER 
JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID
WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME
GROUP BY USER.FULL_NAME)
WHERE HD_TICKET.ID in <TICKET_IDS>
This uses a sub-select statement that should return a comma separated list of user names that have the full name of the ticket submitter.



Answered 04/21/2016 by: chucksteel
Red Belt

  • USER table doesnt have any column named SUBMITTER
    • Correct. I'm creating an alias to the user table in the join statement. This allows you to refer to the table as SUBMITTER.
      • 04/26/2016 00:36:52> Starting: 04/26/2016 00:36:52 04/26/2016 00:36:52> Executing Select Query... 04/26/2016 00:36:52> selected 28 rows 04/26/2016 00:36:52> Executing Update Query... 04/26/2016 00:36:52> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '89,90,92,93,94,95,98,99,100,101,105,106,111,110,91,89,90,92,93,94,95,98,99,100,1' at line 7] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE18= (SELECT GROUP_CONCAT(USER_NAME) FROM USER JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME GROUP BY USER.FULL_NAME) WHERE HD_TICKET.ID in 89,90,92,93,94,95,98,99,100,101,105,106,111,110,91,89,90,92,93,94,95,98,99,100,101,105,106,111") 04/26/2016 00:36:52> Ending: 04/26/2016 00:36:52





        here is the query im using



        select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
        HD_STATUS.NAME AS STATUS_NAME,
        HD_STATUS.ORDINAL as STATUS_ORDINAL,
        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
        STATE,
        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
        case upper(STATE)
        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
        U1.FULL_NAME as OWNER_FULLNAME,
        U1.EMAIL as OWNER_EMAIL,
        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
        U2.FULL_NAME as SUBMITTER_FULLNAME,
        U2.EMAIL as SUBMITTER_EMAIL,
        U2.WORK_PHONE as SUBMITTER_PHONE,
        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
        Q.NAME as QUEUE_NAME
        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID







        LEFT JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
        LEFT JOIN USER SUBMITTER2 on SUBMITTER2.FULL_NAME = SUBMITTER.FULL_NAME



        where HD_PRIORITY.ID = HD_PRIORITY_ID
        and HD_STATUS.ID = HD_STATUS_ID
        and HD_IMPACT.ID = HD_IMPACT_ID
        and HD_CATEGORY.ID = HD_CATEGORY_ID
        and (( HD_TICKET.CUSTOM_FIELD_VALUE18 = 'Set on Save') and HD_TICKET.HD_QUEUE_ID = 16 );

        UPDATE Query

        UPDATE HD_TICKET
        SET CUSTOM_FIELD_VALUE18=
        (SELECT GROUP_CONCAT(USER_NAME) FROM USER
        JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID
        WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME
        GROUP BY USER.FULL_NAME)
        WHERE HD_TICKET.ID in <TICKET_IDS>
  • Sorry, I think there should be parentheses around the <TICKET_IDS>.
    WHERE HD_TICKET.ID in (<TICKET_IDS>)
    • same error


      04/26/2016 07:48:07> Starting: 04/26/2016 07:48:07 04/26/2016 07:48:07> Executing Select Query... 04/26/2016 07:48:07> selected 36 rows 04/26/2016 07:48:07> Executing Update Query... 04/26/2016 07:48:07> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT USER_NAME FROM USER JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITT' at line 3] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE18= SELECT USER_NAME FROM USER JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = HD_TICKET.SUBMITTER_ID WHERE HD_TICKET.ID in (,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,)") 04/26/2016 07:48:07> Ending: 04/26/2016 07:48:07
      • The update statement isn't correct. You are missing a set of parenthesis around the sub select and the where clause in the sub select is wrong. The full update statement should be:
        UPDATE HD_TICKET
        SET CUSTOM_FIELD_VALUE14 =
        (SELECT GROUP_CONCAT(USER_NAME) FROM USER
        JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID
        WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME
        GROUP BY USER.FULL_NAME)
        WHERE HD_TICKET.ID in (<TICKET_IDS>)

        Also, the HD_TICKET table only contains custom fields up to number 15 (CUSTOM_FIELD_VALUE14) and you are using custom field 19 (CUSTOM_FIELD_VALUE18). This update query won't work for fields that high.
      • thanks I have changed the custom field





        04/27/2016 01:11:06> Starting: 04/27/2016 01:11:06 04/27/2016 01:11:06> Executing Select Query... 04/27/2016 01:11:06> selected 6 rows 04/27/2016 01:11:06> Executing Update Query... 04/27/2016 01:11:06> mysqli error: [1052: Column 'USER_NAME' in field list is ambiguous] in EXECUTE("UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE7 = (SELECT GROUP_CONCAT(USER_NAME) FROM USER JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID WHERE USER.FULL_NAME = SUBMITTER.FULL_NAME GROUP BY USER.FULL_NAME) WHERE HD_TICKET.ID in (119,120,121,119,120,121)") 04/27/2016 01:11:06> Ending: 04/27/2016 01:11:06
      • Alright, I think we're getting closer. Try changing GROUP_CONCAT(USER_NAME) to GROUP_CONCAT(USER.USER_NAME)

        Also, the join statement is wrong:
        JOIN USER SUBMITER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
Please log in to comment
Answer this question or Comment on this question for clarity