Getting wrong values when joining tables?

Question

I have 3 tables (pet_table, owner_table, address_table and full_form_table) I want to look for data from and join them together and put all the data in a new table.

I want to first create a temporary table called temporary_new_table

DECLARE GLOBAL TEMPORARY TABLE SESSION.temporary_new_table  (
    pet_id INT,
    pet_name VARCHAR(50),
    owner_id INT,
    owner_name VARCHAR(50),
    address_id INT,
    address_detail VARCHAR(50),
    owner_account_type VARCHAR(50),
    owner_generation_type VARCHAR(50),
    owner_gender_type VARCHAR(50)
);

Now I want to pull all the data from the three tables and insert them to my temporary table above

INSERT INTO SESSION.temporary_new_table

SELECT
    pet_table.pet_id,
    pet_table.pet_name,
    owner_table.owner_id,
    owner_table.owner_name,
    address_table.address_id,
    address_table.address_detail,
    full_form_table.full_description, -- this needs to change?
    full_form_table.full_description, -- this needs to change?
    full_form_table.full_description -- this needs to change?
FROM pet_table
INNER JOIN owner_table ON
    pet_table.pet_id = owner_table.pet_id
INNER JOIN address_table ON
    owner_table.address_id = address_table.address_id
INNER JOIN full_form_table ON
    owner_table.owner_account_type = full_form_table.full_form_id;

The full_form_table has all the full forms I would need for my application (for example: MIL(full_form_id) - > Millennial(full_form_description), MA - > Male, FE -> Female, SAV -> Savings Account) etc.

My new temporary table should have full_form_description which should come from full_form_table but the tricky part is owner_account_type comes from the owner_table.

With the above join, I am getting the owner_account_type, (for example, Savings) for owner_account_type, owner_generation_type and owner_gender_type.

It is probably because I am only joining the owner_account_type with full_form_id. How do I fix this issue?

Getting wrong values when joining tables?

0 Answers