maco1717 maco1717 - 4 months ago 13
MySQL Question

SQL Query, select distinct, FROM, where or and

I have this table

Username Client Name Date Time Published App
1stLineS GS-HPS7 03/08/2015 9:37:44.12 PB Service Desktop
1stLineS GS-HPS7 09/07/2015 11:08:42.31 Citrix Delivery Services Console
1stLineS GARION-WIN8 29/07/2015 15:34:19.85 Mencap@Work2-1
aabidam 009733-DSK 03/08/2015 9:41:56.25 Epicor
aabidam 009733-DSK 03/08/2015 11:53:59.78 Mencap@Work2-1
aabidam 010175-LAP 06/08/2015 9:23:15.67 Mencap Desktop with Acrobat
aabidam NC-CITRIXDESKAA 06/08/2015 12:54:23.51 Powerplan
aabidam WT008064c025aa 13/08/2015 13:44:39.34 Thin Client Desktop
aarong NCIT-008888-DSK 25/07/2015 7:13:30.47 Mencap @ Work Desktop
AarthiT HTML-5190-7294 21/07/2015 11:23:03.42 Mencap@Work2-1
AbbeyY 005625-RMS 14/07/2015 14:22:17.37 Mencap@Work2-1
abbiemc 008700-LAP 03/08/2015 12:31:35.12 Mencap@Work2-1
abbyf 004503-DSK 08/07/2015 9:10:30.71 Mencap@Work2-1
abbygailf 010030-LAP 03/08/2015 11:32:34.53 Mencap@Work2-1
Abbyh ABBY-PC 03/08/2015 13:59:25.21 GLH Contractors Web Portal
abdio 009899-IT 10/07/2015 18:07:49.17 PB Service Desktop
abdio MEMBER-PC 11/08/2015 19:37:49.94 RDP NC-BACKUP01
abdouD 005240-LAP 03/08/2015 11:43:23.43 Mencap@Work2-1
abdulm 008617-DSK 03/08/2015 8:44:32.54 Mencap@Work2-1
AbigailE HP81017347825 02/08/2015 16:07:48.10 Mencap@Work2-1
AbigailJ 009178-DSK 03/08/2015 9:07:03.91 Mencap@Work2-1
AbigailJ 009178-DSK 07/07/2015 10:58:33.74 Mencap Desktop with Acrobat
AbigailJ 009178-DSK 07/07/2015 10:59:33.74 Epicor


I need to get back user where they have Mencap@Work2-1 and a "desktop published app", which is a specific set of apps, im specting back this list of users

1stLineS
aabidam
AbigailJ


I dont get spected results...

I've tried the following query(s) and Returns 0

$sql = "SELECT *
FROM tbl_name
WHERE publishedapp LIKE \"%Mencap@Work2-1%\"
AND publishedapp LIKE \"%Desktop%\"
AND publishedapp LIKE \"%RDP%\"
GROUP BY username


$sql = "SELECT DISTINCT username, publishedapp
FROM tbl_name
WHERE publishedapp = \"Mencap@Work2-1\"
AND publishedapp NOT LIKE \"%Desktop%\"
AND publishedapp NOT LIKE \"%RDP%\"
GROUP BY username
HAVING COUNT(DISTINCT publishedapp) > 1
";


I've tried with more variations, but to be honest I dont know what im doing any more I'm a bit brain dead

Answer

This solvel my problem

SELECT DISTINCT username,
GROUP_CONCAT(DISTINCT publishedapp SEPARATOR \"<br>\") AS publishedapp
FROM tbl_name
WHERE publishedapp LIKE \"%Mencap@Work2-1%\"
OR publishedapp LIKE \"%@%\"
OR publishedapp LIKE \"%Desktop%\"
OR publishedapp LIKE \"%RDP%\"
GROUP BY username
HAVING COUNT(DISTINCT publishedapp) > 1 AND
SUM(DISTINCT publishedapp LIKE \"%Mencap@Work2-1%\") > 0

not only returned the list of users it returned a string with the list of apps used, like this

1stLineS
  Mencap@work2-1
  Citrix Delivery Services Console
aabidam
  Epicor
  Mencap@Work2-1
  Powerplan
AbigailJ
  Mencap@Work2-1
  Epicor
Comments