내가 짠 서브쿼리 2 Subquery in MySQL

sql을 오랜만에 + 서브쿼리는 처음 써보니 앞에꺼 만드는데 1시간, 뒤에꺼 같이 깔끔하게 한번 쓰게끔 바꾸는데 2시간 걸린 듯. 그래도 오랜만에 해보니 기억이 다시 새록새록 나서 좋네.

나도 sql 잘 짜고 싶다...

SELECT

*

FROM

enfant_caterer.caterers AS c

WHERE

c.fee <= '10'

AND c.type = '1'

AND

(

SELECT

c.capacity - SUM(r.quantity)

FROM

enfant_caterer.caterer_reservations AS r

WHERE

r.caterer_id = c.id

AND r.reserved = '2011-01-01'

) >= '5'

AND

(

SELECT

c.capacity - SUM(r.quantity)

FROM

enfant_caterer.caterer_reservations AS r

WHERE

r.caterer_id = c.id

AND r.reserved = '2011-01-01'

) <= '500'

SELECT

*

FROM

(

SELECT

c.id

, c.name

, c.type

, c.capacity - SUM(r.quantity) AS capacity

, c.fee

, c.description

, c.image_url

FROM

enfant_caterer.caterers AS c

, enfant_caterer.caterer_reservations AS r

WHERE

r.caterer_id = c.id

AND r.reserved = '2011-01-01'

AND c.fee <= '10'

AND c.type= '1'

) AS t

WHERE

t.capacity >= '5'

AND t.capacity <= '500'

내가 하려고 했던거...

SELECT

*

, c.capacity - SUM(r.quantity) AS remain

FROM

enfant_caterer.caterers AS c

INNER JOIN

enfant_caterer.caterer_reservations AS r

ON

r.caterer_id = c.id

WHERE

r.reserved = '2011-01-01'

AND c.fee <= '10'

AND c.type = '1'

GROUP BY

caterer_id

HAVING

remain >= '6'

AND remain <= '500'