CODEWITHSUNDEEP
mysql
Mickael Magniez
Mickael Magniez

Reputation: 111

Mysql : Using derived table in subquery

In my (very simplified) schema, i have 3 tables : offers, attributes, attributes_offers.

I want to use subquery from a derived table, in this query : 

SELECT
    o.offer_id,
    GROUP_CONCAT(CONCAT(attr.attribute_code,'-',attr.value_id,'-',attr.value_value) SEPARATOR ';') AS attributes,
    (SELECT attr.value_id FROM attr WHERE attribute_code = 'area') AS area_id,
    (SELECT attr.value_id FROM attr WHERE attribute_code = 'category') AS category_id
FROM   offers o
INNER JOIN (
    SELECT offer_id,attribute_code,value_id,value_value
    FROM   attributes_offers ao
    INNER JOIN attributes att USING (attribute_code) 
) AS attr ON attr.offer_id = o.offer_id

But MySql answers me : Table 'attr' doesn't exist.

Group_concat works well.

Is there a way to use my derived table attr in select subquery?

Upvotes: 2

Views: 2487

Answers (1)

Saad Touhbi
Saad Touhbi

Reputation: 345

it's normal because the table 'attr' and the select you are trying to use on it are in different blocks... see it this way, the Select you are trying to do is in a box and you can't see what's outside. try this 

SELECT
    o.offer_id,
    GROUP_CONCAT(CONCAT(attr.attribute_code,'-',attr.value_id,'-',attr.value_value) SEPARATOR ';') AS attributes,
   attr.value_id AS area_id,
    attr.value_id AS category_id
FROM   offers o
INNER JOIN (
    SELECT offer_id,attribute_code,value_id,value_value
    FROM   attributes_offers ao
    INNER JOIN attributes att USING (attribute_code) 
) AS attr ON attr.offer_id = o.offer_id

and if you wana fetch all the value_id try to do it in another query because the one you r trying to do is simply not gonna work

Upvotes: 1

Related Questions