Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Select question |
Mon, Feb 27 2006 2:07 PM | Permanent Link |
"Lance R." | 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 |
Tue, Feb 28 2006 8:39 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Feb 28 2006 7:49 PM | Permanent Link |
"Lance R." | 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. > |
Wed, Mar 1 2006 1:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Mar 1 2006 10:35 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |