[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Use of subquery on the first select column

trying to use something like -- >
select lessonname, date1, (select date2 from table2) as dtdate2 from table 1

in order to display
lessonname   quiz (date1)   lab (date2)
---------------  -------------     ----
L1                  12/12/2011    11/12/2011
L2                   11/11/2011    11/10/2011

with the query below cannot proceed.
WITH ab(sa.actualtime) AS (select sa.actualtime from traineemodule tm 
INNER JOIN StudentAttendance sa ON tm.said = sa.id
INNER JOIN ClassType ct ON tm.ctid = ct.id
INNER JOIN Trainer tr ON tm.trid = tr.id
INNER JOIN Student st ON tm.sid = st.id
INNER JOIN Period p ON tm.pid = p.id
INNER JOIN Lesson l ON tm.lid = l.id
INNER JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = 'Dec2011' AND ct.classtype = 'LAP'
AND m.modulename = 'module01')
select m.modulename, l.lessonname, ct.classtype, sa.actualtime, ab
from traineemodule tm 
INNER JOIN StudentAttendance sa ON tm.said = sa.id
INNER JOIN ClassType ct ON tm.ctid = ct.id
INNER JOIN Trainer tr ON tm.trid = tr.id
INNER JOIN Student st ON tm.sid = st.id
INNER JOIN Period p ON tm.pid = p.id
INNER JOIN Lesson l ON tm.lid = l.id
INNER JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = 'Dec2011' AND ct.classtype = 'Quiz'
AND m.modulename = 'module01'

Open in new window

0
doramail05
Asked:
doramail05
  • 2
1 Solution
 
kamindaCommented:
Try this, if the subquery returns more than one value (depends on your data structure). You can use top 1 to get the perticular value you wanted together with a order by caluse.
SELECT 
m.modulename, 
l.lessonname, 
ct.classtype, 
sa.actualtime, 
(
SELECT actualtime FROM StudentAttendance saa 
INNER JOIN traineemodule tmm ON tmm.said = saa.id 
INNER JOIN ClassType ctt ON tmm.ctid = ctt.id
WHERE ctt.classtype = 'LAP' AND saa.id = sa.id
)
FROM traineemodule tm 
INNER JOIN StudentAttendance sa ON tm.said = sa.id
INNER JOIN ClassType ct ON tm.ctid = ct.id
INNER JOIN Trainer tr ON tm.trid = tr.id
INNER JOIN Student st ON tm.sid = st.id
INNER JOIN Period p ON tm.pid = p.id
INNER JOIN Lesson l ON tm.lid = l.id
INNER JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = 'Dec2011' AND ct.classtype = 'Quiz'
AND m.modulename = 'module01'

Open in new window

0
 
doramail05Author Commented:
if trying the query as attached, it will get this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

if it is right, it will produce the result,,
SELECT 
m.modulename, 
l.lessonname, 
ct.classtype, 
sa.actualtime, 
(
select sa1.actualtime from traineemodule tm 
INNER JOIN StudentAttendance sa1 ON tm.said = sa1.id
INNER JOIN ClassType ct ON tm.ctid = ct.id
INNER JOIN Trainer tr ON tm.trid = tr.id
INNER JOIN Student st ON tm.sid = st.id
INNER JOIN Period p ON tm.pid = p.id
INNER JOIN Lesson l ON tm.lid = l.id
INNER JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = 'Dec2011' AND ct.classtype = 'LAP'
AND m.modulename = 'module01'
)
FROM traineemodule tm 
INNER JOIN StudentAttendance sa ON tm.said = sa.id
INNER JOIN ClassType ct ON tm.ctid = ct.id
INNER JOIN Trainer tr ON tm.trid = tr.id
INNER JOIN Student st ON tm.sid = st.id
INNER JOIN Period p ON tm.pid = p.id
INNER JOIN Lesson l ON tm.lid = l.id
INNER JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = 'Dec2011' AND ct.classtype = 'Quiz'
AND m.modulename = 'module01'

Open in new window

0
 
kamindaCommented:
Yes I have mentioned that in my answer. it depends on your data, Anyhow subquery can return only 1 result per row. So limit it based on your requirment using a TOP 1 together with Order BY and also by applling further restrictions in the WHERE clause.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now