January 6, 2003 at 1:51 pm
I am trying to query two tables the first table contains a field that holds one of several different possible field names found in the second table. How can I write a query that will return all rows that match an expression from the first table with the value that is found in the corresponding field of the second table. Any ideas to pint me in the right direction ???
January 6, 2003 at 2:07 pm
Can you post some DDL? Not sure hwat you mean. Also perhaps a sample data set.
Steve Jones
January 6, 2003 at 2:44 pm
this might be quicker than the DDL
A query from the first table might return this:
---------------------------------------
SELECT FDT_NAme, F_Description,... FROM tblFDT WHERE F_UID = '12345'
FDT_NAme F_Description FO_XPosition FO_YPosition FDT_UALookup
UA_PHONE Phone Number 5.615 7.947 UA_PHONE
UA_Locator Contact Name 5.615 7.296 UA_Locator
UA_OTHER Email 5.615 8.15 UA_OTHER
UA_Street Local Office Address 5.615 7.51 UA_Street
UA_CSZ City State Zip 5.615 7.741 UA_CSZ
A query from the second table might return this:
---------------------------------------
SELECT * FROM tblUserAddress WHERE UA_UID = '2234'
UA_UID U_UID UA_Locator UA_Street UA_CSZ UA_Phone UA_Other
2234 460 Ledger Plus Franchisee 123 Main St Ste ABC Anywhere, AL 12345 (321) 123-3456 email@email.com
If you notice the first result returns a field (FDT_Name) which contains the name of a field from table two bsuch as (UA_Locator, UA_Street, UA_CSZ, UA_Phone, UA_Other)
I would like to know how to combine the results of these two queries to get something like this:
---------------------------------------
'Value'F_Description FO_XPosition FO_YPosition FDT_UALookup
(321) 123-3456 Phone Number 5.615 7.947 UA_PHONE
Ledger Plus Franchisee Contact Name 5.615 7.296 UA_Locator
email@email.com Email 5.615 8.15 UA_OTHER
123 Main St Ste Local Office Address 5.615 7.51 UA_Street
ABC Anywhere, AL 12345City State Zip 5.615 7.741 UA_CSZ
January 6, 2003 at 4:53 pm
I think your explanation is clear enough but agree with previous post. If you can post the script to create and populate 3 to five records in both tables it will save time. Sometimes, rather than taking a lunch break I'll try and solve some of these questions for my own training.
January 6, 2003 at 5:22 pm
Hope this is of some help:
SELECT FDT_NAme, F_Description,
(Select Case t.FDT_NAme When 'UA_PHONE' Then a.UA_PHONE
When 'UA_Locator' Then a.UA_Locator
Else '' End
FROM tblUserAddress as a WHERE UA_UID = '2234') as OverLoadedCol
FROM tblFDT as t WHERE F_UID = '12345'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply