Need to combine common result of two select statements

Question

i have to select the common coloumns c1 ,c2,c3 from result of two sql satements.

1)

select c1, c2, c3,count(c3)  from (select * from form_name
where data_created >'1273446000' and data_creazione<'1274569200')
group by c1,c2, c3 having count(c3)>1

2)

select c1, c2, c3,count(c3)  from (select * from form_name 
where data_created>'1272236400' and data_creazione<'1274569200')
group by c1,c2, c3 having count(c3)>2

I need to select c1,c2,c3 all same and common found in both the result of query.

how could this be done...could anyone help please?

Answer 1

You could use derived tables and then join them together to get the results you want.

select a.c1, a.c2, a.c3, a.acount, b.bcount
From 
(select c1, c2, c3, count(*) as acount  from (select * from form_name 
where data_created >'1273446000' and data_creazione<'1274569200') 
group by c1,c2, c3 having count(c3)>1) a
join 
(select c1, c2, c3, count(*) as bcount from (select * from form_name  
where data_created>'1272236400' and data_creazione<'1274569200') 
group by c1,c2, c3 having count(c3)>2)b 
    on a.c1 = b.c1 and a.c2 = b.c2 and a.c3 = b.c3

Answer 2

Have you tried joining the 2 queries with 'UNION'?

eg.

select c1, c2, c3,count(c3)  from (select * from form_name
where data_created >'1273446000' and data_creazione<'1274569200')
group by c1,c2, c3 having count(c3)>1    
union    
select c1, c2, c3,count(c3)  from (select * from form_name 
where data_created>'1272236400' and data_creazione<'1274569200')
group by c1,c2, c3 having count(c3)>2

Answer 3

I think INTERSECT will solve your problem. More Infos here.

select c1, c2, c3,count(c3)  from (select * from form_name
where data_created >'1273446000' and data_creazione<'1274569200')
group by c1,c2, c3 having count(c3)>1    
INTERSECT
select c1, c2, c3,count(c3)  from (select * from form_name 
where data_created>'1272236400' and data_creazione<'1274569200')
group by c1,c2, c3 having count(c3)>2

Answer 4

remove the count(c3) from the select list, it can differ (the HAVING clause guarantees this) and the OP only wants to compare c1, c2 and c3. If the COUNT(c3) column is different, what rows can be in common? none or some, it will vary. Also remove the derived tables, they are not necessary. So try:

select 
    c1, c2, c3  
    from form_name
    where data_created >'1273446000' and data_creazione<'1274569200'
    group by c1,c2, c3 
    having count(c3)>1
INTERSECT
select 
    c1, c2, c3
    from form_name 
    where data_created>'1272236400' and data_creazione<'1274569200'
    group by c1,c2, c3 
    having count(c3)>2