Re: MySQL query and sub query
Thanks Peter, after your suggestion below is the query
SELECT
campaigns_5.name AS `Campaign Name`,
adgroups_5.name AS `Adgroup Name`,
STNegData1W.query,
keywords_5.matchType AS `Match Type`,
Sum(metrics_5.clicks) AS `LT Clicks`,
Sum(metrics_5.costs) AS `LT Spend`,
Sum(metrics_5.orders) AS `LT Orders`,
metrics_5.sales AS `LT Sales`,
If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `LT ACOS`,
If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `LT CPC`,
STNegData4W.query,
STNegData4W.metricId,
STNegData4W.keywordId,
metrics_5.impressions
FROM
((((keywords_5 INNER JOIN campaigns_5 ON keywords_5.campaignId = campaigns_5.campaignId)
JOIN adgroups_5 ON keywords_5.adGroupId = adgroups_5.adGroupId)
JOIN STNegData1W ON keywords_5.keywordId = x.keywordId)
JOIN metrics_5 ON x.metricId = metrics_5.id
JOIN ( -- CORRELATED QUERY MOVED HERE
SELECT query, metricId, keywordId, Impressions
FROM (
SELECT searchterm_5.query, searchterm_5.metricId,
searchterm_5.keywordId, Sum(metrics_5.orders) AS `4W Orders`
FROM searchterm_5
JOIN metrics_5 ON searchterm_5.metricId = metrics_5.id
GROUP BY searchterm_5.query, searchterm_5.metricId, searchterm_5.keywordId, metrics_5.created_at
HAVING Sum(metrics_5.orders)=0 AND metrics_5.created_at > DATE_SUB('2018-05-10',INTERVAL 28 DAY)
) AS STNegData4W
JOIN metrics_5 ON STNegData4W.metricId = metrics_5.id
GROUP BY STNegData4W.query, STNegData4W.metricId, STNegData4W.keywordId,
metrics_5.impressions, metrics_5.created_at
HAVING (metrics_5.impressions) > 0
AND metrics_5.created_at > DATE_SUB('2018-05-10', INTERVAL 7 DAY)
) AS X JOIN metrics_5 ON x.metricId = metrics_5.id -- JUST MY GUESS CORRECT IF NEED BE
GROUP BY campaigns_5.name, adgroups_5.name, STNegData1W.query, keywords_5.matchType,
metrics_5.sales, campaigns_5.user_market_id, Right(campaigns_5.name,4)
HAVING Sum(metrics_5.clicks)>5
AND campaigns_5.user_market_id=12
AND Right(campaigns_5.name,4)="PFUS"
It gives error as below
12 Not unique table/alias: 'metrics_5' Query1.sql 20 7