I have 3 tables:
Memory_map: - Id - Memory_map_version - Product_id_hex - Parameter --------------------> want to add this to Histogram table Histogram_read: - Id - Memory_map_version -------> these 2 keys are referencing to the Memory_map table - Product_id_hex ---------> these 2 keys are referencing to the Memory_map table Histogram: - Id - Histogram_read_id
The relationship is:
Histogram_read (one) --> histogram(many) Memory_map (one) --> histogram_read(one)
So Now I want to create a NEW Histogram table with the [Parameter] field from Memory_map table added to the original Histogram table.
Because Memory_map (one) --> histogram_read(one) is a ONE-TO-ONE relationship, so I will need to join these two tables first before I join the Histogram_read table with Memory_map table to add the [Parameter] field to Histogram table.
So, should I do a inner join first between Histogram_read and Histogram based on Histogram_read_id? (since Histogram_read_id is a key referencing the Histogram_read table records), but since it's a ONE-TO-MANY relationship, is it ok to do the INNER JOIN?
And Because I only want all records and columns from Histogram table - just with [Parameter] field added, so if I join the Histogram_read table with Histogram table before I join the Histogram_read table with the Memory_map table, how do I make sure the final table of Histogram wasn't messed up by columns in the Histogram_read table?
I have this following query but it doesn't work:
create view histogram_new as select M.parameter, Nest.* from memory_map M RIGHT JOIN (select * from histogram_read R INNER JOIN histogram H ON H.histogram_read_id = R.id) Nest ON M.memory_map_version = Nest.memory_map_version AND M.product_id_hex = Nest.product_id_hex;
I'm confused about the 3 tables join with one=to-many and one-to-one relationship, I am not sure what the logic should be. anyone can helps? THANKS!