4D v13.4

Joins

Home

 
4D v13.4
4D SQL Reference
Tutorial
Joins

Joins  


 

 

We would now like to find out the city of birth for each actor. The list of actors is in the ACTORS table and the list of cities is in the CITIES table. To execute this query we need to join the two tables: ACTORS and CITIES.

  • The initial query in 4D code would be:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     C_LONGINT($i;$vInd)
     
     $vInd:=0
     ALL RECORDS([ACTORS])
     For($i;1;Records in selection([ACTORS]))
        $vInd:=$vInd+1
        INSERT IN ARRAY(aTitles;$vInd;1)
        aTitles{$vInd}:=[ACTORS]FirstName+" "+[ACTORS]LastName
        RELATE ONE([ACTORS]Birth_City_ID)
        INSERT IN ARRAY(aDirectors;$vInd;1)
        aDirectors{$vInd}:=[CITIES]City_Name
        NEXT RECORD([ACTORS])
     End for
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aSoldTickets;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))
     MULTI SORT ARRAY(aDirectors;>;aTitles;>;aMovieYear;aMedias;aSoldTickets;aNrActors)
  • Using SQL code, the above query becomes:

     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     
     Begin SQL
        SELECT CONCAT(CONCAT(ACTORS.FirstName,' '),ACTORS.LastName), CITIES.City_Name
        FROM ACTORS, CITIES
        WHERE ACTORS.Birth_City_ID=CITIES.City_ID
        ORDER BY 2,1
         INTO :aTitles, :aDirectors;
     End SQL
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aSoldTickets;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))
  • Using generic SQL commands, the above query becomes:

     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     C_TEXT($tQueryTxt)
     
     SQL LOGIN(SQL_INTERNAL;"";"")
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT CONCAT(CONCAT(ACTORS.FirstName,' '),ACTORS.LastName), CITIES.City_Name"
     $tQueryTxt:=$tQueryTxt+" FROM ACTORS, CITIES"
     $tQueryTxt:=$tQueryTxt+" WHERE ACTORS.Birth_City_ID=CITIES.City_ID"
     $tQueryTxt:=$tQueryTxt+" ORDER BY 2,1"
     SQL EXECUTE($tQueryTxt;aTitles;aDirectors)
     SQL LOAD RECORD(SQL All Records)
     SQL LOGOUT
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aSoldTickets;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))
  • Using the QUERY BY SQL command, we are unable to carry out the query above because it is not possible to pass more than one table as the first parameter.
  • Using the SQL EXECUTE IMMEDIATE command, the query above becomes:

     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     C_TEXT($tQueryTxt)
     
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT CONCAT(CONCAT(ACTORS.FirstName,' '),ACTORS.LastName), CITIES.City_Name"
     $tQueryTxt:=$tQueryTxt+" FROM ACTORS, CITIES"
     $tQueryTxt:=$tQueryTxt+" WHERE ACTORS.Birth_City_ID=CITIES.City_ID"
     $tQueryTxt:=$tQueryTxt+" ORDER BY 2,1"
     $tQueryTxt:=$tQueryTxt+" INTO :aTitles, :aDirectors"
     Begin SQL
        EXECUTE IMMEDIATE :$tQueryTxt;
     End SQL
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY INTEGER(aMovieYear;Size of array(aTitles))
     ARRAY TEXT(aMedias;Size of array(aTitles))
     ARRAY LONGINT(aSoldTickets;Size of array(aTitles))
     ARRAY LONGINT(aNrActors;Size of array(aTitles))


    To test all the above examples, launch the "4D SQL Code Samples" database and go to the main window. You can then choose the query mode and press the Joins button.

 
PROPERTIES 

Product: 4D
Theme: Tutorial