Structuring SQL to retrieve data from child recs

Question

Structuring SQL to retrieve data from child recs

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Arden Weiss Right there with Babe Points: 749 More actions · Answer 1

Okay, so what's conclusion(s)?

1. There is no way to do this using a single select statement. (Note: If the data in the child tables I could use the SUM function to combine the contents of the child record's values, right? However, because it is "character" data, there is no equivalent "cancatinate" type function that will combine the contents of a set of child character fields).

The reason I am not doing the bunch of what I will call sub-selects (one for each child table) is that I am in basically "report generation mode" and want a report with the data displayed in a user-meaning way.

Arden

Arden Weiss Right there with Babe Points: 749 More actions · Answer 2

Meant to say "If the data in the child tables were numeric data I could use the SUM function.....

Arden

noeld SSC Guru Points: 96590 More actions · Answer 3

weissa,

Can you answer jxflagg Last question?

I have no clue about what are your presentation requeriments and That makes it harder for me so I am guessing now:

Suppose you have

TableChild (ChildID int, ParentID int , Property varchar (100))

You Could Do:

Declare @strAllChilds varchar(4000)

Declare @MasterRecordID int

SET @MasterRecordID = X

SELECT @strAllChilds = IFNULL(@strAllChilds + ',','') + Property From TableChild Where ParentID = @MasterRecordID

SELECT @strAllChilds

Can you post more info on how are you formating the output?

Edited by - noeld on 12/24/2003 09:47:10 AM

* Noel

Arden Weiss Right there with Babe Points: 749 More actions · Answer 4

Answer to Question "What is the second query?" -- There is no 2nd query -- the query with the left joins includes the parent table REQS and the three child tables (REQ_COLL, REQ_CTRY, REQ_REFNO). As shown in the JOIN statment they are joined on a single variable REQ_ID. So, if there are two identical REQ_ID values in each child table, I get back a total of six records for each parent record. I would like to get back only one record with the child record data concatenated into a single character field.

The table display would be like so:

parent table value,list of COLLectors,list of countries, list of refnos

Arden

noeld SSC Guru Points: 96590 More actions · Answer 5

weissa,

The solution for this is a stored Procedure, As jxflagg Suggested

if you plan to retrieve more than 1 Parent Record at time

You will have to use either a Temp Table with cursor or know before hand the Maximun Number per List to build something the SET BASED way!

The Cursor version will be like:





CREATE TABLE #TBL

(REQID INT,

LISTOFCOLL VARCHAR (1000),

LISTOFCOUNTR VARCHAR (1000),

LISTOFREFNUMB VARCHAR (1000))

GO



DECLARE @CurrentREQID AS INT,

@TotListOfCol AS VARCHAR(1000),

@TotListOfCoutry AS VARCHAR(1000),

@TotListOfREFNUMB AS VARCHAR(1000)





DECLARE curREQID CURSOR LOCAL FOR

SELECT REQ_ID

FROM REQS 

WERE REQ_ID IN (X,Y,Z....)



OPEN curREQID 

FETCH NEXT FROM curREQID INTO @CurrentREQID 

WHILE (@@FETCH_STATUS = 0)

BEGIN

 

  SELECT @TotListOfCol = ISNULL( @TotListOfCol + ',','') +  CONVERT(VARCHAR, REQ_COLL ) FROM REQ_COLL WHERE REQ_ID = @CurrentREQID

  SELECT @TotListOfCoutry = ISNULL( @TotListOfCoutry + ',','') +  CONVERT(VARCHAR, CTRY_CODE ) FROM REQ_CTRY WHERE REQ_ID = @CurrentREQID

  SELECT @TotListOfREFNUMB = ISNULL( @TotListOfREFNUMB + ',','') +  CONVERT(VARCHAR, REFNO ) FROM REQ_REFNO WHERE REQ_ID = @CurrentREQID



  INSERT INTO #TBL(Name, LISTOFCOLL , LISTOFCOUNTR , LISTOFREFNUMB  ) VALUES ( @CurrentREQID, @TotListOfCol , @TotListOfCoutry , @TotListOfREFNUMB )

     



 FETCH NEXT FROM curREQID INTO @CurrentREQID 

END

CLOSE curREQID 

DEALLOCATE curREQID 



SELECT * FROM #TBL

DROP TABLE #TBL

By the way I don't know enough about CF but if XML is a posibility then

FOR XML EXPLICIT COULD be a great Alternative

* Noel

Arden Weiss Right there with Babe Points: 749 More actions · Answer 6

Thanks so much "moeld" -- that should get me on target when this app gets converted to SQL Server bvackend.

It now has an Access backend and I will have to deal with letting CF5 frontend scan/process the set of returned records.

Arden