StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Btw query you have posted have some errors so I am trying to post correct ones so that if someone wants to try that out they can do it. 1st one: SELECT DISTINCT <code>co</code>.<code>c_id</code> FROM (<code>company</code> co) JOIN <code>company_type</code> t ON <code>t</code>.<code>c_id</code> = <code>co</code>.<code>c_id</code> GROUP BY <code>co</code>.<code>c_id</code> HAVING sum(if(<code>t</code>.<code>t_id</code> in (1,2),1,0)) = 2 2nd one:</p> <pre><code>SELECT DISTINCT `co`.`c_id` FROM (`company` co) JOIN `company_branch` b ON `b`.`c_id` = `co`.`c_id` GROUP BY `co`.`c_id` HAVING sum(if(`b`.`b_id` in (1,2),1,0)) = 2 </code></pre> <p>I have excluded status column as I did not took that in my table structure.</p> <p>Now I am not sure what is your business logic but I am putting up your final SQL like following:</p> <pre><code>SELECT DISTINCT `co`.`c_id`,sum(if(`cb`.`b_id` in (1,2),1,0)) cb_sum,sum(if(`ct`.`t_id` in (1,2),1,0)) ct_sum FROM (`company` co) LEFT JOIN `company_branch` cb ON `cb`.`b_id` = `co`.`c_id` LEFT JOIN `company_type` ct ON `ct`.`t_id` = `co`.`c_id` GROUP BY `co`.`c_id` HAVING sum(if(`cb`.`b_id` in (1,2),1,0)) = 2 AND sum(if(`ct`.`t_id` in (1,2),1,0)) = 2 </code></pre> <p>Now if you run that SQL w/o HAVING clause you should see output like below:</p> <pre><code>c_id cb_sum ct_sum 1 4 4 2 1 1 </code></pre> <p>So if you change your condition to "> 2" instead of "= 2" you should get what you want but the other option is that you use UNION to combine your result set as following:</p> <pre><code>SELECT DISTINCT `co`.`c_id` FROM (`company` co) JOIN `company_type` t ON `t`.`c_id` = `co`.`c_id` GROUP BY `co`.`c_id` HAVING sum(if(`t`.`t_id` in (1,2),1,0)) = 2 UNION SELECT DISTINCT `co`.`c_id` FROM (`company` co) JOIN `company_branch` b ON `b`.`c_id` = `co`.`c_id` GROUP BY `co`.`c_id` HAVING sum(if(`b`.`b_id` in (1,2),1,0)) = 2 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload