Satu pertanyaan SQL yang akan menggunakan satu hasil SELECT dalam subquery seterusnya dalam permintaan yang sama

Question

Saya sedang mencari kemungkinan untuk (semula) menggunakan hasil satu pilihan SQL untuk mendapatkan nilai sub-pilihan yang berbeza dalam pertanyaan yang sama

Pertama, saya harus mendapatkan $ValueToUse dengan memilih seperti ini:

SELECT `cb_campaignid` as ValueToUse FROM `#__team_groups` WHERE `id` = '$Value'

Untuk hasil "ValueToUse", saya ingin membuat pilihan seterusnya dalam pernyataan pertanyaan SQL yang sama jika boleh.

SELECT coalesce (nullif (SUM(Amount), ''), '0') as general_amount, 
(SELECT SUM(c.cb_stand) FROM #__employees c, #__team_users u where c.user_id=u.user_id and u.group=(select id from #__team_groups where cb_campaignid='$ValueToUse')) as teamleden_tussenstand, 
(select title from #__customer_campaigns where id='$ValueToUse') as title, 
(select id from #__team_groups where cb_campaignid='$ValueToUse') as groupID, 
(select goal from #__customer_campaigns where id='$ValueToUse') as goal
 FROM #__customer_payments WHERE published = 1 and campaign_id = '$ValueToUse'

Apakah cara terbaik untuk mencapai keputusan ini?

Answer 1

Bagaimana pula dengan menggunakan hasil pertama sebagai pembolehubah?

SELECT @ValueToUse:=
(SELECT `cb_campaignid` as ValueToUse FROM `#__team_groups` WHERE `id` = '$Value');

SELECT coalesce (nullif (SUM(Amount), ''), '0') as general_amount, 
(SELECT SUM(c.cb_stand) FROM #__employees c, #__team_users u where c.user_id=u.user_id and u.group=(select id from #__team_groups where cb_campaignid=@ValueToUse)) as teamleden_tussenstand, 
(select title from #__customer_campaigns where id=@ValueToUse) as title, 
(select id from #__team_groups where cb_campaignid=@ValueToUse) as groupID, 
(select goal from #__customer_campaigns where id=@ValueToUse) as goal
 FROM #__customer_payments WHERE published = 1 and campaign_id = @ValueToUse;