Błąd w zapytaniu SQL: Every derived table must have its own alias

Dostaję na to zapytanie błąd: Every derived table must have its own alias
Czy ktoś coś widzi?

select U.`Name`,U.`Surname`,if(U.`Sex`,'Pani','Pan') ChSex,if(U.`Sex`,'Pani','Panie') CallSex,U.`Email`,
(
   select 
      group_concat(
         concat(
             (
                select group_concat(concat(RL.`Surname`,' ',RL.`Name`) separator ', ')
                from `rpt_authors` RA,`rpt_aliases` RL
                where
                   true
                   and RA.`ReportId`=R.`Id`
                   and RL.`Id`=RA.`AliasId`
                group by RA.`ReportId`
                order by RL.`Surname`<>L.`Surname`,RL.`Surname`
             ),
             ': ',
             R.`Title`,', ',
             R.`Number`,', ',
             R.`Year`
         )
         separator '\r'
      )
   from
      `rpt_reports` R,`rpt_authors` A,`rpt_aliases` L
   where
      true
      and L.`UserId`=U.`Id`
      and A.`AliasId`=L.`Id`
      and R.`Id`=A.`ReportId`
	  and U.`Id` in
	  (
	     select
      	  `UserId`
	     from
	     (
	        select 
         	  count(A1.`ReportId`) Cnt,L1.`UserId`
	        from 
      	     `rpt_authors` A1,`rpt_aliases` L1 
	        where 
               L1.`UserId` in 
               (
	  	          select
		  	         L2.`UserId`
	              from
      	             `rpt_authors` A2,`rpt_aliases` L2
	              where
	                 A2.`ReportId`=A.`ReportId`
               ) 
               and A1.`AliasId`=L1.`Id`
               and (select count(A3.`AliasId`) from `rpt_authors` A3 where A3.`ReportId`=A1.`ReportId`)>1
	        group by L1.`UserId`
	        order by Cnt desc
	     )
	     limit 1
	  )
   group by L.`UserId`
) Reports,
(
   select 
      min(R.`Year`)
   from
      `rpt_reports` R,`rpt_authors` A,`rpt_aliases` L
   where
      true
      and L.`UserId`=U.`Id`
      and A.`AliasId`=L.`Id`
      and R.`Id`=A.`ReportId`
	  and U.`Id` in
	  (
	     select
      	  `UserId`
	     from
	     (
	        select 
         	  count(A1.`ReportId`) Cnt,L1.`UserId`
	        from 
      	     `rpt_authors` A1,`rpt_aliases` L1 
	        where 
               L1.`UserId` in 
               (
	  	          select
		  	         L2.`UserId`
	              from
      	             `rpt_authors` A2,`rpt_aliases` L2
	              where
	                 A2.`ReportId`=A.`ReportId`
               ) 
               and A1.`AliasId`=L1.`Id`
               and (select count(A3.`AliasId`) from `rpt_authors` A3 where A3.`ReportId`=A1.`ReportId`)>1
	        group by L1.`UserId`
	        order by Cnt desc
	     )
	     limit 1
	  )
   group by L.`UserId`
) MinYear,
(
   select 
      max(R.`Year`)
   from
      `rpt_reports` R,`rpt_authors` A,`rpt_aliases` L
   where
      true
      and L.`UserId`=U.`Id`
      and A.`AliasId`=L.`Id`
      and R.`Id`=A.`ReportId`
	  and U.`Id` in
	  (
	     select
      	  `UserId`
	     from
	     (
	        select 
         	  count(A1.`ReportId`) Cnt,L1.`UserId`
	        from 
      	     `rpt_authors` A1,`rpt_aliases` L1 
	        where 
               L1.`UserId` in 
               (
	  	          select
		  	         L2.`UserId`
	              from
      	             `rpt_authors` A2,`rpt_aliases` L2
	              where
	                 A2.`ReportId`=A.`ReportId`
               ) 
               and A1.`AliasId`=L1.`Id`
               and (select count(A3.`AliasId`) from `rpt_authors` A3 where A3.`ReportId`=A1.`ReportId`)>1
	        group by L1.`UserId`
	        order by Cnt desc
	     )
	     limit 1
	  )
   group by L.`UserId`
) MaxYear

from `rpt_users` U
where 
   true
;

CREATE TABLE `rpt_users` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Sex` tinyint(1) unsigned DEFAULT NULL,
 `Birthday` int(11) DEFAULT '0',
 `LanguageId` int(11) DEFAULT NULL,
 `Title` tinytext CHARACTER SET utf8,
 `Name` char(64) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
 `Surname` char(64) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
 `Adres` tinytext CHARACTER SET utf8,
 `City` tinytext CHARACTER SET utf8,
 `Postal` tinytext CHARACTER SET utf8,
 `WhereIs` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `Email` tinytext CHARACTER SET utf8,
 `Phone` tinytext CHARACTER SET utf8,
 `Domains` mediumtext CHARACTER SET utf8,
 `Links` mediumtext CHARACTER SET utf8,
 `Info` mediumtext CHARACTER SET utf8,
...

CREATE TABLE `rpt_aliases` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `UserId` int(11) NOT NULL,
 `Name` char(64) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
 `Surname` char(64) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
...

CREATE TABLE `rpt_reports` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Number` char(16) CHARACTER SET utf8 NOT NULL,
 `LanguageId` int(11) DEFAULT NULL,
 `Title` mediumtext CHARACTER SET utf8,
 `Year` int(11) NOT NULL,
...

CREATE TABLE `rpt_authors` (
 `ReportId` int(11) NOT NULL,
 `AliasId` int(11) NOT NULL
...

Wydaje mi się, że błąd jest w 80-81 linijce. Alias A tak "głęboko" nie sięga...

Może jakiś pomysł jak to obejść?

A nie jest tak jak w postgresie, że trzeba sobie nazwać blok w którym mamy podzapytanie?

np.

 (
                  select
                     L2.`UserId`
                  from
                     `rpt_authors` A2,`rpt_aliases` L2
                  where
                     A2.`ReportId`=A.`ReportId`
               )  <-- nie jest nazwana

Czyli problemem są linie: 49,54,56,88,93,127 i brak aliasów dla tych 'dervied tables'?

yarel napisał(a):

A nie jest tak jak w postgresie, że trzeba sobie nazwać blok w którym mamy podzapytanie?

np.

 (
                  select
                     L2.`UserId`
                  from
                     `rpt_authors` A2,`rpt_aliases` L2
                  where
                     A2.`ReportId`=A.`ReportId`
               )  <-- nie jest nazwana

Czyli problemem są linie: 49,54,56,88,93,127 i brak aliasów dla tych 'dervied tables'?

To nie musi mieć, bo to jest w IN

Trochę uprościłem, teraz błąd: Nieznana kolumna 'R2.Id' w where clause

select 
	X.Name,X.Surname,X.ChSex,X.CallSex,X.Email,X.Reports,X.MinYear,X.MaxYear
from
(
	select 
		U.`Name`,U.`Surname`,if(U.`Sex`,'Pani','Pan') ChSex,if(U.`Sex`,'Pani','Panie') CallSex,U.`Email`,
		group_concat(
			concat(
				(
					select group_concat(concat(RL.`Surname`,' ',RL.`Name`) separator ', ')
					from `rpt_authors` RA,`rpt_aliases` RL
					where
						true
						and RA.`ReportId`=R.`Id`
						and RL.`Id`=RA.`AliasId`
					group by RA.`ReportId`
					order by RL.`Surname`<>L.`Surname`,RL.`Surname`
				),
				': ',
				R.`Title`,', ',
				R.`Number`,', ',
				R.`Year`
			)
			separator '\r'
		) Reports,
		min(R.`Year`) MinYear,
		max(R.`Year`) MaxYear
	from
		`rpt_reports` R,`rpt_authors` A,`rpt_aliases` L,`rpt_users` U
	where
		U.`Id` in
		(
			select
				Z.`UserId`
			from
				`rpt_reports` R2, # STĄD
				(
					select 
					count(A1.`ReportId`) Cnt,L1.`UserId`
					from 
						`rpt_authors` A1,`rpt_aliases` L1,`rpt_reports` R1
					where
						true and
					    R1.`Id`=R2.`Id` and # NIE PRZECHODZI TU
					    L1.`UserId` in 
					    (
							select
								L2.`UserId`
							from
								`rpt_authors` A2,`rpt_aliases` L2
							where
								A2.`ReportId`=R1.`Id`
						) 
						and A1.`AliasId`=L1.`Id`
						and (select count(A3.`AliasId`) from `rpt_authors` A3 where A3.`ReportId`=A1.`ReportId`)>1
						group by L1.`UserId`
						order by Z.`Cnt` desc
						limit 1
				) Z
			where R2.`Id`=R.`Id`
		) 
		and L.`UserId`=U.`Id`
		and A.`AliasId`=L.`Id`
		and R.`Id`=A.`ReportId`
		group by L.`UserId`
	) X
where 
	true
;

Może da się jakoś inaczej z tego wybrnąć?
Albo inaczej zapisać ...
Struktura:

• Każdy rpt_user U powiązany 1:N do rpt_aliases L przez U.Id=L.UserId
• Rekordy z tablicy rpt_aliases L powiązane N:N do rekordów rpt_reports R za pomocą tablicy rpt_authors A przez L.Id=A.AliasId and A.ReportId=R.Id

Czyli każdy naukowiec user ma kilka aliasów (np P.Kowalski, P.A.Kowalski) oraz piszę raporty ze współautorami podpisując się jednym z aliasów.

Potrzebuje zrobić dane do korespondencji seryjnej:
Dla każdego naukowca wybrać tylko te raporty, które on napisał nie solo, oraz tylko te gdzie on jest "Głównym".
"Główny" to ten kto ma w bazie więcej raportów wespół z innymi naukowcami.
Brzmi niby prosto :D

Ja bym podszedł do tego w ten sposób:

select 
    r.id reportid
	,substring_index(GROUP_CONCAT(g.id order by g.ui desc),',',1)  autor_glowny
    ,group_concat(concat(ral.`Surname`,' ',ral.`Name`) separator ', ')
from
    rpt_reports r
    inner join rpt_authors ra on ra.ReportId = r.id
    inner join rpt_aliases ral on ral.Id = ra.AliasId
    inner join (SELECT 
	               u.Id
					, count(distinct reportid) ui 
				FROM 
					`rpt_users` u 
					inner join rpt_aliases al on al.UserId=u.id
					inner join rpt_authors au on au.AliasId=al.id
				where 
					au.ReportId in (select reportid from rpt_authors group by reportid having count(reportid) > 1)
				group by 
					u.Id) g on g.id = ral.userid
group by
	r.id
having
	count(r.id) > 1

Ciężko przetestować bez danych, ale koncept powinien być zrozumiały. Wystarczy dodać joina do tabeli rpt_users i rozbudować selecta

Udało mi się popełnić to, działa (Dzięki @Panczo):

select 
	U.`Name`,U.`Surname`,
	if(U.`Sex`,'Szanowna','Szanowny') SzSex,
	if(U.`Sex`,'Pani','Pan') ChSex,
	if(U.`Sex`,'Pani','Panie') CallSex,
	if(U.`Sex`,'am','em') SfxSex,
	U.`Email`,
	group_concat(
		concat(
			(
				select group_concat(concat(RL.`Surname`,' ',RL.`Name`) separator ', ')
				from `rpt_authors` RA,`rpt_aliases` RL
				where
					true
					and RA.`ReportId`=R.`Id`
					and RL.`Id`=RA.`AliasId`
				group by RA.`ReportId`
				order by RL.`Surname`<>L.`Surname`,RL.`Surname`
			),
			': ',
			R.`Title`,', ',
			R.`Number`,', ',
			R.`Year`
		)
		separator '\r'
	) `Reports`,
	min(R.`Year`) `MinYear`,
	max(R.`Year`) `MaxYear`
from
	`rpt_users` U
inner join `rpt_aliases` L on L.`UserId`=U.`Id`
inner join 
	(
		select
			A4.`ReportId`,A4.`AliasId`
		from
			`rpt_authors` A4
		inner join
			(
				select
					P.`ReportId`,max(P.`PtId`) `MaxId`
				from
					(
						select
							A3.`ReportId`,C.`Points`*1000000+A3.`AliasId` `PtId`
						from
							`rpt_authors` A3
						inner join
							(
								select
									L2.`Id`,U0.`Weight`*count(A2.`AliasId`) `Points`
								from
                                    `rpt_aliases` L2,
									`rpt_aliases` L1,
									`rpt_users` U0,
									`rpt_authors` A1,
									`rpt_authors` A2
								where
                                    U0.`Id`=L2.`UserId` and
									L1.`UserId`=U0.`Id` and
									A1.`AliasId`=L1.`Id` and
									A2.`ReportId`=A1.`ReportId` and
									A2.`AliasId`<>A1.`AliasId` and
									true
								group by
									L2.`Id`
							) C on C.`Id`=A3.`AliasId`
						where
							true
					) P
				group by
					P.`ReportId`
			) M on M.`ReportId`=A4.`ReportId` and M.`MaxId`%1000000=A4.`AliasId`
		where
			true
		order by 
			A4.`AliasId`,A4.`ReportId`
	) UR on UR.`AliasId`=L.`Id`
inner join `rpt_reports` R on R.`Id`=UR.`ReportId`
where 
	length(U.`Email`)>2 and
	(select count(Z.`AliasId`) from `rpt_authors` Z where Z.`ReportId`=R.`Id`)>1 and
	U.`WhereIs`>0 and
	true
group by
	U.`Id`
#having length(`Reports`)>2 and
;

Jakieś pomysły na optymalizację?

Strasznie nie lubię tej skladni z tak zapisanym join-em...

Nie wiem jak to optymalizować, ale za dużo tam podzapytań, wystarczy dodać tak:

select 
	U.`Name`,U.`Surname`,
    if(U.`Sex`,'Szanowna','Szanowny') SzSex,
    if(U.`Sex`,'Pani','Pan') ChSex,
    if(U.`Sex`,'Pani','Panie') CallSex,
    if(U.`Sex`,'am','em') SfxSex,
    U.`Email`,
	group_concat(concat(autorzy,':', `Title`,', ',`Number`,', ', `Year`) separator '\r') `Reports`,
	min(`Year`) `MinYear`,
    max(`Year`) `MaxYear`
from (
	select 
		r.id reportid
		,r.Year
		,r,Title
		,r.Number
		,substring_index(GROUP_CONCAT(g.id order by g.ui desc),',',1)  autor_glowny
		,group_concat(concat(ral.`Surname`,' ',ral.`Name`) separator ', ') autorzy
	from
		rpt_reports r
		inner join rpt_authors ra on ra.ReportId = r.id
		inner join rpt_aliases ral on ral.Id = ra.AliasId
		inner join (SELECT 
					   u.Id
						, count(distinct reportid) ui 
					FROM 
						`rpt_users` u 
						inner join rpt_aliases al on al.UserId=u.id
						inner join rpt_authors au on au.AliasId=al.id
					where 
						au.ReportId in (select reportid from rpt_authors group by reportid having count(reportid) > 1)
					group by 
						u.Id) g on g.id = ral.userid
	group by
		r.id
		,r.Year
		,r,Title
		,r.Number
	having
		count(r.id) > 1) rpts
	inner join rpt_users u on u.id=rpts.autor_glowny
where
	length(U.`Email`)>2
	and U.`WhereIs`>0
group by
   u.id

Pozostaje sprawdzić empirycznie co jest optymalniejsze.

Dla mnie "logiczniejsze" jest wychodzenie od raportów bo to ich szukam, a nie userów...