Many to many question

[arochford]

Hi I am trying Alpha platinum v9. Any help would be appreciated.

I have two tables

TABLE 1 Country_Products
ID
CountryID
ProductID

1 BAH 207
2 BAH 208
3 TOB 207

TABLE 2PERIODS
ID
FirstDate

1 01/01/2008
2 02/01/2008




I would like to combine all possible combinations of the two to give a new table

New table
ID
PeriodID
FirstDate
CountryProductID
CountryID
ProductID

1 1 BAH 207 1 1/1/2008
2 2 BAH 208 1 1/1/2008
3 3 TOB 207 1 1/1/2008
4 1 BAH 207 2 2/1/2008
5 2 BAH 208 2 2/1/2008
6 3 TOB 207 2 2/1/2008

But when I use the operations I only get the first set of records
1 1 BAH 207 1 1/1/2008
2 2 BAH 208 1 1/1/2008
3 3 TOB 207 1 1/1/2008

I am seeing this as a many to many join. However even though the browse can make it happen by having a link like "1" the operations still only return a subset. I can get a group at a time by filtering the period table a record at a time, but I am hoping I do not have to step through every record in my period table. If I do have to step through every record what is the best code to do this? Any ideas?

[Keith Hubert]

Re: Many to many question

Hi Andrea,

Welcome to the would of Alpha.

The basics for this is a nested loop. I will try and work on some code for this and get back to you.

Is this a one off situation?

[Stan Mathews]

Re: Many to many question

Create a new table with your fields as indicated. Run this script.

tbl_dest = table.open("[COLOR="Red"]example[/COLOR]",FILE_RW_EXCLUSIVE) ' use your new table name here

tbl_src1 = table.open("country_products",FILE_RW_EXCLUSIVE)
tbl_src1.fetch_first()
tbl_src2 = table.open("periods",FILE_RW_EXCLUSIVE)
tbl_src2.fetch_first()
i = 0
While .not. tbl_src1.fetch_eof()
	vcid = tbl_src1.Id
	vctry = tbl_src1.Countryid
	vprd = tbl_src1.Productid
		while .not. tbl_src2.fetch_eof()
			tbl_dest.enter_begin()
			tbl_dest.Id = alltrim(str(increment_value(i)))
			tbl_dest.Periodid = tbl_src2.Id
			tbl_dest.Firstdate = tbl_src2.Firstdate
			tbl_dest.Countryproductid = vcid
			tbl_dest.Countryid = vctry
			tbl_dest.Productid = vprd
			tbl_dest.enter_end(.t.)
			i = i + 1
			tbl_src2.fetch_next()
		end while
		tbl_src2.fetch_first()
	tbl_src1.fetch_next()
end while
tbl_src1.close()
tbl_src2.close()
tbl_dest.close()
end

I can't tell if I have the mappings like you want but I get the result shown in the attached screenshot.

[Mike Wilson]

Re: Many to many question

Andrea,
Welcome to the Alpha message board.

The code that Stan gave you should work. Be aware that line 14 which delivers the new table ID "number" is delivering a character value:

tbl_dest.Id = alltrim(str(increment_value(i)))

The others ID's you have might be numeric. Keep in mind that unless a field value will be used for mathmatical functions, character type is advisable.

[Keith Hubert]

Re: Many to many question

Thanks guys for following up on this.

Looks like Stan the Man has done it again. Nice job Stan.

[Stan Mathews]

Re: Many to many question

This is equivalent but I thought the other might be clearer in flow.

tbl_dest = table.open("example",FILE_RW_EXCLUSIVE)

tbl_src1 = table.open("country_products",FILE_RW_EXCLUSIVE)
tbl_src1.fetch_first()
tbl_src2 = table.open("periods",FILE_RW_EXCLUSIVE)
tbl_src2.fetch_first()
i = 1
While .not. tbl_src1.fetch_eof()
		while .not. tbl_src2.fetch_eof()
			tbl_dest.enter_begin()
			tbl_dest.Id = alltrim(str(i))
			tbl_dest.Periodid = tbl_src2.Id
			tbl_dest.Firstdate = tbl_src2.Firstdate
			tbl_dest.Countryproductid = tbl_src1.Id
			tbl_dest.Countryid = tbl_src1.Countryid
			tbl_dest.Productid = tbl_src1.Productid
			tbl_dest.enter_end(.t.)
			i = i + 1
			tbl_src2.fetch_next()
		end while
		tbl_src2.fetch_first()
	tbl_src1.fetch_next()
end while
tbl_src1.close()
tbl_src2.close()
tbl_dest.close()
end

[G Gabriel]

Re: Many to many question

What do the ID fields in both tables have in common? if any?
Why do you have them in the first place?
And what exactly do you intend to do with the final table?

[arochford]

Re: Many to many question

Thanks so much guys this looks like it. I will try it. There are no common fields it is used to create data entry records for a new product for each date in a table.