0

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 
3

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)
     ;
    
  • 1
    That works and is likely better than what I had. I was going to say it was more complex but in my first SQL that worked I had to specify the 1,5 (which was ok in my situation) but your solution is more complete. My only comment is the second WHERE should be WHERE mod.id = 17 – Dan Tappin Apr 29 '17 at 15:25
  • @DanTappin thnx, corrected. By the way, you can edit (or suggest edits) your self, in other people's posts! – ypercubeᵀᴹ Apr 29 '17 at 18:07
2

I have just copied/pasted your INNER JOIN query - see below. You have coded a WHERE clause in the wrong place. Keep the second WHERE (at the end of your query), remove the one before the INNER JOIN.

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

It should be:

SELECT ...
FROM ... INNER JOIN ... ON
WHERE

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

-- update: Sorry about this - the WHERE was just sticking out like a sore thumb... Apparently, you are working with 3 tables, but you've only shown us (parts of) 2 of them in your initial question. If my assumption is correct, then the tables you are dealing with are: MODELS, MODEL_LOCATIONS, and LOCATIONS.

If this is the case, I suggest that you just join them all together, and then select whichever column you need. Use "table aliases" ie one or 2 letters as "shortcuts" for table names, in order to make your query easier to read/code.

Do something like:

select 
      -- whichever columns you need eg M.id, ML.model_id
from models M 
  inner join model_locations ML on (M.id = ML.model_id)
  inner join locations L        on (L.id = ML.location_id)
where ... -- whatever condition(s) return(s) the correct rows

In any case, the WHERE needs to be written after the FROM.

  • Except if I remove the WHERE before the join I lose those results. Note the location_from_id and location_to_id and NOT just location_id – Dan Tappin Apr 29 '17 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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