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\$ Commented Aug 4, 2014 at 11:21
  • \$\begingroup\$ Yes, I think it will not be a problem \$\endgroup\$ Commented Aug 4, 2014 at 11:25

1 Answer 1

Reset to default
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\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.