April 12, 2016 at 9:40 am
So I have a table with a list of many values but for this conversation the only column that matters is Field name. The table is a list of all fields in a given table.
Then I have a table that maintains a list of Foreign keys for a that table
So example.
Table 1: CustomerID, FirstName, LastName, SatatusID, CustomerTypeID
IN the above example StatusID and CustomerTypeID Both have a Foreign Key relationship to the Status Table and the CustomerType table.
This first table clearly has 5 columns but only two of those columns are in the second table because this table correlates the FK to an aliased name I am using against another table.
So in this second table I will have two records for the above table.
One record that has a field FKColumnName which would have StatusID as one record and CustomerTypeID for the second record.
There is another column "CorrelationField". So here I have code that already populates this. IT is always DestinationID as a value but their will be an alias in front of it that a join will use by way of dynamic SQL later.
So in this example the two records in the second table would look like this (for the columns we care about.
FKColumnName CorrelationField
StatusID fk1.DestinationID
CustomerTypeID fk2.DestinationID
I need to have in a SELECT the CorrelationField value if there is one, and if not the FKColumn name.
Here is the SELECT I am using, and not getting what I need
SELECT DISTINCT coalesce(fl.CorrelationField, stf.FieldName), *
FROM SourceTableFields stf
inner JOIN FKFieldList as fl on fl.FKTableName = stf.TableName AND fl.fkColumnName = stf.TableName
WHERE stf.TableName = 'AccountingPeriodStaffingSupplier'
-- In my example I am limiting to a single table being returned.
If a table has say 10 columns, then the first table above would have 10 records. Each with the same table name in a field and then each record with a distinct column name for that table.
So if a table only has say one foreign key then the second table will just have one record. The select needs to return all 10 records from the first table but for the record where the column name is in both the first and second tables, I need to grab the column name as the CorrelationField value (ie: fk1.DestinationID)
Sorry if this is a ramble, I sure hope it is clear.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 12, 2016 at 9:58 am
Are you able to provide sample DDL, data with DML and desired results, as described in the link in my signature? It would make things clearer.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 12, 2016 at 10:05 am
I hope this addresses what you are looking for. I uploaded the tables. with data for a give table.
Now if you query both those tables;
SELECT DISTINCT *
FROM SourceTableFields stf
WHERE TableName = 'AccountingPeriodStaffingSupplier'
SELECT *
FROM FKFieldList
WHERE FKTableName = 'AccountingPeriodStaffingSupplier'
The top table above has 13 records. Which means the table AccountingPeriodStaffingSupplier'
has 13 fields. This table has two foreign keys. (AccountingPeriodGUID, StaffingSupplierGUID)
So if I did a query I would want to return the same results as the first query above except for those two columns; I want to instead have the values from the CorrelationField column
So the result; If I returned only the FieldName column from the SourceTableFields table would look like this:
fk1.DestinationID
AccountingPeriodStaffingSupplierGUID
BiWeeklyCycle
DateClosed
DateOpen
MonthID
QuarterID
fk2.DestinationID
Status
UserGUID
WeekID
WeekOfMonth
YearID
If you query the SourceTableFields table all on its own (no join or anything) you get:
AccountingPeriodGUID
AccountingPeriodStaffingSupplierGUID
BiWeeklyCycle
DateClosed
DateOpen
MonthID
QuarterID
StaffingSupplierGUID
Status
UserGUID
WeekID
WeekOfMonth
YearID
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 12, 2016 at 11:13 am
I am such an idiot. I was beating my head against the wall. It is not like I have not done this before.
Good grief.
I had 'stf.TableName'
Instead of 'stf.FieldName'
Ok I need a break lol
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply