Every derived table must have its own alias!

[omid020]

Hello ,

When I want to run my query in MySQL Query Browser , I receive this alert :
Every derived table must have its own alias .
But I don`t know what changes should I take to correct it ?

This is my query :

SELECT  ft.projectName
        ,ft.leafCode
        ,ft.leafName
        ,ft.colorsCode
        ,ft.polish
        ,ft.pack
        ,ft.warehouse
        ,ft.sent
        ,ft.orderQuota
        ,COALESCE(((ft.polish + ft.pack + ft.warehouse) - (ft.orderQuota)), 0) AS `available`
        ,ft.orderAmount
        ,COALESCE((ft.orderAmount) - (ft.polish + ft.pack + ft.warehouse + ft.sent - (ft.orderQuota)), 0) AS `projectRemained`
        ,COALESCE((((ft.polish + ft.pack + ft.warehouse + ft.sent) - (ft.orderQuota)) / (ft.orderAmount)), 0) AS `projectProgress`
FROM (
SELECT	projects.projectName
		,rs_leaves.leafCode
		,rs_leaves.leafName
		,rs_colors.colorsCode
		,NULL AS `polish`
		,NULL AS `pack`
		,NULL AS `warehouse`
		,SUM(assign_details.assAmount) AS `sent`
		,projects_details.orderQuota
		,projects_details.orderAmount
FROM projects_details
INNER JOIN rs_leaves
ON rs_leaves.leafID = projects_details.leafName
INNER JOIN rs_colors
ON rs_colors.colorsID = projects_details.leafColor
INNER JOIN assign_details
ON assign_details.projectID = projects_details.projectID
INNER JOIN projects
ON projects.projectID = projects_details.projectID
Group By rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
UNION ALL
SELECT	NULL AS `projectNmae`
		,NULL AS `leafCode`
		,NULL AS `leafName`
		,NULL AS `colorsCode`
		,COALESCE(Sum(patine_tbl.receivedNum), 0) - COALESCE(Sum(Case
    	When sent_receive_tbl.stationID = '6' Then sent_receive_tbl.sentNum
    	Else Null End), 0) As `ploish`
    	,NULL AS `pack`
    	,NULL AS `warehouse`
    	,NULL AS `sent`
    	,NULL AS `orderQuota`
    	,NULL AS `orderAmount`
FROM patine_tbl
INNER JOIN rs_leaves
ON rs_leaves.leafID = patine_tbl.leafID
INNER JOIN rs_colors
ON rs_colors.colorsID = patine_tbl.colorsID
LEFT JOIN sent_receive_tbl
ON sent_receive_tbl.leafID = rs_leaves.leafID
AND sent_receive_tbl.colorsID = rs_colors.colorsID
Group By rs_leaves.leafCode,
  rs_leaves.leafName,
  rs_colors.colorsCode
UNION ALL
SELECT	NULL AS `projectNmae`
		,NULL AS `leafCode`
		,NULL AS `leafName`
		,NULL AS `colorsCode`
		,NULL AS `polish`
		,COALESCE((Sum(sent_receive_tbl.receivedNum) - Sum(sent_receive_tbl.sentNum)), 0) As `pack`
    	,NULL AS `warehouse`
    	,NULL AS `sent`
    	,NULL AS `orderQuota`
    	,NULL AS `orderAmount`
FROM sent_receive_tbl
Where rs_stations.stationID = '7'
Group By rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
UNION ALL
SELECT	NULL AS `projectNmae`
		,NULL AS `leafCode`
		,NULL AS `leafName`
		,NULL AS `colorsCode`
		,NULL AS `polish`
		,NULL AS `pack`
    ,tt.warehouse
    ,NULL AS `sent`
    ,NULL AS `orderQuota`
    ,NULL AS `orderAmount`
FROM (SELECT NULL AS `projectNmae`
		,NULL AS `leafCode`
		,NULL AS `leafName`
		,NULL AS `colorsCode`
		,NULL AS `polish`
		,NULL AS `pack`
    ,st.warehouse
    ,NULL AS `sent`
    ,NULL AS `orderQuota`
    ,NULL AS `orderAmount`
FROM (SELECT
  st.leafCode,
  st.colorsCode,
  COALESCE(Sum(st.INPUT), 0) As INPUT,
  COALESCE(Sum(st.OUTPUT), 0) As OUTPUT,
  COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
From (Select rs_leaves.leafCode
	,rs_colors.colorsCode
     ,Sum(receipt_details.recAmount) As INPUT
     ,Null As OUTPUT
    From receipt_details
    Inner Join rs_leaves
    On rs_leaves.leafID = receipt_details.leafName
    Inner Join rs_colors
    On rs_colors.colorsID = receipt_details.leafColor
    Group By rs_leaves.leafCode, rs_colors.colorsCode
    UNION All
    Select rs_leaves.leafCode
    ,rs_colors.colorsCode
    ,Null As INPUT
    ,Sum(assign_details.assAmount) As OUTPUT
    From assign_details
    Inner Join rs_leaves
    On rs_leaves.leafID = assign_details.leafName
    Inner Join rs_colors
    On rs_colors.colorsID = assign_details.leafColor
    Group By rs_leaves.leafCode, rs_colors.colorsCode) As st
Group By st.leafCode,st.colorsCode
) AS tt
Group By tt.leafCode,tt.colorsCode
)
) AS ft
Group By ft.projectName
		,ft.leafCode
		,ft.leafName
		,ft.colorsCode

Also in above code I tried to embed one subquery into another subquey ; Did I do it correctly ? :

SELECT	NULL AS `projectNmae`
		,NULL AS `leafCode`
		,NULL AS `leafName`
		,NULL AS `colorsCode`
		,NULL AS `polish`
		,NULL AS `pack`
    ,tt.warehouse
    ,NULL AS `sent`
    ,NULL AS `orderQuota`
    ,NULL AS `orderAmount`
FROM (SELECT NULL AS `projectNmae`
		,NULL AS `leafCode`
		,NULL AS `leafName`
		,NULL AS `colorsCode`
		,NULL AS `polish`
		,NULL AS `pack`
    ,st.warehouse
    ,NULL AS `sent`
    ,NULL AS `orderQuota`
    ,NULL AS `orderAmount`
FROM (SELECT
  st.leafCode,
  st.colorsCode,
  COALESCE(Sum(st.INPUT), 0) As INPUT,
  COALESCE(Sum(st.OUTPUT), 0) As OUTPUT,
  COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
From (Select rs_leaves.leafCode
	,rs_colors.colorsCode
     ,Sum(receipt_details.recAmount) As INPUT
     ,Null As OUTPUT
    From receipt_details
    Inner Join rs_leaves
    On rs_leaves.leafID = receipt_details.leafName
    Inner Join rs_colors
    On rs_colors.colorsID = receipt_details.leafColor
    Group By rs_leaves.leafCode, rs_colors.colorsCode
    UNION All
    Select rs_leaves.leafCode
    ,rs_colors.colorsCode
    ,Null As INPUT
    ,Sum(assign_details.assAmount) As OUTPUT
    From assign_details
    Inner Join rs_leaves
    On rs_leaves.leafID = assign_details.leafName
    Inner Join rs_colors
    On rs_colors.colorsID = assign_details.leafColor
    Group By rs_leaves.leafCode, rs_colors.colorsCode) As st
Group By st.leafCode,st.colorsCode
) AS tt
Group By tt.leafCode,tt.colorsCode
)

[SpacePhoenix]

@milotche

ingredient recette
orange Cocktail
pois Cocktail
rhum Cocktail
pois Riz
pomme Riz
rhum Riz
rhum Tarte
pois Tassau
carotte Tassau

Now if ‘ingredient1’ = ‘pois’ and ‘ingredient2’ = 'rhum 'the query should return Cocktail and Riz

If that is how the table is then this will work (Rudy will probably come up with an easier way

SELECT
    recette_1 AS recette
FROM
    (
        SELECT
              ingredient AS ingredient_1
            , recette AS recette_1
        FROM
            recipe
        WHERE
            ingredient = 'pois'
    ) AS ingredient_1
INNER JOIN
    (
        SELECT
              ingredient AS ingredient_2
            , recette AS recette_2
        FROM
            recipe
        WHERE
            ingredient = 'rhum'        
    ) AS ingredient_2
ON ingredient_1.recette_1 = ingredient_2.recette_2

[milotche]

I’ve update the query for :

SELECT recette From (select recette From tbl_ingredient WHERE ingredient = ‘“& ingredient2 &”’ ) as recette where ingredient = ‘“& ingredient1 &”’ GROUP BY recette

I got this error

Unknown column ‘ingredient’ in ‘where clause’

So I added the ingredient field in the query

SQL = “SELECT recette,ingredient From (select recette,ingredient From tbl_ingredient WHERE ingredient = '”& ingredient2 &“’ ) as recette where ingredient = '”& ingredient1 &“’ GROUP BY recette”

Works but I dont get the expected result

I wanted to get records that have 2 similar field
ex.

ingredient recette
orange Cocktail
pois Cocktail
rhum Cocktail
pois Riz
pomme Riz
rhum Riz
rhum Tarte
pois Tassau
carotte Tassau

Now if ‘ingredient1’ = ‘pois’ and ‘ingredient2’ = 'rhum 'the query should return Cocktail and Riz

[r937]

try to avoid using the same identifier for two different things in a query, like naming the derived table the same as a column

but that’s not the big problem here

the derived table consists of only one column – recette

however, the outer query is trying to retrieve something based on a condition in the WHERE clause using the ingredient column

that’s gonna fail, because that column doesn’t exist in the derived table

furthermore, it is doubtful that GROUP BY is needed here

[milotche]

Hi I got this problem,

Every derived table must have its own alias

From that query

SQL = “SELECT recette From (select recette From tbl_ingredient WHERE ingredient = '”& ingredient2 &“’ ) where ingredient = '”& ingredient1 &“’ GROUP BY recette”

[r937]

okay try this –

SELECT recette
  FROM tbl_ingredient 
 WHERE ingredient = '"& ingredient1 &"' 
    OR ingredient = '"& ingredient2 &"' 
GROUP 
    BY recette
HAVING COUNT(*) = 2

easily extensible to 3 ingredients, whereas the join method will require a third derived table

[SpacePhoenix]

@milotche

Hi I got this problem,

Every derived table must have its own alias

From that query

SQL = “SELECT recette From (select recette From tbl_ingredient WHERE ingredient = '”& ingredient2 &“’ ) where ingredient = '”& ingredient1 &“’ GROUP BY recette”

    SELECT
        recette
    FROM
        (
            SELECT
                recette
            FROM
                tbl_ingredient
            WHERE
                ingredient = '"& ingredient2 &"'
        ) AS recette
    WHERE
        ingredient = '"& ingredient1 &"'
    GROUP BY
        recette

This is the derived table in the query:

(SELECT
                recette
            FROM
                tbl_ingredient
            WHERE
                ingredient = '"& ingredient2 &"'
        ) AS recette
    

[r937]

@omid020

This is my query

okay, let’s prune away some of the trees so that you can see the forest –

SELECT stuff
  FROM ( SELECT stuff
           FROM bunchofjoins
         UNION ALL
         SELECT stuff
           FROM bunchofjoins
         UNION ALL
         SELECT stuff
           FROM sometable
         UNION ALL
         SELECT stuff
           FROM ( Select stuff
                   FROM ( SELECT stuff
                            From ( Select stuff
                                     From bunchofjoins
                                   UNION All
                                   Select stuff
                                     From bunchofjoins
                                 ) As st
                        ) AS tt
                )
      ) AS ft

can you spot where a derived table is missing an alias now?

[omid020]

let’s prune away some of the trees so that you can see the forest

Ok , this is more regular :

>SELECT  ft.projectName
>        ,ft.leafCode
>        ,ft.leafName
>        ,ft.colorsCode
>        ,ft.polish
>        ,ft.pack
>        ,ft.warehouse
>        ,ft.sent
>        ,ft.orderQuota
>        ,COALESCE(((ft.polish + ft.pack + ft.warehouse) - (ft.orderQuota)), 0) AS `available`
>        ,ft.orderAmount
>        ,COALESCE((ft.orderAmount) - (ft.polish + ft.pack + ft.warehouse + ft.sent - (ft.orderQuota)), 0) AS `projectRemained`
>        ,COALESCE((((ft.polish + ft.pack + ft.warehouse + ft.sent) - (ft.orderQuota)) / (ft.orderAmount)), 0) AS `projectProgress`
>FROM (
>SELECT	projects.projectName
>		,rs_leaves.leafCode
>		,rs_leaves.leafName
>		,rs_colors.colorsCode
>		,NULL AS `polish`
>		,NULL AS `pack`
>		,NULL AS `warehouse`
>		,SUM(assign_details.assAmount) AS `sent`
>		,projects_details.orderQuota
>		,projects_details.orderAmount
>FROM projects_details
>INNER JOIN rs_leaves
>ON rs_leaves.leafID = projects_details.leafName
>INNER JOIN rs_colors
>ON rs_colors.colorsID = projects_details.leafColor
>INNER JOIN assign_details
>ON assign_details.projectID = projects_details.projectID
>INNER JOIN projects
>ON projects.projectID = projects_details.projectID
>Group By rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
>UNION ALL
>SELECT	NULL AS `projectNmae`
>		,NULL AS `leafCode`
>		,NULL AS `leafName`
>		,NULL AS `colorsCode`
>		,COALESCE(Sum(patine_tbl.receivedNum), 0) - COALESCE(Sum(Case
>    	When sent_receive_tbl.stationID = '6' Then sent_receive_tbl.sentNum
>    	Else Null End), 0) As `ploish`
>    	,NULL AS `pack`
>    	,NULL AS `warehouse`
>    	,NULL AS `sent`
>    	,NULL AS `orderQuota`
>    	,NULL AS `orderAmount`
>FROM patine_tbl
>INNER JOIN rs_leaves
>ON rs_leaves.leafID = patine_tbl.leafID
>INNER JOIN rs_colors
>ON rs_colors.colorsID = patine_tbl.colorsID
>LEFT JOIN sent_receive_tbl
>ON sent_receive_tbl.leafID = rs_leaves.leafID
>AND sent_receive_tbl.colorsID = rs_colors.colorsID
>Group By rs_leaves.leafCode,
>  rs_leaves.leafName,
>  rs_colors.colorsCode
>UNION ALL
>SELECT	NULL AS `projectNmae`
>		,NULL AS `leafCode`
>		,NULL AS `leafName`
>		,NULL AS `colorsCode`
>		,NULL AS `polish`
>		,COALESCE((Sum(sent_receive_tbl.receivedNum) - Sum(sent_receive_tbl.sentNum)), 0) As `pack`
>    	,NULL AS `warehouse`
>    	,NULL AS `sent`
>    	,NULL AS `orderQuota`
>    	,NULL AS `orderAmount`
>FROM sent_receive_tbl
>Where rs_stations.stationID = '7'
>Group By rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode
>UNION ALL
>SELECT	NULL AS `projectNmae`
>		,NULL AS `leafCode`
>		,NULL AS `leafName`
>		,NULL AS `colorsCode`
>		,NULL AS `polish`
>		,NULL AS `pack`
>    	,tt.warehouse
>    	,NULL AS `sent`
>    	,NULL AS `orderQuota`
>    	,NULL AS `orderAmount`
>		FROM (SELECT NULL AS `projectNmae`
>					,NULL AS `leafCode`
>					,NULL AS `leafName`
>					,NULL AS `colorsCode`
>					,NULL AS `polish`
>					,NULL AS `pack`
>    				,st.warehouse
>    				,NULL AS `sent`
>    				,NULL AS `orderQuota`
>    				,NULL AS `orderAmount`
>						FROM (SELECT st.leafCode,
>  									st.colorsCode,
>  									COALESCE(Sum(st.INPUT), 0) As INPUT,
>  									COALESCE(Sum(st.OUTPUT), 0) As OUTPUT,
>  									COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse`
>										FROM (SELECT rs_leaves.leafCode
>										,rs_colors.colorsCode
>     									,Sum(receipt_details.recAmount) As INPUT
>     									,NULL As OUTPUT
>    									FROM receipt_details
>    									INNER JOIN rs_leaves
>    									ON rs_leaves.leafID = receipt_details.leafName
>    									INNER JOIN rs_colors
>    									ON rs_colors.colorsID = receipt_details.leafColor
>    									Group By rs_leaves.leafCode, rs_colors.colorsCode
>    									UNION All
>    									SELECT rs_leaves.leafCode
>    									,rs_colors.colorsCode
>    									,NULL As INPUT
>    									,Sum(assign_details.assAmount) As OUTPUT
>    									FROM assign_details
>    									INNER JOIN rs_leaves
>    									ON rs_leaves.leafID = assign_details.leafName
>    									INNER JOIN rs_colors
>    									ON rs_colors.colorsID = assign_details.leafColor
>										Group By rs_leaves.leafCode, rs_colors.colorsCode
>										) As st
>							Group By st.leafCode,st.colorsCode
>					    	) AS tt
>				Group By tt.leafCode,tt.colorsCode
>			)
>     ) AS ft
>Group By ft.projectName
>,ft.leafCode
>,ft.leafName
>,ft.colorsCode

Or this screenshot :

can you spot where a derived table is missing an alias now?

No , sorry I cant see related alert concept , also MySQL Query Browser application doesnt poke me on any specific table

[r937]

@omid020

can you spot where a derived table is missing an alias now?
No , sorry

can you see it now –

SELECT stuff
  FROM [COLOR="Indigo"]( SELECT stuff
           FROM bunchofjoins
         UNION ALL
         SELECT stuff
           FROM bunchofjoins
         UNION ALL
         SELECT stuff
           FROM sometable
         UNION ALL
         SELECT stuff
           FROM[/COLOR] [COLOR="Red"]( Select stuff
                   FROM[/COLOR] [COLOR="Blue"]( SELECT stuff
                            From[/COLOR] [COLOR="DarkGreen"]( Select stuff
                                     From bunchofjoins
                                   UNION All
                                   Select stuff
                                     From bunchofjoins
                                 ) As st   [B]-- this derived table has an alias[/B][/COLOR]
                        [COLOR="blue"]) AS tt   [B]-- this derived table has an alias[/B][/COLOR]
                [COLOR="red"])[/COLOR]
      [COLOR="purple"]) AS ft   [B]-- this derived table has an alias[/B][/COLOR]

[omid020]

Oh , sorry , I don`t know where is my head striked ; I found it ; Now I have other bugs but I think that I can solve them .
TNX