CODEWITHSUNDEEP
mysqlsql
salma
salma

Reputation: 73

Writing a single query for mutliple queries with same where condition same table

select column1,column2,column3 from table1 where column5=0 and column6=0
select column1,column2,column3 from table1 where column5!=0 and column6!=0

These are two sql statements reading data from same table1. Is there a way to write a single query that returns the same data?

i want individual result for (column5 = 0 AND column6 = 0) and (column5 != 0 AND column6 != 0) in single query.

as example:
select column1 as c1,column2 as c2,column3 as c3 from table1 where column5=0 and  column6=0
union
select column1 as c1_,column2 as c2_,column3 as c3_ from table1 where column5!=0 and column6!=0

Upvotes: 0

Views: 201

Answers (6)

salma
salma

Reputation: 73

select 
sum(
 case when (column5=0 or column6=0 ) 
 then 1 else 0 end 
   ) as c1 ,

 sum(
 case when (column5=0 or column6=0 ) 
 then 1 else 0 end 
   ) as c2 ,
 sum(
 case when (column5=0 or column6=0 ) 
 then 1 else 0 end 
  ) as c3 ,

sum(
 case when (column5!=0 or column6!=0 ) 
 then 1 else 0 end 
) as c1_ ,

  sum(
 case when (column5!=0 or column6!=0 ) 
 then 1 else 0 end 
  ) as c2_ ,
 sum(
 case when (column5!=0 or column6!=0)  
 then 1 else 0 end 
   ) as c3_ ,

from table1

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656441

If I understand correctly, you want to keep the two fractions of the result apart. Add an ORDER BY clause for that.

SELECT column1, column2, column3 
FROM   table1 
WHERE  (column5 =  0 AND column6 =  0)
    OR (column5 != 0 AND column6 != 0)
ORDER  BY (column5 = 0) DESC -- it's enough to include one column in this case.

If you also want to be able to tell them apart, add another column that signifies the origin:

SELECT (column5 = 0) AS first_part, column1, column2, column3 
FROM   table1 
WHERE  (column5 =  0 AND column6 =  0)
    OR (column5 != 0 AND column6 != 0)
ORDER  BY 1 DESC;

Upvotes: 0

Faisal Feroz
Faisal Feroz

Reputation: 12785

SELECT column1,column2,column3 from table1 where (column5=0 and column6=0) or (column5!=0 and column6!=0)

Upvotes: 0

Dave Richardson
Dave Richardson

Reputation: 4995

As alluded by others you can use UNION. In order to identify which records came from which query you'd do it like this :

select 'query1', column1,column2,column3 from table1 where column5=0 and column6=0
union
select 'query2', column1,column2,column3 from table1 where column5!=0 and column6!=0

Then in your recordset processing you can check the first value and decide what to do with the rest.

Upvotes: 0

palacsint
palacsint

Reputation: 28865

SELECT column1, column2, column3 
FROM table1 
WHERE (column5 = 0 AND column6 = 0) OR (column5 != 0 AND column6 != 0)

Upvotes: 1

imm
imm

Reputation: 5919

If you wanted to get all the results back from both queries in a single result set, you could use a UNION like so:

select column1,column2,column3 from table1 where column5=0 and column6=0
union
select column1,column2,column3 from table1 where column5!=0 and column6!=0

Upvotes: 0

Related Questions