4 Replies Latest reply: Mar 14, 2013 6:32 AM by Arun Paladugu RSS

    How to join more than one column?

      Hi friends,

       

      I have the following query in sql syntax:

       

      select stu.name,stu.regd_id,attd.address,mks.total from student stu,marks mks,attendance attd where stu.regd_id=mks.regd_id and stu.regd_id=attd.regd_id and attd.serial in (marks.number,12,76,89);

       

      My Intention is--- To display the student details with the  "student.ID" in marks and attendance tables and 'attd.serial' in either marks table or '12,76,89'

       

      Note: Student.ID will have multiple entries in attendance table and I need the result with the selected attd.serial.

       

      How to convert this SQL query to qlikview script?

       

      I have written this with the QV Community help

       

      load   name,       

               regd_id as id,

               

      FROM

      [C:\Documents and Settings\QV_QVD\student.qvd]

      (qvd);

       

      Inner Join

       

      load         

               regd_id as id,

               number as numb

      FROM

      [C:\Documents and Settings\QV_QVD\marks.qvd]

      (qvd);

       

      Inner Join

       

      load   regd_id as id

               serial,

               address,

              

      FROM

      [C:\Documents and Settings\QV_QVD\attendance.qvd]

      (qvd)where exists(numb,serial) or match(serial,12,76,89);

       

      --But I got the result set with all the student ID's which not desired.

       

       

      Please help, Any help is greatly appreciated.

        • Re: How to join more than one column?
          Sushil Kumar

          can u post the sample data?

            • Re: How to join more than one column?

              Hi Sushil,

               

              Existing sample set is

               

                   Student                                       Marks                                              Attendance

               

              Regd_id    Name                 Regd_id      Number                         Regd_id    Serial          Address

              --------------------------            ------------------------------------                  ----------------------------------------------------

              1001          arun                   1001            301                               1001        12             xyz

              1002          paul                                                                          1001        201            abc

                                                                                                               1001        301            pqr

               

               

              I want the result-set as

               

              Regd_id     Name          Serial           Address

              ---------------------------------------------------------------

              1001          arun             12                xyz

              1001          arun             301              pqr

               

              Serial No. '12' because i want it explicitly and '301' because it exists in "Marks" table.

            • Re: How to join more than one column?
              vishal waghole

              Hi,

               

              Please try this one.

               

              ATTD:
              load       regd_id as id,
                            serial,
                            address,
              FROM
              [C:\Documents and Settings\QV_QVD\attendance.qvd]
              (qvd)where exists(numb,serial) or match(serial,12,76,89);

              left Join (ATTD)
              load  name,
                       regd_id as id,
              FROM
              [C:\Documents and Settings\QV_QVD\student.qvd]
              (qvd);

              left Join (ATTD)
              load  regd_id as id,
                       number as numb
              FROM
              [C:\Documents and Settings\QV_QVD\marks.qvd]
              (qvd);

               

              If still have doubt then share your qvd's data with some dummy Data.

               

              Thanks and Regards,

               

              Vishal Waghole