Possible Self Join SELECT?

[FoxFool]

I need to make one record, out of multiple records based on a key code. Here's a sample:
mytable
CODE NAME1 ADDRESS1 CITYSTZIP1

001 Mike 1234 Main Anytown,US 99999
001 John 1234 Street AnyWhere,CA 12343
001 Dan 1234 First Mytown,UT, 22222
003 Steve 999 Main Mytown,UT,21111
003 Don 111 Second Mytown,US 22222

newTable
001 Mike 1234 Main Anytown,US 99999 John 1234 Street Anywhere,CA 12343 Dan 1234 First Mytown,UT,22222

003 Steve 999 Main Mytown,UT 21111 Don 111 Second Mytown,US 22222

I'm thinking some kind of self join based on the code field, but I have never done self join or multiple nested joins. Any help would be much appreciated!!

 

[Mike Lewis]

FoxFool,

I don't see how you can do this with a self-join, or, for that matter, any other kind of SQL statement.

It seems to me that the number of columns in your result set will be open-ended. In your example, the first record has 19 columns, because it is made up of three records. The second record has 13 columns, and comes from two records.

Given that you have a variable number of records per Code, there seems to be no way of defining the result set to accommodate that. The only possibility I can think of would be to roll up all the columns into a single memo field.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[rkolva]

I had to do something like this for a file search based on a random number of key words and the solution I came up with was to dynamically construct a self-join SELECT statement. The following should get you most of the way there but still results in a cross product recordset:

LOCAL SearchForValue AS String, lnNumberOfJoins AS Number, lsSQL AS String
LOCAL lsSelect AS String, lsFrom AS String, lsWhere AS String

SearchForValue = "001"

SELECT COUNT(test.pkey) FROM test WHERE test.pkey == ; SearchForValue INTO ARRAY laHowManyJoins

lsSelect = 'SELECT '
lsFrom 	 = ' FROM '
lsWhere  = 'WHERE a.pkey'	

FOR lnNumberOfJoins = 1 TO laHowManyJoins[1]
	lsSelect = lsSelect + SPACE(1) + SUBSTR("abcdefghi", lnNumberOfJoins, 1) + ".Name, " + SUBSTR("abcdefghi", lnNumberOfJoins, 1) + ".Address,"
	lsFrom 	 = lsFrom 	+ "Test " + SUBSTR("abcdefghi", lnNumberOfJoins, 1) + ","
	IF lnNumberOfJoins > 1 THEN
		lsWhere = lsWhere + " = " + SUBSTR("abcdefghi", lnNumberOfJoins, 1) + ".pkey AND " + SUBSTR("abcdefghi", lnNumberOfJoins, 1) + ".pkey"
	ENDIF
NEXT

lsSelect = LEFT(lsSelect, LEN(lsSelect) - 1)
lsFrom 	 = LEFT(lsFrom , LEN(lsFrom ) - 1) + SPACE(1) 

lsSQL  = lsSelect + lsFrom + lsWhere + " = '" + SearchForValue + "'" 
? lsSQL  
&lsSQL

I appologize for the formatting but too much to do right now .

Good luck with it,

Ralph