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!