HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Selecting countries with different situations

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
countrieswithdifferentselectingsituations

Problem

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


  • Continent has to be deliver = 1 and then select all countries inside it.



  • If Continent is deliver = 0 but there are countries with deliver = 1 inside, then select it.



What is the best way of doing this?

Solution

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.

Code Snippets

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)

Context

StackExchange Code Review Q#59002, answer score: 2

Revisions (0)

No revisions yet.