Mysql Query help

[jacob21]

Hi,
below queries gives two rows like
category_name     high_value_complaints
category_name     high_value_duplicate_complaints
I am trying to merge both queries
category_name  high_value_complaints  high_value_duplicate_complaints

I tried to join both tables but its giving
category_name     high_value_complaints  category_name     high_value_duplicate_complaints

Need suggestions





select category_name, count(distinct complaint3.id ) as high_value_complaints
from complaint_category,assignment3,user3,complaint3_details, complaint3,complaint_tag ct 
where
complaint3.complaint_category_id=complaint_category.id
and complaint3.id=complaint3_details.complaint_id
and ct.complaint_id=complaint3.id
and assignment3.assigned_to=user3.id
and ct.tag_id IN (select tag.id from tag , tag_value where tag_value.tag_value like '%Irrelevant%'
and tag.tag_value_id = tag_value.id)
and assignment3.id = (select min(id) from assignment3 where assignment3.complaint_id = complaint3.id)
and complaint3_details.disputed_amount>5000
and complaint3.created_at>='20131101' and complaint3.created_at<='20131131'
group by category_names


select category_name, count(distinct complaint3.id ) as high_value_duplicate_complaints
from complaint_category,assignment3,user3,complaint3_details, complaint3,complaint_tag ct 
where
complaint3.complaint_category_id=complaint_category.id
and complaint3.id=complaint3_details.complaint_id
and ct.complaint_id=complaint3.id
and assignment3.assigned_to=user3.id
and ct.tag_id IN (select tag.id from tag , tag_value where tag_value.tag_value like '%Duplicate%'
and tag.tag_value_id = tag_value.id)
and assignment3.id = (select min(id) from assignment3 where assignment3.complaint_id = complaint3.id)
and complaint3_details.disputed_amount>5000
and complaint3.created_at>='20131101' and complaint3.created_at<='20131131'
group by category_names

[BenWard]

maybe try to alias the table in your join and don't use select *

[jacob21]

I did
select * from
(......query 1 stuff) as t1
JOIN
(.....query 2 stuff) as t2
ON t1.category_name=t2.category_name

But its give 4 row(2 times category_name)

[BenWard]

try this :

select t1.category_name, t1.high_value_complaints, t2.high_value_duplicate_complaints from
(......query 1 stuff) as t1
JOIN
(.....query 2 stuff) as t2
ON t1.category_name=t2.category_name

[jacob21]

can we used two queries in one query.The only difference is:
and ct.tag_id IN (select tag.id from tag , tag_value where tag_value.tag_value like '%Irrelevant%'