OUTER JOIN Works in one direction but not the other This started as a Rails app question but has evolved as I need to do a raw SQL query.  My title may be off but it's the best I could come up with.

Here is my data:

SELECT 
     "model_locations".* 
FROM 
     "model_locations"

    +----+---------------------+-------------+
    | id |            model_id | location_id |
    +----+---------------------+-------------+
    |    | 17                  | 50          |
    |    | 17                  | 51          |
    |    | 10                  | 24          |
    |    | 19                  | 11          |
    |    | 19                  | 5           |
    |    | 19                  | 51          |
    +----+---------------------+-------------+
    6 rows in set

SELECT 
    "models"."id", "models"."location_from_id", "models"."location_to_id" 
FROM 
    "models"'

+----+------------------+----------------+
| id | location_from_id | location_to_id |
+----+------------------+----------------+
| 17 | 1                | 5              |
| 18 | 50               | 24             |
| 10 | 3                | 8              |
| 1  | 50               | 11             |
| 19 | 1                | 5              |
| 20 | 1                | 11             |
| 21 | 11               | 5              |
+----+------------------+----------------+
7 rows in set


So for example:

Model 17 has Locations 50, 51 AND 1 ,5 
Location 11 has Models 19, 1 AND 20, 21


So I can find the Model Locations:

SELECT 
    "locations".* 
FROM 
    "locations" 
    LEFT JOIN "model_locations" ON "locations"."id" = "model_locations"."location_id" 
WHERE 
    "model_locations"."model_id" = 17 OR "locations"."id" IN (1,5)


This works great - I get my 4 locations however I can't get the reverse to work:

SELECT 
     "models".* 
FROM 
     "models" 
WHERE 
     "models"."location_from_id" = 11 OR "models"."location_to_id" = 11 
INNER JOIN "model_locations" ON "model_locations"."model_id" = "models"."id" 
WHERE 
     "models_locations"."location_id" = 11'


This fails at the INNER JOIN:

PG::SyntaxError: ERROR:  syntax error at or near "INNER"


I am pretty weak on my SQL but I don't get while this doesn't work.  I have tried several variations etc. and am sure I don't have a typo.

I need the SQL that will get me Models 19, 1 AND 20, 21 related to Location 11

UPDATE

I should really not stay up late working on this stuff.  This seems to work and a swear I tried this:

SELECT 
     "models".* 
FROM 
     "models" 
LEFT JOIN "model_locations" ON "model_locations"."model_id" = "models"."id" 
WHERE 
     "models_locations"."location_id" = 11'
     OR
     "models"."location_from_id" = 11
     OR
     "models"."location_to_id" = 11
There are two (plus 1) - seemingly independent - relationships between models and locations. One relationship is in model_locations and the other two in models.

I find both your queries to be incorrect because they don't examine the relationships independently. If there is a relationship (between a model and a location) only in one of these two tables, the query will not return any. Using LEFT joins might be possible but seems inappropriate, too.

I'd use simple OR conditions with EXISTS subqueries or a UNION:


Final all locations of a certain model (17):

SELECT 
    loc.* 
FROM 
    locations AS loc 
WHERE EXISTS
      ( SELECT 1
        FROM model_locations AS ml
        WHERE ml.model_id = 17 
          AND ml.location_id = loc.id
      )
   OR EXISTS
      ( SELECT 1
        FROM models AS mod
        WHERE mod.id = 17  
          AND loc.id IN (mod.location_from_id, mod.location_to_id)
      ) ;

Final all models of a certain location (11):

SELECT 
    mod.* 
FROM 
    models AS mod 
WHERE EXISTS
      ( SELECT 1
        FROM model_locations AS ml
        WHERE ml.location_id = 11 
          AND ml.model_id = mod.id
      )
   OR 11 IN (mod.location_from_id, mod.location_to_id)
 ;