0

Complaint table
id company_id user_id source

id=coplaint_id
user_id=user which filed complaint
company_id=complaint againt company
source=source of complaint

I am trying to get user_id who have filed complaint via same user_id and against same company.

one user can filed multiple complaints against same/different company

3
Contributors
7
Replies
37
Views
3 Years
Discussion Span
Last Post by scudzilla
0

I tried below query.Its give all users who filed more then one complaints
But its no working in my tool.(due to unoptimized query)

select * from complaint3 where user_id IN(
select c3.user_id from complaint3 c3
where
c3.created_at>='2014-01-01'
and c3.created_at<='2014-01-30'
group by c3.user_id having count(c3.id)>1)

Need suggestions

Edited by pritaeas: Fixed markdown.

0

Oh, not what I imagined. Try something like this:

SELECT user_id, company_id, COUNT(*) AS num_complaints
FROM complaint3
GROUP BY user_id, company_id
HAVING COUNT(*) > 1
0

Hi,
I already tried that but i am trying to get COUNT(id) value
For exmple
if user_id 1 has 3 complaint against 2 different companies then data should be

user_id company_id id(complaint_id)
1 123 1232333
1 123 3565636
1 167 3535535

Need Suggestions
Thanks

select c3.user_id, c3.company_id, COUNT(id) FROM complaint3 c3
where c3.created_at>='2014-01-01'
and c3.created_at<='2014-01-30'
GROUP BY c3.user_id, c3.company_id
HAVING  COUNT(id) > 1
order by user_id desc
0

Your example above is different than the one in the OP. Can you give some sample data with expected result?

0

Sample Data

With Above Query I am getting
user_id    company_id   count(id)
122           63525         2

i.e. user 122  filed 2 complaints against 63525   company

I am looking for those 2 complaint_id
user_id    company_id   complaint_id

122           63525         35535
122           63525        35536
0

Something like this?

Selece c1.user_id, c1.company_id, c1.id from complaint3 c1, complaint3 c2 where c1.user_id = c2.user_id and c1.companyid = c2.companyid and c1.id <> c2.id group by c1.id

Edited by scudzilla

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.