2 Replies Latest reply: Mar 23, 2017 9:06 AM by Rayna Curtis RSS

    Script Join Problem

    Rayna Curtis

      Hi All,


      I am hoping you can come to my rescue with respect to an issue I am having.


      My database has buildings; each building is assigned to a property.  SO my gbbuilding table has (for example):


      Building     Property

      1               A

      2               B

      3               B

      4               C

      5               D

      6               D


      And so on.


      I have a booking table, which includes a building code.  There is a table I need to join to that has several fields in common, but instead of the building code, it has the property code.  How the heck do I join these from the SQL statement standpoint?


      SQL SELECT *

      FROM cmsvbook b LEFT OUTER JOIN cmprovable p

      ON b.service_type=p.service_type

          AND b.service_detail=p.service_detail

          AND b.provider_code=p.provider_code

         and b.building_code=p.property_code; 

      This was how it was written long before I got the view, and might have worked in theory once upon a time.  It's now wrong because, for many of my clients these days, the building code and property code can be, and  usually are, different.  I need to "go through" gbbuilding and swap out the b.building_code for the correct property code that I need.


      Does anyone know how to make this work?