Recursive Query in MYSQL?

Question

I think my problem is solved with a 'recursive query', but since MySQL doesn't support recursive queries I was trying to use the adjacent list model. This should not be a problem since I know how deep I want to go.

Here's an example of what I need to do: Table Classes:

dept    classNum    prereqDept  prereqClassNum
BIO     465         BIO         335
EE      405         EE          325
EE      325         EE          120
BIO     465         EE          120
BIO     335         BIO         225
BIO     225         CHEM        110
BIO     225         BIO         105

What I need is all the classes of a certain level (let's say 400) with all their prerequisites up to 3 levels deep. So I'd get something like

dept    classNum    prereqDept  prereqClassNum
BIO     465         BIO         335
BIO     465         BIO         225
BIO     465         CHEM        110
BIO     465         BIO         105
EE      405         EE          325
EE      405         EE          120
....

I know I need to use 3-LEFT JOINs if I want to go 3 levels deep, but I can't figure out how to set up these joins to get what I need. Any ideas? I'll appreacite your help!

P.S. I can't change the table structure at all.

Answer 1

I've run this and it gets all classes with (in this sample) up to 3 pre-requisites, but could be expanded to 4, 5, 6 just by copying the pairs based on "IDSeq = ?".

The critical element here is to get a number assigned to each record based on the common Dept + ClassNum each time starting with 1 at the beginning of each time the group changes. To do this, I've applied SQL Variables to FIRST Make sure that each group is sequenced 1, 2, 3... 1, 2, ... 1, ... 1, 2, 3, 4, 5... etc...

Result of Inner Query

Once this is done, we can do a simple group by with no other complex joining, joining, unioning, etc... Just apply a max() of an IF() based on the known sequence. As you can see the pattern, I'm getting whatever the row has for its prerequisite Dept and ClassNum provided that is the "1"st record, then again on the "2"nd, and "3"rd, but could be applied for the "4"th, "5"th, etc.

By using the max( if() ), every class will always have a 1 sequence, but only sometimes will have a 2, let alone a 3, 4 or 5. So, if there is NOT a value, it at least gets filled with blank spaces so it won't show null. Then, if/when there IS a value, the MAX() will supercede the blank spaces when it hits...

The final query is amazing and probably JUST what you need.

select
      NewSet.Dept,
      NewSet.ClassNum,
      max( if( NewSet.IDSeq = 1, NewSet.PreReqDept, '    ' )) FirstDept,
      max( if( NewSet.IDSeq = 1, NewSet.PreReqClassNum, '    ' )) FirstClassNum,
      max( if( NewSet.IDSeq = 2, NewSet.PreReqDept, '    ' )) SecondDept,
      max( if( NewSet.IDSeq = 2, NewSet.PreReqClassNum, '    ' )) SecondClassNum,
      max( if( NewSet.IDSeq = 3, NewSet.PreReqDept, '    ' )) ThirdDept,
      max( if( NewSet.IDSeq = 3, NewSet.PreReqClassNum, '    ' )) ThirdClassNum
   from 
      ( select 
             @orig := @orig +1 as OrigSeq,
             @seq := if( concat(P.Dept, P.ClassNum ) = @LastGrp, @seq +1, 1 ) as IDSeq,
             @LastGrp := concat( P.Dept,  P.ClassNum ) NextGrp,
             P.Dept,
             P.ClassNum,
             P.PreReqDept,
             P.PreReqClassNum
          from 
             PreReqs P,
             ( select @orig := 0, @seq := 0, @LastGrp := '' ) x
          order by 
             Dept,
             ClassNum ) NewSet
   group by
      NewSet.Dept,
      NewSet.ClassNum
   order by 
      NewSet.Dept,
      NewSet.ClassNum