phpmyadmin Question posted in
The official documentation can be found here.

Unknown column in 'on clause' – tried both where and having conditions but same error – Phpmyadmin

SELECT hh_podcast_channel.id
FROM hh_podcast_channel
inner JOIN (
        SELECT hh_podcast_episodes.podcast_id, 
            ROW_NUMBER() OVER(PARTITION BY hh_podcast_episodes.id ORDER BY hh_podcast_episodes.published_on) as rn
        FROM hh_podcast_episodes
    ) as t2
    ON hh_podcast_channel.id = hh_podcast_episodes.podcast_id
having t2.rn = 1

#1054 – Unknown column ‘hh_podcast_episodes.podcast_id’ in ‘on clause’

Tags:

3

Answers


  1. 0

    You need to use the table alias (t2) in the JOIN condition

    SELECT hh_podcast_channel.id 
FROM hh_podcast_channel inner JOIN 
(
    SELECT hh_podcast_episodes.podcast_id, 
    ROW_NUMBER() OVER(PARTITION BY hh_podcast_episodes.id
    ORDER BY hh_podcast_episodes.published_on) as rn 
    FROM hh_podcast_episodes
) 
as t2 ON hh_podcast_channel.id = t2.podcast_id 
having t2.rn = 1
    Login or Signup to reply.
  2. 0

    Why you even use the subquery in join. Use the row_number in main query and then use it in the subquery for simplicity as follows:

    Select id from
(SELECT t1.id,
        ROW_NUMBER() OVER(PARTITION BY t2.id ORDER BY t2.published_on) as rn
  FROM hh_podcast_channel t1
  JOIN hh_podcast_episodes t2
    On t1.id = t2.podcast_id) t
Where rn = 1
    Login or Signup to reply.
  3. 0

    I would suggest:

    SELECT pc.id
FROM hh_podcast_channel pc JOIN
     (SELECT pe.*
            ROW_NUMBER() OVER (PARTITION BY pe.id ORDER BY pe.published_on) as rn
      FROM hh_podcast_episodes pe
     ) pe
     ON pc.id = pe.podcast_id
WHERE pe.rn = 1;

    This fixes your specific problem which is that your table aliases are confused. In addition:

    • This uses meaningful table aliases, which are abbreviations for the column names.
    • This replaces the HAVING with WHERE. Although both happen to work in MySQL, the use of HAVING is misleading because it implies aggregation.
    • This uses pe.*, relying on the optimizer to choose the columns in the subquery. I just find that that is more convenient, so if I decide to include another column, I don’t have to make changes in multiple places.

    I also suspect that you want SELECT DISTINCT, but your question doesn’t provide sample data and desired results.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search