Join 2 tables or....?

[haymarketpaul]

QV10 SR2

I have the following 2 tables (from a 3rd party source):

Tab1:

Question            Answer                Choice

Job Function        District Nurse         01

Job Function        Practice Nurse        02

Job Function        Specialist Nurse     03

Job Function        Clinical Nurse         04

Job Function        Health Visitor          05

Specialist Areas   Oncology              01

Specialist Areas   Diabetes               02

Specialist Areas   Wound Care           03

Specialist Areas   A & E                    04

Specialist Areas   Travel Health          05

Industry Sector     Primary Care        01

Industry Sector     Secondary Care     02

Industry Sector     Private Sector        03

Tab2:

CustomerID    Question            Choice

1                     Job Function          01

1                     Specialist Areas     02

1                     Industry Sector       01

2                     Job Function          05

2                     Specialist Areas     03

3                     Job Function          02

3                     Specialist Areas     01

3                      Industry Sector      03

Here we can see that CustomerID 2 did not answer the Industry Sector Question hence no record in table 2 for that question.

I need to be able to display all the customers and their questions and answers but also include Questions they didn't answer, so for example i need to display something like this...

CustomerID    Question                    Answer

1                    Job Function                District Nurse

1                    Specialist Areas           Diabetes

1                     Industry Sector             Primary Care

2                    Job Function                 Health Visitor

2                    Specialist Areas           Wound Care

2                      Industry Sector           <Not Answered>

3                     Job Function               Practice Nurse

3                     Specialist Areas          Oncology

3                      Industry Sector           Private Sector

This shows CustomerID 2 with an Industry Sector of <Not Answered> so the user will be able to select <Not Answered> and see which customers have missed which questions.

I have been trying to use some kind of Join in my script but to no avail

Any ideas about how best to tackle this would be much appreciated.

[kaushiknsolanki]

Hi,

    Have a look at this Example.

    I hope this will solve your problem.

Regards,

Kaushik Solanki

[johnw · Accepted solution]

Here are three different and simpler approaches.  I suspect there's a simpler way still, which is why I have so many attempts, but I'm giving up for now.  My favorite so far is the second:

LEFT JOIN (Tab2)
LOAD *
RESIDENT Tab1
;
All:
LOAD num(fieldvalue('CustomerID',recno())) as CustomerID
AUTOGENERATE fieldvaluecount('CustomerID')
;
LEFT JOIN (All)
LOAD text(fieldvalue('Question',recno())) as Question
AUTOGENERATE fieldvaluecount('Question')
;
OUTER JOIN (Tab2)
LOAD *
RESIDENT All
;
Tab3:
NOCONCATENATE
LOAD
CustomerID
,Question
,alt(Choice,00) as Choice
,if(len(Answer),Answer,'<Not Answered>') as Answer
RESIDENT Tab2
;
DROP TABLES All, Tab1, Tab2
;

[haymarketpaul]

That's fantastic guys - thankyou very much.

Going to spend today looking at how they work