MySQL Query, (access mainquery from subquery)

Question

I have a problem that I need a WHERE clause in a subquery that depends on the results of the main Query, otherwise my results would be wrong and the query takes too long / is not executeable.

The circumstances that I need this query to create a view which I need for a search server support the problem that I cannot split this into two queries, nor process it with a script dynamically.

The problem occurs with the following query:

    SELECT `s`.`id` AS `seminar_id`, (SUM( `sub`.`seminar_rate` ) / COUNT( `sub`.`seminar_id` )) AS `total_rate`
    FROM
    (
        SELECT (SUM( value ) / COUNT( * )) AS `seminar_rate` , `r`.`seminar_id`
        FROM `rating` r
        INNER JOIN `rating_item` ri ON `r`.`id` = `ri`.`rating_id`
        WHERE `r`.`seminar_id` = `s`.`id`/* <- Here is my problem, this is inacessible */
        GROUP BY `r`.`seminar_id`
    ) AS sub,
    `seminar` s
    INNER JOIN `date` d
    ON `s`.`id` = `d`.`seminar_id`
    INNER JOIN `date_unit` du
    ON `d`.`id` = `du`.`date_id`        
    LEFT JOIN `seminar_subject` su
    ON `s`.`id` = `su`.`seminar_id`
    LEFT JOIN `subject` suj
    ON `su`.`subject_id` = `suj`.`id`
    INNER JOIN `user` u
    ON `s`.`user_id` = `u`.`id`
    INNER JOIN `company` c
    ON `u`.`company_id` = `c`.`id`
    GROUP BY `du`.`date_id`, `sub`.`seminar_id`

This query should calculate a total rate out of ratings for each Seminar. However my ratings are stored in my "rating" table and should be processed live.

(Sidenote: If you wonder about all the joins: This query has alooot more SELECT'ed fields, I just removed them because they are not nesessary to solve the problem and to make the query look less complicated [I know it still is >.>]...)

The reason is that I want this results to be sortable by my search engine later depending on the users sort parameters, thatswhy I need it inside this query.

The problem itself is pretty obvious: ERROR 1054 (42S22): Unknown column 's.id' in 'where clause'

The subselect doesnt know about the results of the main query, is there a solution to bypass this?

Could someone give me a hint to get this working?

Thanks in advance.

i wanna see this done ! - who was first ? Chicken or the egg ? -(your question) — Up_One
– Up_One, Commented Mar 28, 2014 at 11:41

Neels · Accepted Answer · 2014-03-28 11:50:33Z

1

Using your subquery in the JOIN you can eliminate the WHERE clause and achieve nearly the same result. Here is your modified query. Hope this solves your problem.

SELECT `s`.`id` AS `seminar_id`, (SUM( `sub`.`seminar_rate` ) / COUNT( `sub`.`seminar_id` )) AS `total_rate`
FROM `seminar` s
INNER JOIN
(
    SELECT (SUM( value ) / COUNT( * )) AS `seminar_rate` , `r`.`seminar_id`
    FROM `rating` r
    INNER JOIN `rating_item` ri ON `r`.`id` = `ri`.`rating_id`
    /*WHERE `r`.`seminar_id` = `s`.`id` <- Here is my problem, this is inacessible */
    GROUP BY `r`.`seminar_id`
) AS sub ON s.id = sub.`seminar_id`
INNER JOIN `date` d
ON `s`.`id` = `d`.`seminar_id`
INNER JOIN `date_unit` du
ON `d`.`id` = `du`.`date_id`        
LEFT JOIN `seminar_subject` su
ON `s`.`id` = `su`.`seminar_id`
LEFT JOIN `subject` suj
ON `su`.`subject_id` = `suj`.`id`
INNER JOIN `user` u
ON `s`.`user_id` = `u`.`id`
INNER JOIN `company` c
ON `u`.`company_id` = `c`.`id`
GROUP BY `du`.`date_id`, `sub`.`seminar_id`

answered Mar 28, 2014 at 11:50

Neels

2,5436 gold badges36 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steini Over a year ago

This has indeed solved my problem! Quick easy and does not require many changes in my query. Thanks alot! :D

Steini Over a year ago

A sidenode: I came across another problem that when trying to create a view MySQL tells me "views cannot contain subqueries-.-". However I solved it creating a view that selects another view. Another stupid weakness of Mysql, but atleast I found a quick workarround for it.

Collectives™ on Stack Overflow

MySQL Query, (access mainquery from subquery)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related