I beg to please stay with me on this for a minute.
I have two tables, one called Applications and the other called Assets
Assets table is a lookup table newly created to join to Applications table.
Background: There is an existing table called Applications. This table was designed long before I was born (Ok just joking) but this table was built in 2007 but someone pretending to be a developer.
This application table has several column names but just to name a few relevant ones:
Table: Applications Columns: AppID int PK autoincrement, Asset1 decimal (8,2) Asset2 decimal(8,2) Sample Values: AssetID Asset1 Asset2 1 33.01 120.10 2 230.00 189,00
There is a dropdownList of Assets. The dropdownlist is called Assets and have values of Asset1 and Asset1
After several years of using the app that connects to this Appplications table,user contact management and asked that we add two new columns called Asset3 and Asset4.
Unfortunately, Asset3 and Asset4 could not be used as column names because like Asset1 and Asset2, they are values not column names but are used as column names by whoever designed the database previously.
Those values should not have been used as column names.
To fix this problem, I created a new table - a look up table called Assets.
This look up table is now has four values, Asset1, Asset2, Asset3 and Asset4
Table now looks like this:
Assets - Table
AssetID int PK AutoIncrement Asset decimal (8,2)
Sample table data:
AssetID Assets 1 33.01 2 120.10 3 230.00 4 189,00 etc etc etc
Now, to insert records into Applications table, user selects Asset from lookup table and AssetID is then inserted into the Applications table.
Now, when joining the two tables together, we have the following simple query:
Select a.Assets, ap.AssetAmount, ap. FirstName, ap.Lastname, ap.Phone, ap.Email
From applications ap inner join Assets a on ap.AssetID = a.AssetID where ap.AppID = someValue.
When you run the query, if there are matching records, the records get displayed.
However, there are thousands of instances where there are no matching records and as a result, the query returns no records.
The problem with this is that the business rule says that if there are no matching records, it means the record does not exists and an admin is presented with a blank form to enter the record that does not exist.
As you can see from the above scenario, several records are returned as none existent even though they exist.
Is there some trick to recognize the existence of records to avoid duplicating records?
Please understand that this is a truly tricky situation way beyond my pay grade.
I am fairly good with database design and writing queries but not sure how to handle a situation such as this when the record actually exists except that zero record is returned simply because those existing records do not have matching AssetID between Assets table and Applications table.
Your constructive advise is greatly appreciated.
If you can't assist, that's understandable but please understand I am forced into this situation.
I asked for the existing records to be archived so we can start all over but the admin argues that archiving existing records will make it extremely difficult for them to know if a record truly exists or not.
I will be happy to provide further context to make things clearer.
Thank you for your understanding and assistance.