Connecting two fields to one field

[raynac]

Here is my dilemma.  I have a booking table:

scbooking:

LOAD

    "booking_number" as "SC Booking Number",

    "client_code" as "Booking Client Code",

    description as "SC Booking Description",

    "reservation_number" as "FD Group Master",

SQL SELECT *

FROM scbooking where reservation_number>0;

This table connects to a folio header, and can connect via *either* the booking number or the group master reservation number.

I wrote this but the resultant set is disconnected from the scbooking table above.

sourceID:

  load [SC Booking Number] as source_id,

  "SC Booking Number"

Resident  scbooking;

Concatenate(sourceID)

    load [FD Group Master] as source_id,

    "SC Booking Number"

Resident  scbooking;

sc_gbfol_head:

load

    "folio_number" as "SC Folio Number",

    "source_id"

[raynac]

OK...for some reason I cannot edit or delete the original post.  What I WANTED to say was that it's connected but only via the booking number and is still not picking up the folios connected to the reservation number.  If I connect via both, that gives me a blank set.

Can anyone see where I am going wrong?

[Anonymous]

A bit of guess without data to test against, but how about something like this ?

scbooking:

LOAD

    "booking_number" as "source_id",

    "booking_number" as "SC Booking Number",

    "client_code" as "Booking Client Code",

    description as "SC Booking Description",

    "reservation_number" as "FD Group Master",

SQL SELECT *

FROM scbooking where reservation_number>0;

sc_gbfol_head:

load

    "folio_number" as "SC Folio Number",

    "source_id"

[raynac]

Hi Bill,

Thank you so much for taking the time to respond.

Unfortunately, the reason that won't work is because the source_id can be either the booking_number or the reservation_number.  So joining based on one drops all the folios where the source_id matches the other.  That was the reason for my attempt to build some sort of "connector" table that would gather up all the possibilities for the source_id while still joining them back to the original booking table.

Does that make sense?

[maxgro · Accepted solution]

hope to understand the question (if not, please post some data)

scbooking:

LOAD

    rowno() as id,

    "booking_number" as "SC Booking Number",

    "client_code" as "Booking Client Code",

    description as "SC Booking Description",

    "reservation_number" as "FD Group Master",

SQL SELECT *

FROM scbooking where reservation_number>0;

linktable:

load id, "SC Booking Number" as joinfield resident scbooking;

concatenate (linktable) load id, "FD Group Master" as joinfield resident scbooking;

sc_gbfol_head:

load

    "folio_number" as joinfield,

    "source_id"

    .......

[raynac]

Thanks Massimo!  That worked! 😄