CODEWITHSUNDEEP
mysqlsqlsubqueryunion
Braydon Batungbacal
Braydon Batungbacal

Reputation: 1038

How To Access Column In Subquery?

I'm having issues with the following query. For the subquery returning a sum of total results, I'm getting the error Unknown column 'search.term' in 'where clause', I'm not sure how I'd be able to do this query properly? Any help is appreciated.

SELECT *,
  (SELECT SUM(totalResults)
   FROM (SELECT COUNT(*) as totalResults
         FROM media
         LEFT JOIN users ON users.id = media.userId
         WHERE media.title LIKE CONCAT('%', search.term, '%')
       OR media.description LIKE CONCAT('%', search.term, '%')
       OR media.tags LIKE CONCAT('%', search.term, '%')
       OR users.username LIKE search.term
         UNION ALL
         SELECT COUNT(*)
         FROM reposts
         LEFT JOIN media ON media.id = reposts.mediaId
         LEFT JOIN users ON users.id = reposts.userId
         WHERE media.title LIKE CONCAT('%', search.term, '%')
       OR media.description LIKE CONCAT('%', search.term, '%')
       OR media.tags LIKE CONCAT('%', search.term, '%')
       OR users.username LIKE search.term
         )
  t) AS totalResults,
  MAX(search.timestamp) AS searchTimestamp
  FROM search
  WHERE userId = '1'
  GROUP BY search.term
  ORDER BY searchTimestamp DESC

Upvotes: 1

Views: 1227

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

The reason is the doubly nested SELECT subqueries. Try this:

 SELECT  term,
         ( SELECT COUNT(*)
           FROM media
           LEFT JOIN users ON users.id = media.userId
           WHERE media.title LIKE CONCAT('%', search.term, '%') 
              OR media.description LIKE CONCAT('%', search.term, '%') 
              OR media.tags LIKE CONCAT('%', search.term, '%') 
              OR users.username LIKE search.term
         )
         +
         ( SELECT COUNT(*)
           FROM reposts
           LEFT JOIN media ON media.id = reposts.mediaId
           LEFT JOIN users ON users.id = reposts.userId
           WHERE media.title LIKE CONCAT('%', search.term, '%') 
              OR media.description LIKE CONCAT('%', search.term, '%') 
              OR media.tags LIKE CONCAT('%', search.term, '%') 
              OR users.username LIKE search.term
         ) AS totalResults,
         MAX(timestamp) AS searchTimestamp
FROM search
WHERE userId = '1'
GROUP BY term
ORDER BY searchTimestamp DESC ;

Sidenote: Using SELECT * while having GROUP BY <columns> will give you indeterminate results in all other columns, except the <columns>. MySQL allows such ill-defined behaviour but it's not good practice.

Upvotes: 2

Related Questions