How to handle two tables with common ID but no matching records?

simflex1 · April 4, 2023, 3:15am

Greetings,

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.

harishgg1 · April 4, 2023, 10:04am

hi

please check my design

drop table if exists #Applications
create table #Applications(AppID int ,AssetID int, Asset1 decimal (8,2),Asset2 decimal(8,2))
insert into #Applications select 100, 1,                 33.01  ,       120.10
insert into #Applications select 200, 2,                  230.00 ,       189.00
select * from #Applications

drop table if exists #Assets
create table #Assets (AssetID int , AssetNo Int ,  Asset   decimal (8,2) )
insert into #Assets select  1, 1  ,                           33.01  
insert into #Assets select  1, 2  ,                          120.10
insert into #Assets select  2, 1  ,                          230.00
insert into #Assets select  2, 2  ,                          189.00
select * from #Assets


SELECT * FROM #Applications a JOIN #Assets b on a.AssetID = b.AssetID

simflex1 · April 4, 2023, 12:16pm

Sorry, it was more of a mistake on my part than a design flaw. I mistakenly left out AssetID on Applications table.

That's why I asked for constructive feedback.

The larger issue remains which is that both Applications table and Assets table have AssetID but not all the AssetIDs on Applications table have values since most of the records existed before the tables were redesigned.

How do you account for situations where there is a common related ID between two tables but values for most of the IDs do not exist in one but does in another?

By the way not sure why you have AssetNo on Assets table. It is not being used for anything.

My issue is NOT how to join tables, rather how to join them to account for common ID like AssetID where AssetID in Assets table has values that do not exist in existing records in Applications table.

Your query does not address that.

I don't know if LEFT JOIN will work in such a situation.

Thanks for your efforts though.

harishgg1 · April 4, 2023, 1:21pm

hi

can you please share a data sample of what you are talking about ?

it becomes very easy to figure out a solution

please provide sample data which shows your situation
in the form of create table , insert into table statements

thank you

ScottPletcher · April 4, 2023, 1:54pm

It sounds like you may need a FULL OUTER JOIN rather than just a LEFT JOIN. It's difficult to tell, I'm not sure I 100% follow exactly what your core issue is.

simflex1 · April 4, 2023, 4:50pm

Thank you very much guys, your assistance is greatly appreciated.

Please see the sample INSERT statements for the two tables.

If you take a look at Assets table, there are three values for AssetID, 10,12,13.

Then if you take a took at Applications table, there seven records but only three of those have values for AssetID that match both tables.

The records in Applications table are in the thousands but I am keeping it simple with those few records.

We are tasked with figuring out how to display both the records where AssetID from Assets table matches AssetID from Applications table as well as displaying the records where there is no match between the two tables.

The idea is to avoid inserting records that already exists.

Here is the INSERT script for the two tables:

INSERT INTO Assets (AssetID,Asset)
VALUES (10,'Auto'),
       (12,'Boat'),
       (13,'Aircraft');


INSERT INTO Applications (AppID,AssetID,Asset1,Asset2,Email,Phone,FirstName,LastName,DateEntered)
 VALUES
        (1,NULL,'230.00','2300.00','Joeblow@yahoo.com','111-222-3333','Joe','Blow','01/20/2015'),
		(2,NULL,'430.00','2400.00','Janeblow@yahoo.com','111-222-3334','Jane','Blow','02/20/2016'),
        (3,NULL,'360.00','2500.00','RobbieKeane@yahoo.com','111-222-3335','Robbie','Keane','03/20/2017'),
        (4,NULL,'340.00','2600.00','JennyCraig@yahoo.com','111-222-3336','Jenny','Craig','04/20/2018'),
        (5,10,'370.00','2700.00','Glen@yahoo.com','Glen','111-222-3337','Joykins','01/20/2023'),
        (6,12,'380.00','2800.00','MaryMetalin@yahoo.com','111-222-3338','Mary','Metalin','03/20/2023'),
        (7,13,'390.00','2900.00','JerryRice@yahoo.com','111-222-3339','Jerry','Rice','04/04/2023');

CREATE TABLE Assets (
AssetID int IDENTITY(1,1) NOT NULL,
Asset nvarchar(50) NULL
)

CREATE TABLE Applications (
	AppID int IDENTITY(1,1) NOT NULL,
	AssetID int NULL,
	Asset1 decimal (8,2) NULL,
	Asset2 decimal (8,2) NULL,
	Email nvarchar(50) NULL,
	Phone nvarchar(12) NULL,
	FirstName nvarchar(50) NULL,
	LastName nvarchar(50) NULL,
	DateEntered DateTime
)

simflex1 · April 4, 2023, 5:48pm

UPDATE: FULL OUTER JOIN worked!

Many thanks Scott and good to hear from you.

I hope everyone takes note of Scott's approach.

I have been coming here now for many years. At no time have I directed been insulted by him or Jeff Moden and perhaps a host of others, neither have I read of them insulting other posters.

They read the poster's thread. If they feel they can help, they help if they understand the issue or ask for clarifications.

I do sincerely appreciate their approach to helping helpers like me solve problems.