CODEWITHSUNDEEP
mysqlsqlperl
Sakthivel
Sakthivel

Reputation: 7

How to access subquery table in the main query with MySQL?

select  m1.id, m1.status, at.view_data, at.view_graph, ta.tag_string
from
access_tbl at, image_campaign_tbl m1
RIGHT JOIN 
        (select
        GROUP_CONCAT(t.name) as tag_string , c.image_campaign_id
        from campaign_tags_tbl c,tag_tbl t
        where c.tag_id=t.id 
        $tag_q
        group by c.image_campaign_id
        ) as ta
ON ta.image_campaign_id=m1.id
where
        m1.client_id =$client_id
        and m1.client_id = at.client_id
    $prev_filter
limit $start,$end;

Error message:

in LOGS: DBD::mysql::db selectall_arrayref failed: Unknown column 't.name' in 'where clause' at /home/sakthi/rtads/Project/pm/Image/UI.pm line 2536.**

In Perl Module, I'm passing the same value of $tag_q to the $prev_filter to get the Pagination of filter based on TAGS values in the next page

if ( $prev_filter eq '' ) {
    $prev_filter =
        $search_clist_q . ' '
      . $tag_q . ' '
}

From the error msg, I got the error which I'm doing. Since I'm trying to access the table of subquery in the main query, this error is happening.

So I want to know how to access the tag_string(or)t.name outside the subquery.

Upvotes: 0

Views: 392

Answers (1)

Joël Salamin
Joël Salamin

Reputation: 3576

First of all, i suggest you to avoid use of old school syntax for jointures (FROM table1, table2,... WHERE table1.column1 = table2.column2 AND ...).

Here is the query that seems to return what you're looking for:

SELECT IC.id
    ,IC.status
    ,A.view_data
    ,A.view_graph
    ,TA.tag_string
FROM access_tbl A
INNER JOIN image_campaign_tbl IC ON IC.client_id = A.client_id
                                   AND IC.client_id = $client_id
RIGHT JOIN (SELECT CT.image_campaign_id
                  ,GROUP_CONCAT(T.name) AS [tag_string]
            FROM campaign_tags_tbl CT
            INNER JOIN tag_tbl T ON T.id = CT.tag_id
            GROUP BY CT.image_campaign_id) TA ON TA.image_campaign_id = IC.id
WHERE <Your filters here>
LIMIT $start, $end

Hope this will help you.

Upvotes: 1

Related Questions