Solved

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

Posted on 2013-12-14
2
345 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sort in mysql based off of query param 4 29
MySQL recovery 7 27
PHP: Filling Out/Creating a PDF 29 92
How to use 2 ON statements in inner join 3 20
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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