SQL query (using joins) / recursion

Question

I have 3 tables: AuditCheckCriteria as T1, Learners as T2 & LearnerAuditRegistrations as T3 with the following structure:

  T1: CheckID     CheckName                      
          1       'Stage One'
          2       'Stage Two'
          3       'Stage Three'

  T2   LearnerID   LearnerName
          10         'John'
          11         'Peter'
          12         'Paul'

  T3: LearnerAuditID  LearnerID  CheckID
          1             10         1
          2             10         2
          3             11         1
          4             11         2
          5             11         3

I want the following output:

T2.LearnerID, T1.CheckID, T3.CheckID
      10           1           1
      10           2           2
      10           3          NULL
      11           1           1
      11           2           2
      11           3           3
      12           1          NULL
      12           2          NULL
      12           3          NULL

(T2 & T3 contain over 20000 rows each)

Any help is appreciated!

What part of the query are you having difficulty with? You have presented requirements, not a question. — D'Arcy Rittich
– D'Arcy Rittich, Commented Jul 24, 2012 at 14:17

Gordon Linoff · Accepted Answer · 2012-07-24 14:20:06Z

1

It looks like you want all combinations of values from T1 and T2, with the lookup from T3. If so, the following does this:

with t1vals as (select distinct checkID from t1),
     t2vals as (select distinct LearnerId from t2)
select t2vals.LearnerId, t1vals.CheckId, t3.CheckId
from t1vals cross join
     t2vals left outer join
     t3
     on t3.LearnerId = t2vals.LearnerId and
        t3.CheckId = t1vals.CheckId

answered Jul 24, 2012 at 14:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gulli Meel · Accepted Answer · 2012-07-24 14:18:49Z

0

Use (T2 cross join T1) left join T3..

answered Jul 24, 2012 at 14:18

Gulli Meel

8914 silver badges6 bronze badges

Collectives™ on Stack Overflow

SQL query (using joins) / recursion

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related