Returning records with a foreign key in preference to one without

[bnbertha]

I have two tables; Parameters and Concessions. The parameters table has a foreign key field to concessions that is populated only if a concession exists. If a concession is put in place, a duplicate record in Parameters is created with the duplicate having a foreign key entry to concessions. When the concession expires, the duplicate is removed. For example:

Param_key Param_value test_fk Param_Version concession_fk
-----------------------------------------------------------
0 6.0 1 1
1 2.5 2 1
2 3.25 2 2
3 7.7 3 1
4 9.31 4 1
5 9.32 4 2
6 9.33 4 2 17
7 8.0 5 1
8 8.022 5 2
9 8.01 5 3

I have a derived table query that returns the data in test_fk ascending order, taking the highest value of Param_Version of each test_fk;

SELECT Param_value, test_fk, Param_Version FROM Parameters INNER JOIN (SELECT test_fk, MAX(Param_Version) AS latest_ver FROM Parameters GROUP BY test_fk) MaxParam ON Parameters.test_fk = MaxParam.test_fk AND Parameters.Param_version = MaxParam.latest_ver;

What I need to try and do is, if a concession_fk entry exists choose that record over the same record without a concession_fk entry like this:

Param_key Param_value test_fk Param_Version concession_fk
-----------------------------------------------------------
0 6.0 1 1
2 3.25 2 2
3 7.7 3 1
6 9.33 4 2 17
9 8.01 5 3

All my attempts seem to either return both test 4 version 2 records or none at all. Any ideas will be much appreciated.

Thanks

 

[Thargy]

bertha,

I know this isn't what you asked, but you seem to be making your own problem here.

If a concession exists, add the fk value to the necessary column, and use it as a flag to indicate the presence of the concession programatically.

When the concession ceases, remove the fk. Deliberately adding duplicate records is usually very bad form, and as you can see for yourself, leads to difficulties.

I genuinely believe that "not doing it this way" is the best option here. Do you have that flexibility?

Regards

T

Grinding away at things Oracular

 

[bnbertha]

No I don't really have that flexibility. We need to keep track of what values were used when. This is only a small corner of our database and the possible variation in changes makes holding the old data in a separate table very complex, and something I'd rather avoid if I can.

 

[Otto]

Take a try with this:

select T.* from @TMP T
 join (select TEST_FK, max(PARAM_VERSION) PARAM_VERSION, max(CONCESSION_FK) CONCESSION_FK from @TMP group by TEST_FK) T1
  on T1.TEST_FK=T.TEST_FK and T1.PARAM_VERSION=T.PARAM_VERSION and (T1.CONCESSION_FK=T.CONCESSION_FK or
   T1.CONCESSION_FK is null)
 order by PARAM_KEY

It gives the result as you want on your test data.

 

[bnbertha]

No it doesn't work. My test data is obviously not close enough to the actual data. I'll expand it to the complete table structure for the tables concerned. Parameters is actually called Process_Params and the columns (all data type of int) are as follows:

Params_key Test_Seq_id Param_seq_num int, position_id Command_id Stim_id params_version concession_id

Our naming convention is *key for a primary key *id for a foreign key. The Stim table holds the equivalent of the Param_Value in the above test data. test_seq_id links to a Process_Tests table. Many records in this table to one record in Process_Test. Param_seq_num is the order in which the records in this table are ordered for a give Test_seq_id. The concession_id is a foreign key to the concessions table which looks like this

Concession_key (int), Expire_date (datetime), doc_reference char

The way the data works is; any permanent change to a Process_Params table record causes a duplicate record to be created, with the change incorporated and the params_version number incremented by one. The original record is left untouched to give us params config history. Temporary changes are as described in my original post. The whole point to what I am trying to do is to create a view that picks up the current version of the params. This includes including the concession_id linked record if the concession has not expired, otherwise the non-linked record, and at the latest version according to the params_version column. Maybe I should have explained all this to start with rather than trying to simplify the question.

Bertha

 

[Otto]

Huh, it is a bit difficult to understand (for me )... And without sample records... But try this:

select P.*, T.*, S.* from @PARAM P
 join (select Test_Seq_Id, max(Params_Version) Params_Version, max(Concession_id) Concession_id from @PARAM P
  where Concession_Id is null or (select Expire_Date from @CONC where Concession_key=Concession_Id)>getdate()
  group by Test_Seq_Id) T1
  on T1.Test_Seq_Id=P.Test_Seq_Id and T1.Params_Version=P.Params_Version and ((T1.Concession_id=P.Concession_id and
   (select Expire_Date from @CONC where Concession_key=T1.Concession_Id)>getdate()) or (T1.Concession_id is null and P.Concession_id is null))
 join @TESTS T on T.Test_Seq_key=P.Test_Seq_id
 join @STIM S on S.Stim_key=P.Stim_id
 order by P.PARAM_KEY

@PARAM=Process_Params table
@CONC=Concessions table
@TESTS=Process_Tests table
@STIM=Stim table

 

[bnbertha]

No it didn't work but you've given me an idea, something for me to work on that should work, so thanks