CODEWITHSUNDEEP
mysqlsqldatabase
MobileCushion
MobileCushion

Reputation: 7095

SQL query with two values from the same table under conditions set by column in the same table

I have these two tables:

TableA:
aid ! name
--- ! -----
1   ! Paper
2   ! Rock
3   ! Sciz

Table B (aid is TableA FKEY)
id ! aid ! area  ! mode
-- ! --- ! ----- ! ----
1  ! 1   ! 200   ! 1
2  ! 1   ! 240   ! 2
3  ! 2   ! 300   ! 1
4  ! 2   ! 290   ! 2
5  ! 3   ! 100   ! 1
6  ! 3   ! 110   ! 2

I want to write a query that will return the following results:

aid ! area(mode=1) ! area(mode=2) 
--- ! ------------ ! ------------
1   ! 200          ! 240
2   ! 300          ! 290
3   ! 100          ! 110

How can I accomplish this in mySQL? I also need to exclude the cases where Table B contains only a value for mode1 and not for mode2, and vice-versa.

Thanks.

Upvotes: 0

Views: 1199

Answers (5)

isaace
isaace

Reputation: 3429

This should work:

select * from(
    select aid, max(case when mode = 1 then area end) as mode1, 
                max(case when mode = 2 then area end) as mode2
    from tableB  
    group by aid order by aid
)where mode1 is not null and mode2 is not null

Upvotes: 1

LupusE
LupusE

Reputation: 161

I don't know why TableA what is the purpose of Table A. But I think it will be the startpoint.

SELECT DISTINCT a.aid
    ,b1.area as area_mode1
    ,b2.area as area_mode2

FROM TableA a
    JOIN TableB b1 ON (a.aid = b1.aid AND b1.mode = '1')
    JOIN TableB b2 ON (a.aid = b2.aid AND b2.mode = '2')
;

Maybe a SUBSELECTwill do the trick as well, but I think the double JOIN of Table B will cost less memory for larger tables.

Hint: You should avoid field names like 'name' and 'mode'. They are reserved words in SQL and could cause confusing results.

Upvotes: 1

Raymond Nijland
Raymond Nijland

Reputation: 11602

This is a process called pivot.
And can be done using GROUP BY in combination with MAX(CASE END) clauses.
The HAVING clause makes sure a B.aid has both records containing 1 and 2 mode. 

SELECT 
   B.aid 
 , MAX(CASE WHEN B.mode = 1 WHEN B.area ELSE 0 END) AS 'area(mode=1)'
 , MAX(CASE WHEN B.mode = 2 WHEN B.area ELSE 0 END) AS 'area(mode=2)'
FROM 
 B
GROUP BY
 B.aid 
HAVING
   SUM(B.mode = 1) = 1
 AND
   SUM(B.mode = 2) = 1
ORDER BY 
 B.aid ASC

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you need both modes, then I would suggest using inner join:

SELECT a.aid, b1.area, b2.area  
FROM tablea a1 JOIN
     tableb b1
     ON a1.aid = b1.aid AND b1.mode = 1 JOIN
     tableb b2
     ON a1.aid = b2.aid AND b2.mode = 2;

The inner join will ensure that both values are present in b.

Because both modes need to be present, you don't actually need tablea:

SELECT b1.aid, b1.area, b2.area  
FROM tableb b1
     ON a1.aid = b1.aid JOIN
     tableb b2
     ON a1.aid = b2.aid AND b1.mode = 1  AND b2.mode = 2;

Actually, if you just need the aids where both are present, you can also do:

select b.aid, max(case when mode = 1 then area end) as area1,
       max(case when mode = 2 then area end) as area2
from tableb b
group by b.aid
having area1 is not null and area2 is not null;

This doesn't require joining the tables at all.

Upvotes: 2

Pelin
Pelin

Reputation: 966

http://sqlfiddle.com/#!9/2db6c5/1

Try this;

SELECT b1 .aid, b1.area, b2.area  
FROM tablea a1
LEFT JOIN tableb b1 ON a1.aid = b1 .aid
LEFT JOIN tableb b2 ON a1.aid = b2 .aid
where 
b1.mode = 1
and b2.mode = 2

Upvotes: 2

Related Questions