This is a rookie type question, but here it goes.

I have the following type tables

PERSONS
=======
PERSONID   GUID
FNAME      VARCHAR(30)
MNAME      VARCHAR(30)
LNAME      VARCHAR(30)
GENDER     CHAR(1)

ADDRESS_TO_PERSONS
==================
PERSONID    GUID
ADDRESSID   GUID
ADDRESSTYPE GUID


ADDRESS
=======
ADDRESSID  GUID
STREET     VARCHAR(40)
CITY       VARCHAR(20)
STATE      VARCHAR(2)
ZIP        VARCHAR(10)

ADDRESSTYPE
===========
ADDRESSTYPE GUID
Description  VARCHAR(10)

The general DB schema purpose is that each person can have many
addresses. With that address, it's identified as "Billing", "Shipping",
etc, so that the user can choose from a list of address types or add
custom ones.  Each address can only have one of an address type, so 1
Billing, 1 shipping, etc.

What would an appropriate select look like to generate a Mailing Label
(FName, LName, Street, City, State, Zip) for:

1) Each person (Generate a label for each address for each person, even
if its the same person)

2) Each person based on a specific address type.  (Generate labels for
only for those with shipping addresses)

3)Each person based on a specific address type, and if type not
available, the first one that is.  I.E. I want all the shipping
addresses, but if a person doesn't have a shipping address, I'll take
the billing or whatever's 1st.



Thanks for any assistance!!

Lance

Lance,

<< What would an appropriate select look like to generate a Mailing Label
(FName, LName, Street, City, State, Zip) for:

1) Each person (Generate a label for each address for each person, even if
its the same person) >>

SELECT FName, LName, Street, City, State, Zip
FROM Persons P INNER JOIN Address_To_Persons AP ON P.PersonID=AP.PersonID
INNER JOIN Address A ON AP.AddressID=A.AddressID
ORDER BY LName, FName  /* Optional */

<< 2) Each person based on a specific address type.  (Generate labels for
only for those with shipping addresses) >>

SELECT FName, LName, Street, City, State, Zip
FROM Persons P INNER JOIN Address_To_Persons AP ON P.PersonID=AP.PersonID
INNER JOIN Address A ON AP.AddressID=A.AddressID
WHERE AP.AddressType='S'
ORDER BY LName, FName  /* Optional */

<< 3)Each person based on a specific address type, and if type not
available, the first one that is.  I.E. I want all the shipping addresses,
but if a person doesn't have a shipping address, I'll take the billing or
whatever's 1st. >>

SELECT PersonID, AddressID
INTO "Memory\Temp"
FROM Address_To_Persons AP
WHERE AP.AddressType='S'
UNION
SELECT DISTINCT PersonID, AddressID
FROM Address_To_Persons AP
WHERE AP.AddressType <> 'S';

SELECT FName, LName, Street, City, State, Zip
FROM "Memory\Temp" T
INNER JOIN Address_To_Persons AP ON T.PersonID=AP.PersonID AND
T.AddressID=AP.AddressID
INNER JOIN Persons P ON AP.PersonID=P.PersonID
INNER JOIN Address A ON AP.AddressID=A.AddressID
ORDER BY LName, FName  /* Optional */

There is a problem with the first query however, since there is really no
way to specify "first" in any meaningful way.  IOW, the "first" non-S
address type will simply be random and it won't be based upon any meaningful
order.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thank you so much....    That actually also helps me grasp where Memory
Tables are useful for.

After a Select into and select from memory table.  Do you need to clear
the memory table out?   (I'm doing everything C/S)

Lance



Tim Young [Elevate Software] wrote:
> Lance,
>
> << What would an appropriate select look like to generate a Mailing Label
> (FName, LName, Street, City, State, Zip) for:
>
>  1) Each person (Generate a label for each address for each person, even if
> its the same person) >>
>
> SELECT FName, LName, Street, City, State, Zip
> FROM Persons P INNER JOIN Address_To_Persons AP ON P.PersonID=AP.PersonID
> INNER JOIN Address A ON AP.AddressID=A.AddressID
> ORDER BY LName, FName  /* Optional */
>
> << 2) Each person based on a specific address type.  (Generate labels for
> only for those with shipping addresses) >>
>
> SELECT FName, LName, Street, City, State, Zip
> FROM Persons P INNER JOIN Address_To_Persons AP ON P.PersonID=AP.PersonID
> INNER JOIN Address A ON AP.AddressID=A.AddressID
> WHERE AP.AddressType='S'
> ORDER BY LName, FName  /* Optional */
>
> << 3)Each person based on a specific address type, and if type not
> available, the first one that is.  I.E. I want all the shipping addresses,
> but if a person doesn't have a shipping address, I'll take the billing or
> whatever's 1st. >>
>
> SELECT PersonID, AddressID
> INTO "Memory\Temp"
> FROM Address_To_Persons AP
> WHERE AP.AddressType='S'
> UNION
> SELECT DISTINCT PersonID, AddressID
> FROM Address_To_Persons AP
> WHERE AP.AddressType <> 'S';
>
> SELECT FName, LName, Street, City, State, Zip
> FROM "Memory\Temp" T
> INNER JOIN Address_To_Persons AP ON T.PersonID=AP.PersonID AND
> T.AddressID=AP.AddressID
> INNER JOIN Persons P ON AP.PersonID=P.PersonID
> INNER JOIN Address A ON AP.AddressID=A.AddressID
> ORDER BY LName, FName  /* Optional */
>
> There is a problem with the first query however, since there is really no
> way to specify "first" in any meaningful way.  IOW, the "first" non-S
> address type will simply be random and it won't be based upon any meaningful
> order.
>

Lance

SELECT INTO creates/overwrites the memory table
INSERT INTO adds to the memory table
SELECT FROM just like a disk based table

If I'm reading your question correctly you will need to DROP the table after you've finished using it otherwise it will persist in local mode until the app is closed but I'm not sure in c/s when it gets removed.



Roy Lambert

Roy,

<< If I'm reading your question correctly you will need to DROP the table
after you've finished using it otherwise it will persist in local mode until
the app is closed but I'm not sure in c/s when it gets removed. >>

It never is removed with C/S if you don't do so specifically.  This is one
reason why we have this method:

http://www.elevatesoft.com/dbisam4d5_tdbisamsession_removeallremotememorytables.htm

--
Tim Young
Elevate Software
www.elevatesoft.com