Solved

add a where clause to existing query to limit results because I have numerous staff members

Posted on 2013-12-14
2
344 Views
Last Modified: 2013-12-14
please see answer which worked if only using data from one staff member this_user='staff3'
http://www.experts-exchange.com/Database/MySQL/Q_28318002.html#a39718372

SELECT
        profile_id, this_user
FROM a_messages
GROUP BY
        profile_id
HAVING sum(CASE WHEN sender = 'staff3' and this_user='staff3' THEN 1 ELSE 0 END) = 0
 where this_user='staff3' 

Open in new window


I am getting results from profile_id that had messages from other staff1,staff2 and no messages from staff3

so I am trying to add a where clause


Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where this_user='staff3'' at line 7
0
Comment
Question by:rgb192
2 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 39718824
the WHERE clause belongs before the GROUP BY

SELECT
        profile_id, this_user
FROM a_messages
 where this_user='staff3' 
GROUP BY
        profile_id
HAVING sum(CASE WHEN sender = 'staff3' and this_user='staff3' THEN 1 ELSE 0 END) = 0

Open in new window


I'm not sure that does what you're asking, but it corrects the syntax error.
0
 

Author Closing Comment

by:rgb192
ID: 39718971
Yes this corrects the syntax error.

Thanks
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question