How can I get a query that answers the following
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"
-
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") - Wildwolfay 11 years ago
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?
