/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


How can I get a query that answers the following

12/11/2013 1865 views

I have the following table entries in a mysql database. I have a lot but this is just an example.  

 USER:

======

    

    ID EMAIL                      CUSTOM_1

    1  username@company.com       null

    2  username2@company.com      null

    3  username@company2.com      username5@company.com

    4  first.last@company2.com    username4@company.com

    5  username3@company.com      null

 

LABEL (there are lots of these):

======

  

    ID NAME

    1  THERE-A

    2  THERE-B

    3  THERE-C

    4  THERE-D

    5  SAD

    6  GLAD

 

 

USER_LABEL_JT:

===============

 

    LABEL_ID  USER_ID

    1         1

    1         2

    1         3

    1         4

    4         1

    4         2

    1         5

    1         6

    2         5

    

 

How do I get a query to show:

  •  display user's ID, email address and ALL the labels they are in of any kind (comma separated)
  •  - that I have only one row per user.
  • only show their  row label for certain LABELS. e.g. A   
  • do NOT show a row for users if they are in both the previous list and also a specific list of labels e.g. SAD/GLAD 
  • also show users that are not in a label at all
  • display whatever email is relevant for "company.com" only not "company2.com"
  • if there is no email address at all then show "unknown"
  • for users with no label then show string "needs THERE-X label"
 
Every time i try it something I get multiple rows per user,  or missing the list of users that have no labels or include users that are not relevant to the labels.
1 Comment   [ + ] Show comment

Comments

  • Oh this sounds fun! I haven't been able to spend time on KACE (or ITNINJA) for a while and need to re-brush my SQL learning. I will see if I can come up with something (before verboski! Although his will probably actually "work")

All Answers

1

In order to show the user ID, email address and labels for a user the following query would work:

 

SELECT USER.ID, USER.EMAIL, USER.CUSTOM_1, group_concat(LABEL.NAME)
from USERS
JOIN USER_LABEL_JT on USER_LABEL_JT.ID = USER.ID
JOIN LABEL on LABEL.ID = USER_LABEL_JT
GROUP BY (USER.ID)

 

This is the most basic query that will select everything. 

To only include certain labels you would need to change the join to the LABEL table, I believe. So you could have instead:

JOIN LABEL on LABEL.ID = USER_LABEL_JT and LABEL.NAME in (LABELA, LABELB, LABELC)

In order to select one email address or the other you would need to use a CASE statement. See http://dev.mysql.com/doc/refman/5.0/en/case.html for the syntax. In this example, instead of just selecting USER.EMAIL and USER.CUSTOM_1 you would have something like

CASE
    WHEN USER.EMAIL like '%company.com' THEN USER.EMAIL
    WHEN USER.CUSTOM_1 like '%company.com' THEN USER.CUSTOM_1
    WHEN USER.EMAIL = '' and USER.CUSTOM_1 = '' THEN 'unknown'
END AS USER_EMAIL

 

That should meet most of your requirements. Can you try putting that together and seeing it if actually works?

Answered 12/12/2013 by: chucksteel
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ