StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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 = ?".</p> <p>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...</p> <p>Result of Inner Query</p> <p><img src="https://i.stack.imgur.com/pi4fx.jpg" alt="Result of inner query"></p> <p>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.</p> <p>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...</p> <p>The final query is amazing and probably JUST what you need.</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload