3
\$\begingroup\$

I'm trying to select two groups of countries with different situations.

SELECT cou.id, 
       cou.title 
FROM   continents con 
       RIGHT OUTER JOIN countries cou 
                     ON con.id = cou.continents_id 
WHERE  con.deliver = 1 
       AND con.active = 1 
UNION ALL 
SELECT cou2.id, 
       cou2.title 
FROM   countries cou2 
       LEFT OUTER JOIN continents con2 
                    ON con2.id = cou2.continents_id 
WHERE  cou2.deliver = 1 
       AND con2.deliver = 0
  1. Continent has to be deliver = 1 and then select all countries inside it.
  2. If Continent is deliver = 0 but there are countries with deliver = 1 inside, then select it.

What is the best way of doing this?

\$\endgroup\$
2
  • \$\begingroup\$ is it possible to write left outer join instead of right outer join for the first part \$\endgroup\$
    – Paritosh
    Commented Aug 4, 2014 at 11:21
  • \$\begingroup\$ Yes, I think it will not be a problem \$\endgroup\$
    – CIRCLE
    Commented Aug 4, 2014 at 11:25

1 Answer 1

2
\$\begingroup\$

I have formatted the queries. I am not able to test it, so if you can give the schema for this, or create it at SQL fiddle, that will be great. Data is not required.

SELECT cou.id, 
       cou.title 
FROM   countries cou 
       LEFT OUTER JOIN continents con 
                     ON  cou.continents_id = con.id 
WHERE  (con.deliver = 1 AND con.active = 1 ) OR 
       (cou.deliver = 1 AND cou.deliver = 0)

Please let me know in case there are any concerns.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.