Query can't see LEFT table id in INNER JOIN select

Databases
1

Maybe this just isn’t possible. But the id in table pin_board is unknown inside of the subquery WHERE clause. If this isn’t possible, is there another way to do this in 1 sql statement?


SELECT 
                                                id
                                               ,name
                                               ,CASE WHEN is_private THEN 'b' ELSE 'a' END AS `orderBy` 
                                               ,pin_board_image.image_path
                                           FROM 
                                                pin_board
                                                INNER JOIN (
                                                                SELECT
                                                                    board_id
                                                                   ,image_path
                                                                FROM
                                                                    pin
                                                                WHERE
                                                                    board_id = pin_board.id
                                                                ORDER BY
                                                                    date_created DESC
                                                                LIMIT 1
                                                            ) AS pin_board_image ON pin_board.id = pin_board_image.board_id
                                           WHERE 
                                                is_default = 1
                                                   OR (is_private = 1 && user_id = {$_SESSION['user_id']})
                                           ORDER BY
                                                   `orderBy` ASC
2

you negelcted to mention exactly what it is you’re trying to do

i tried looking at your query to figure out what that might be, but i got a headache

3

I think you want something like that:


SELECT `id`, `name`, IF(`is_private`, 'b', 'a') AS `orderBy`,
  (SELECT `image_path` FROM `pin` WHERE `board_id` = `pin_board`.`id` ORDER BY `date_created` DESC LIMIT 1) AS `image_path`
FROM `pin_board`
WHERE
    `is_default` = 1
     OR (`is_private` = 1 AND `user_id` = X)
ORDER BY `orderBy` ASC

I replaced your case statement with a simple IF function, which is in my opinion much cleaner in statements.
You further tried to combine the two tables twice. The first time by using the parent value inside the subselect. The second time with the help of a join. This does not make sense to me. I assumed you want all entries of pin_board and the newest corresponding pin-entry, which can be achieved for example with one subquery.

4

cleaner? okay, sure, maybe

but don’t forget that CASE is standard sql, and IF isn’t

5

Hey all, thanks for you help. GhostGambler’s solution is what I was after.

Sorry, Rudy, I should have expalined :slight_smile:
I wanted the most recent image_path from the pin table based on the pin_board id.
What caused the headache? What is just an ugly query / poorly written or that I didn’t explain? Just curious so I don’t make the same mistake.

6

the, um, unconventional (to put it as kindly as possible) indentation and ensuing line wraps

7

I wouldn’t suggest using ordering and limit in a subquery for optimization purposes. Something like this is far more standardized and optimized solution.


SELECT
      b.id,
      b.name,
      p.image_path,
  FROM
      pin_board b
 INNER
  JOIN (SELECT
             board_id,
             image_path,
             MAX(date_created) most_recent
          FROM
             pin
         GROUP
            BY
             board_id) i
    ON
      b.board_id = i.board_id
  INNER
   JOIN
      pin p
     ON
      i.board_id = p.board_id
    AND
      i.most_recent = p.date_created
  WHERE
      b.is_default = 1
     OR
      (b.is_private = 1 AND b.user_id = {#user_id#} )
  GROUP
     BY
       b.board_id
  ORDER
     BY
       FIELD(b.is_private,1,0,NULL)