'Combining the results of two queries into one line [closed]

I have a query with UNION, in which it is required to output the results in a single line. I tried using group by, but an error occurred. Maybe there is a solution in which the current query can be combined into a single line?

select a.RecordID, (ACADEMICYEAR(sysdate) - c.year_begin + 1) COURSE, c.year_begin year_begin, c.Name StGroup
from fc_StudentOrders a   
left join fc_OrderTypes b on b.TypeID=a.OrderType   
left join fc_StudentGroups c on c.Code=a.StudentGroupID   
left join RB_DEPARTMENTS d on d.code = c.faculty  
where a.RecordID = 205838 and a.ORDERTYPE IN (15,56,109)
union all 
select a.RecordID, (ACADEMICYEAR(sysdate) - c.year_begin + 1) COURSE, c.year_begin year_begin, c.Name StGroup
from fc_StudentOrders a   
left join fc_OrderTypes b on b.TypeID=a.OrderType   
left join fc_StudentGroups c on c.Code=a.StudentGroupID   
left join RB_DEPARTMENTS d on d.code = c.faculty  
where a.RecordID = 205838 and a.ORDERTYPE IN (1)

Screenshot with results: here

sqloracle


Solution 1:[1]

If I understood you correctly, this might be one option: split queries into two (temp1 and temp2) and then JOIN them:

WITH
   temp1
   AS
      (SELECT a.RecordID,
              (ACADEMICYEAR (SYSDATE) - c.year_begin + 1) COURSE,
              c.year_begin year_begin,
              c.Name StGroup
         FROM fc_StudentOrders a
              LEFT JOIN fc_OrderTypes b ON b.TypeID = a.OrderType
              LEFT JOIN fc_StudentGroups c ON c.Code = a.StudentGroupID
              LEFT JOIN RB_DEPARTMENTS d ON d.code = c.faculty
        WHERE     a.RecordID = 205838
              AND a.ORDERTYPE IN (15, 56, 109)),
   temp2
   AS
      (SELECT a.RecordID,
              (ACADEMICYEAR (SYSDATE) - c.year_begin + 1) COURSE,
              c.year_begin year_begin,
              c.Name StGroup
         FROM fc_StudentOrders a
              LEFT JOIN fc_OrderTypes b ON b.TypeID = a.OrderType
              LEFT JOIN fc_StudentGroups c ON c.Code = a.StudentGroupID
              LEFT JOIN RB_DEPARTMENTS d ON d.code = c.faculty
        WHERE     a.RecordID = 205838
              AND a.ORDERTYPE IN (1))
SELECT a.recordid,
       a.course,
       a.year_begin,
       a.stgroup,
       b.strgoup
  FROM temp1 a
       JOIN temp2 b
          ON     a.recordid = b.recordid
             AND a.course = b.course
             AND a.year_begin = b.year_begin;

Solution 2:[2]

You could start by simplifying your query as follow

select a.RecordID, (ACADEMICYEAR(sysdate) - c.year_begin + 1) COURSE, c.year_begin year_begin, c.Name StGroup
    from fc_StudentOrders a   
    left join fc_OrderTypes b on b.TypeID=a.OrderType   
    left join fc_StudentGroups c on c.Code=a.StudentGroupID   
    left join RB_DEPARTMENTS d on d.code = c.faculty  
    where a.RecordID = 205838 and a.ORDERTYPE IN (1,15,56,109)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot
Solution 2 JohanB

Related Questions