CODEWITHSUNDEEP
mysqlsql
vap0991
vap0991

Reputation: 113

Show IDs where two conditions in the same column satisfy

This is my table1 

ID    || Basket  || Balls
--------------------------
1     || Ram   || 1  
1     || Rom   || 3  
2     || Ram   || 2  
3     || Rom   || 5

Query - Select all the IDs that have both at least 1 ball in both Ram and Rom.

----All the IDs that have at least 1 ball in Ram

 SELECT distinct  ID
 INTO #RAM
 FROM Table1
 where balls > =1 and basket = 'Ram'

----All the IDs that have at least 1 ball in Rom

 SELECT distinct  ID
 INTO #ROM
 FROM Table1
 where balls > =1 and basket = 'Rom'

---All the IDs that have both

 Select distinct ID 
 INTO #FinalTable
 from #RAM a join #ROM b on a.id = b.id

This is what I have written so far it works but I feel there is definitely a more efficient way to do this.

Please let me know. Thank you.

Upvotes: 0

Views: 55

Answers (4)

Strick
Strick

Reputation: 1642

We can group by based on ID,Basket and select records having count greater than 1

select distinct a.id from 
(
    select id,basket,count(balls) as cn
    from table1 
    group by id,basket
    having cn>1
) a ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would use a single aggregation:

select id
from table1 t1
group by id
having sum(case when basket = 'Ram' then balls end) > 0 and
       sum(case when basket = 'Rom' then balls end) > 0;

Assuming the value of balls is never negative or zero, then you only need to check that that the rows exist. That is a little simpler in MySQL:

select id
from table1 t1
group by id
having sum(basket = 'Ram') > 0 and
       sum(basket = 'Rom') > 0;

Upvotes: 2

nbk
nbk

Reputation: 49373

You need Only t use this query and combine your Queries

SELECT 
  a.ID 
FROM (SELECT ID FROM Table1 Where Balls >= 1 And Basket = 'RAM') a
INNER JOIN (SELECT ID FROM Table1 Where Balls >= 1 And Basket = 'ROM') b 
ON a.ID = b.ID

It only return the OD that are have nmore than 1 ball and are in RoM and RAM

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

You can use subqueries to achieve this.

  1. join RAM
  2. join ROM
  3. select count() >= 1
select t1.ID
from Table1 t1
join
    (select ID, count(1) ct from Table1 where Basket = 'Ram'
    group by ID) as ram on ram.ID = t1.ID
join
    (select ID, count(1) ct from Table1 where Basket = 'Rom'
    group by ID) as rom as rom.ID = t1.ID
where ram.ct >= 1 and rom.ct >= 1

Upvotes: 0

Related Questions