wesleycrusher
Technical User
member table contains:
member.member_id (PRIMARY)
member.related_to
member_product table contains:
member_product.member_id (PRIMARY)
member_product.product_type_id (PRIMARY)
member_product.form_received
related_to contains the member_id of the parent company. If there is no parent company, it contains the model_id of itself.
Whenever I receive a signed form, I manually go into the member_product table and change the form_received field to 'true' for the parent company.
My goal is to develop a statement that can be run afterwards to update all the subsidiary companies.
The problem is the double primary key in member_product. A member_id can have several product_type_id so both are needed for uniqueness.
The logic I'm using goes:
1 - search member_product to find where form_received is not null and record member_id and product_type_id.
2 - use member_product.member_id collected as member.related_to and collect the resulting list of subsidiary companies by recording the returned member.member_id
3 - take member.member_id back into member_product table and update form_received to match the parent company's value BUT only for the correct product_type_id in step 1.
I can't figure out a way to ensure it uses both primary keys and doesn't ignore one to screw up the data. I've tried all day and only have a nice INNER JOIN statement that will get me the data I need. I think there's a way to use it to automatically loop update statements but I'm not positive.
Thanks.
member.member_id (PRIMARY)
member.related_to
member_product table contains:
member_product.member_id (PRIMARY)
member_product.product_type_id (PRIMARY)
member_product.form_received
related_to contains the member_id of the parent company. If there is no parent company, it contains the model_id of itself.
Whenever I receive a signed form, I manually go into the member_product table and change the form_received field to 'true' for the parent company.
My goal is to develop a statement that can be run afterwards to update all the subsidiary companies.
The problem is the double primary key in member_product. A member_id can have several product_type_id so both are needed for uniqueness.
The logic I'm using goes:
1 - search member_product to find where form_received is not null and record member_id and product_type_id.
2 - use member_product.member_id collected as member.related_to and collect the resulting list of subsidiary companies by recording the returned member.member_id
3 - take member.member_id back into member_product table and update form_received to match the parent company's value BUT only for the correct product_type_id in step 1.
I can't figure out a way to ensure it uses both primary keys and doesn't ignore one to screw up the data. I've tried all day and only have a nice INNER JOIN statement that will get me the data I need. I think there's a way to use it to automatically loop update statements but I'm not positive.
Thanks.