On MYSQL Join, I'm getting un unknown column error.

Question

Here is my query:

   select s.*,
          u.display_name 
     from wp_wdify_sites s, 
          wp_users u
LEFT JOIN wp_wdify_sitesmeta m ON (s.sid = m.site_id)
    where milestones like '%dateSubmitted%' 
      and milestones not like '%dateArchived%' 
      and u.ID = s.cid and did IN (0)
      and m.meta_key = 'aboutSite'`

The error I'm getting is #1054 - Unknown column 's.sid' in 'on clause'. The problem is, wp_wdify_sites.sid definitely does exist. It doesn't make sense.

Answer 1

try something like this :

    select s.*,
           u.display_name 
      from wp_users u 
INNER JOIN wp_wdify_sites s ON u.ID = s.cid 
 LEFT JOIN wp_wdify_sitesmeta m ON (s.sid = m.site_id) 
     where milestones like '%dateSubmitted%' 
       and milestones not like '%dateArchived%' 
       and did IN (0) 
       and m.meta_key = 'aboutSite'

your LEFT JOIN ON clause is in the wrong place for joining wp_wdify_sites and wp_wdify_sitesmeta.