Get all members from country depending on multiple tables

Question

I have 4 tables, filled with some example data

user table

id | name  | idAddress | 
------------------------
1  | Bernd | 1         | 
2  | Max   | 2         |
3  | Tom   | 3         |
4  | Bob   | 4         |
5  | Alice | 5         |

address

id | country |  zip  |
----------------------
1  | DE      | 39213 |
2  | CH      | 13847 |
3  | NL      | 38472 |
4  | DE      | 94872 |
5  | MT      | 54682 |
6  | US      |  4682 |
7  | DE      | 45853 |
8  | NA      | 12144 |

orga2Address

idOrga   | idAddress |
------------------
1        | 6         | 
1        | 7         |
2        | 7         |
2        | 8         |

orga2User

idUser | idOrga |
-----------------
1      | 1      | 
1      | 2      |
2      | 1      |
3      | 2      |
4      | 2      |

I want to select all user names that have address in 'DE' in the user table or that are in an orga which has an address in 'DE' in orga2Address.

I was able to get the result with the UNION command like this (see this SQLFiddle):

SELECT `name`, `country` FROM (SELECT `user`.`name`, `address`.`country` from `user` 
INNER JOIN `address` ON `user`.`idAddress` = `address`.`id`
UNION
SELECT  `user`.`name` from `user` 
INNER JOIN `orga2User` `o2U` ON `o2U`.`idUser` = `user`.`id`
INNER JOIN `orga2Address` `o2A` ON `o2A`.`idOrga` = `o2U`.`idOrga` 
INNER JOIN `address` `a` ON `a`.`id` = `o2A`.`idAddress`) as `U` WHERE `U`.`country`="DE";

The result

name   |
--------
Bernd  |
Max    |
Tom    |
Bob    |

Is there a quicker and better way to get the wanted result?

Answer 1

Here's a more compact formulation (SQL Fiddle):

SELECT DISTINCT user.name, address.country
    FROM orga2Address
        INNER JOIN orga2User
            ON orga2User.idOrga = orga2Address.idOrga
        RIGHT OUTER JOIN user
            ON user.id = orga2User.idUser
        INNER JOIN address
            ON address.id IN (user.idAddress, orga2Address.idAddress)
    WHERE address.country = 'DE';

The trick is, instead of UNION, perform a JOIN with more than one join path. (We unfortunately need to use DISTINCT, but that is no worse than your UNION, which also de-duplicates results.)

In addition, I suggest:

• formatting your code with better indentation. In this example, the SELECT statement has a FROM clause and a WHERE clause. The FROM clause has multiple JOINs, each of which has an ON clause.
• using single quotes for string literals like 'DE', which is standard ANSI SQL. Using double quotes for strings is a MySQLism, and it fails if SQL_MODE = 'ANSI_QUOTES' is in force.
• avoid overdoing the `quotes`. If you didn't need them in your CREATE TABLE statements, you don't need them in your query either.