Query help

[jacob21]

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

[pritaeas]

What have you tried? A simple join does what you want.

[jacob21]

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

[pritaeas]

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

[jacob21]

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

[pritaeas]

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

[jacob21]

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

[scudzilla]

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