How to join on unknown number of records?

[SBpsc]

Hello:

I am pretty new to MySQL, so I am hoping someone can help me with this. I have the following data in my database.

ID | Name | Relation/Value
-----------------------------------------
26 | PPID | DMDR_14
26 | PPID | BMDR_20
14 | DMDR | DMDR_16
16 | DMDR | BMDR_33
16 | DMDR | BMDR_52
33 | BMDR | Here is the result
20 | BMDR | BMDR_10


In other words, PPID 26 points to DMDR 14 and BMDR 20. DMDR 14 points to DMDR 16 which in turn points to BMDR 33 and BMDR 52. BMDR 33 does not point to anything. Its the bottom of the chain.

I can keep joining the PPIDs to the DMDRs and the BMDRs to get to the bottom of the chain. My problem is that I don't know how many DMDRs or BMDRs the PPID goes through so I can't just keep adding another join. Is there a simple way to solve this using a loop or something perhaps?

(A sample of what I have written is attached but as I mentioned its not very efficient since I won't know how many joins I need)

SELECT
	tb1.portal_id,
	tb1.portal_name,
	tb1.instantiator_process_id,
	tb2.ProgressAndPerformanceIndicatorDefinitionName AS PPIDR,
	tb4.control_number,
	tb4.DerivedMeasureName AS DMDR,
	tb6.control_number,
	tb6.BaseMeasureName AS BMDR
FROM
	portals AS tb1

	INNER JOIN

	(SELECT
		tb.portal_id,
		tb.member_number,
		tb.control_number,
		tb.date_time_of_entry,
		tb.field_value AS ProgressAndPerformanceIndicatorDefinitionName
	FROM
		ped AS tb
	WHERE
		tb.work_product_code = 'ppidr'
		AND
		tb.field_name = 'ProgressAndPerformanceIndicatorDefinitionName'
		AND
		(tb.date_time_marked_for_deletion IS NOT NULL OR tb.date_time_marked_for_deletion = '00-00-00 00:00:00')
		AND
		tb.date_time_of_entry = (
			SELECT
				MAX(date_time_of_entry)
			FROM
				ped
			WHERE
				work_product_code = 'ppidr'
				AND
				field_name = 'ProgressAndPerformanceIndicatorDefinitionName'
				AND
				portal_id = tb.portal_id
				AND
				member_number = tb.member_number
				AND
				control_number = tb.control_number
			)
	) AS tb2 ON tb1.portal_id = tb2.portal_id

	LEFT JOIN

	(SELECT
		tb.portal_id,
		tb.control_number,
		tb.date_time_of_entry,
		SUBSTRING_INDEX(tb.field_value,'|',1) AS FKey_ipid,
		SUBSTRING_INDEX(SUBSTRING_INDEX(tb.field_value,'|',3),'|',-1) AS FKey_member_number,
		SUBSTRING_INDEX(tb.field_value,'|',-1) AS FKeyDerivedMeasureName
	FROM
		ped AS tb
	WHERE
		tb.work_product_code = 'ppidr'
		AND
		tb.field_name = 'FKeyDerivedMeasureName'
		AND
		tb.date_time_of_entry = (
			...
			)
	) AS tb3 ON tb3.control_number = tb2.control_number

	LEFT JOIN

	(SELECT
		tb.portal_id,
		tb.control_number,
		tb.date_time_of_entry,
		tb.field_value AS DerivedMeasureName
	FROM
		ped AS tb
	WHERE
		tb.work_product_code = 'dmdr'
		AND
		tb.field_name = 'DerivedMeasureName'
		AND
		tb.date_time_of_entry = (
			...
			)
	) AS tb4 ON tb3.FKeyDerivedMeasureName = tb4.control_number

	LEFT JOIN

	(SELECT
		tb.portal_id,
		tb.member_number,
		tb.control_number,
		tb.date_time_of_entry,
		SUBSTRING_INDEX(tb.field_value,'|',1) AS FKey_ipid,
		SUBSTRING_INDEX(SUBSTRING_INDEX(tb.field_value,'|',3),'|',-1) AS FKey_member_number,
		SUBSTRING_INDEX(tb.field_value,'|',-1) AS FKeyBaseMeasureName
	FROM
		ped AS tb
	WHERE
		tb.work_product_code = 'ppidr'
		AND
		tb.field_name = 'FKeyBaseMeasureName'
		AND
		tb.date_time_of_entry = (
			...
			)
	) AS tb5 ON tb5.control_number = tb2.control_number

	LEFT JOIN

	(SELECT
		tb.portal_id,
		tb.member_number,
		tb.control_number,
		tb.date_time_of_entry,
		tb.field_value AS BaseMeasureName
	FROM
		ped AS tb
	WHERE
		tb.work_product_code = 'bmdr'
		AND
		tb.field_name = 'BaseMeasureName'
		AND
		tb.date_time_of_entry = (
			...
			)
	) AS tb6 ON tb5.FKeyBaseMeasureName = tb6.control_number

	LEFT JOIN

	(SELECT
		tb.portal_id,
		tb.member_number,
		tb.control_number,
		tb.date_time_of_entry,
		SUBSTRING_INDEX(tb.field_value,'|',1) AS FKey_ipid,
		SUBSTRING_INDEX(SUBSTRING_INDEX(tb.field_value,'|',3),'|',-1) AS FKey_member_number,
		SUBSTRING_INDEX(tb.field_value,'|',-1) AS FKeyBaseMeasureName
	FROM
		ped AS tb
	WHERE
		tb.work_product_code = 'dmdr'
		AND
		tb.field_name = 'FKeyBaseMeasureName'
		AND
		tb.date_time_of_entry = (
			...
		)
	) AS tb7 ON tb4.control_number = tb7.control_number

	LEFT JOIN

	(SELECT
		tb.portal_id,
		tb.member_number,
		tb.control_number,
		tb.date_time_of_entry,
		SUBSTRING_INDEX(tb.field_value,'|',1) AS FKey_ipid,
		SUBSTRING_INDEX(SUBSTRING_INDEX(tb.field_value,'|',3),'|',-1) AS FKey_member_number,
		SUBSTRING_INDEX(tb.field_value,'|',-1) AS BaseMeasureName
	FROM
		ped AS tb
	WHERE
		tb.work_product_code = 'bmdr'
		AND
		tb.field_name = 'BaseMeasureName'
		AND
		tb.date_time_of_entry = (
			...
			)
	) AS tb7 ON tb4.control_number = tb7.control_number

...

 

[TonyGroves]

No, you can't follow the chain in a single query unless you can identify a maximum number of iterations, in which case a sequence of joins like what you have already would be possible. But even then, you are going to have an enormous query returning a non-relational result set which could prove quite difficult to process.

There are two approaches you could use:
(1) Let your program assemble the query for each iteration until all the chains are exhausted.
(2) Read the whole table (or whichever part of it contains all the records that might be needed) into your program and have the program follow the chains.

If you're only interested in a small portion of a huge table, then option (1) would probably be best. Otherwise, option (2) would be much faster and simpler.

 

[ericbrunson]

Search this forum for "adjacency model" to see a discussion on alternative ways to model your data.

 

[ericbrunson]

Here's a direct link to the paper on the MySQL site.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

[SBpsc]

Thanks guys for responding.

Eric, the weblink is going to be extremely helpful. Appreciate the help.

- SB

 

[imstillatwork]

great link.