1 Subquery returns more than 1 row - MySQL

Формулировка задачи:

Господа, Hola! Есть запросик у меня и все вроде бы было хорошо и он отрабатывал, пока я не влепил подзапросы и он не выдал ошибочку: 1 Subquery returns more than 1 row...
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos,
  SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog,
  ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL,
  ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = 35 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date =
  (
  SELECT deal_date FROM deal WHERE deal_date BETWEEN '01.10.2017' AND '30.10.2017'
  )
 
UNION ALL
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos,
  SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog,
  ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL,
  ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = 36 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date =
  (
  SELECT deal_date FROM deal WHERE deal_date BETWEEN '01.10.2017' AND '30.10.2017'
  )
Пожалуйста о магистры знаний, укажите верный путь... В чем может быть проблема?

Код к задаче: «1 Subquery returns more than 1 row - MySQL»

textual
SET @vardt1 = '01.10.2017';
SET @vardt2 = '01.11.2017';
SET @vardt3 = '2017-10-01';
SET @vardt4 = '2017-10-30';
SET @vardt5 = '2017-11-01';
SET @vardt6 = '2017-11-30';
SET @varid1 = '34';
SET @varid2 = '35';
SET @varid3 = '36';
SET @varid4 = '71';
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid1 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid2 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid3 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid4 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid1 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid2 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid3 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid4 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6

10   голосов, оценка 3.500 из 5


СОХРАНИТЬ ССЫЛКУ