Solved

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

Posted on 2013-12-14
2
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

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…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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