3 Replies Latest reply: Nov 30, 2011 4:30 AM by Liron Baram RSS

    concat two different tables to one new table (different field names) for single projection

    andries Bos

      I want to concatentating two tables : the extra information should create duplicated records...

       

      I have two tables where the second tabel contains ‘extra’ information within a 1:n relation.

       

      The first table contains the vehicle general information

      vehicle

      numberColor
      Alfa12-34-56White
      BMW55-66-77Blue

       


      The second table contains the inspections:

       

       

      Numberinspection typesLast inspectionnext inspectionHeader 5
      12-34-56Yearly inspection01-01-201001-01-2011
      12-34-56Yearly inspection05-01-201105-01-2012
      12-34-564 years inspection06-05-201006-05-2014
      55-66-77Yearly05-05-201105-05-2012

       

      I would like to merge these two tabels into one where the outcome should be :

       

      vehiclenumberUnique inspection typesLastNext
      Alfa12-34-56Yearly inspection05-01-201105-01-2012
      Alfa12-34-564 years inspection06-05-201006-05-2014
      B55-66-77Yearly05-05-201105-05-2012


      It should merge from the original database the unique different inspection types (1:n relation) and project only the last known inspection date info my overview table: this means i could filter (by date) only the inspection and inspection types to still be done within the selected month

       

      as an example, the resulting merged table should be shown in a 'flat' perspective:

      Clipboard01.jpg

      How could I join information into a resulting table whereby the resulting table creates extra (duplicate) records to contain the information.

      In words the soluton could be : iterate through the cars list ; search for the unique types of inspections avaiable for this car, select by mapping the max(first date) from the database...

       

      I'm used to program, but to add all these conditions into a single "load" seems to be quite difficult.

       

      Some ways to solve this by providing pseudo code or step by step info would be appreciated.