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")
Please log in to comment

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

Please log in to comment
Answer this question or Comment on this question for clarity