Structuring SQL to retrieve data from child recs
-
How do I go about having the DB SELECT the contents of a single field in a bunch of child records into a single record formatted as a string of characters?
Currently, I am using a left join to do the SELECT and it retrives one record for each child record. I then use the front end tool (Cold Fusion) to scan the selected records to prepare a report that has one table row per parent record (very tedious).
Like to have the DB do the heavy lifting.
Thanks for any/all advice this very Christmas Eve...
Arden -
Can you post your first query and the query that CF is issuing?
-
can you post something about the structure of your tables?
the short answer is I think you are doing the right thing (Formatting is a Front-End task). But I have almost no info to back that up 100%
* Noel -
Even if this is best done as two queries, it would probably be better to write it into a stored procedure rather than having CF issue a second select.
-
quote:
Even if this is best done as two queries, it would probably be better to write it into a stored procedure rather than having CF issue a second select.There is no info as of now!! on how are things implemented ( used) supposed a Master -Detail Application.
You should NOT present the Details until you know about what Master was requested
* Noel -
The left join query is as follows -- syntax is actually for ODBC MS Access, but logic holds for SQL Server.
For an actual query I get about 400 records returned for about 10 parent records, hence using Cold Fusion front end to scan results set get very slow.
<CFQUERY NAME="REQ_GET" DATASOURCE="IIR">
SELECT REQS.*,
REQ_COLL.S_REQ_ID,
REQ_COLL.COLL_CODE,
REQ_COLL.COLL_PRTY,
REQ_CTRY.CTRY_CODE,
REQ_REFNO.REFNO,
REQ_REFNO.REF_DT,
REQ_REFNO.REF_STAT,
REQ_REFNO.REF_STAT_DT,
REQ_REFNO.REF_RMKS
FROM
((REQS LEFT JOIN REQ_COLL ON REQS.REQ_ID = REQ_COLL.REQ_ID)
LEFT JOIN REQ_CTRY ON REQS.REQ_ID = REQ_CTRY.REQ_ID)
LEFT JOIN REQ_REFNO ON REQS.REQ_ID = REQ_REFNO.REQ_ID
ORDER BY
REQS.REQ_CODE,
REQ_COLL.S_REQ_ID,
REQ_CTRY.CTRY_CODE,
REQ_REFNO.REFNO
</CFQUERY>
Arden -
quote:
There is no info as of now!! on how are things implemented ( used) supposed a Master -Detail Application.I have no idea what you just said.
-
quote:
The left join query is as follows -- syntax is actually for ODBC MS Access, but logic holds for SQL Server.For an actual query I get about 400 records returned for about 10 parent records, hence using Cold Fusion front end to scan results set get very slow.
<CFQUERY NAME="REQ_GET" DATASOURCE="IIR">
SELECT REQS.*,
REQ_COLL.S_REQ_ID,
REQ_COLL.COLL_CODE,
REQ_COLL.COLL_PRTY,
REQ_CTRY.CTRY_CODE,
REQ_REFNO.REFNO,
REQ_REFNO.REF_DT,
REQ_REFNO.REF_STAT,
REQ_REFNO.REF_STAT_DT,
REQ_REFNO.REF_RMKS
FROM
((REQS LEFT JOIN REQ_COLL ON REQS.REQ_ID = REQ_COLL.REQ_ID)
LEFT JOIN REQ_CTRY ON REQS.REQ_ID = REQ_CTRY.REQ_ID)
LEFT JOIN REQ_REFNO ON REQS.REQ_ID = REQ_REFNO.REQ_ID
ORDER BY
REQS.REQ_CODE,
REQ_COLL.S_REQ_ID,
REQ_CTRY.CTRY_CODE,
REQ_REFNO.REFNO
</CFQUERY>
I don't know much about CF but
I believe that your problem should be solved writting a where clause that uses The Mastrer Record To retrieve ALL the childs at once
* Noel -
weissa, so that's the first query that you issue, right. And then based on the results, you issue a seprate query for each record you got from the first query, correct?
What is that seperate query that you issue (400 times?)?
-
quote:
quote:
There is no info as of now!! on how are things implemented ( used) supposed a Master -Detail Application.I have no idea what you just said.
You just cut my post in half!!!
quote:
There is no info as of now!! on how are things implemented ( used)
Supposed a Master -Detail Application.
You should NOT present the Details until you know about what Master was requested
* Noel -
I still have no idea what you said.
-
quote:
Even if this is best done as two queries, it would probably be better to write it into a stored procedure rather than having CF issue a second select.The Second SELECT query IS usually NEEDED in a Master-Detail Front-End Type
Edited by - noeld on 12/24/2003 08:49:07 AM
* Noel -
quote:
The Second SELECT query IS usually NEEDED in a Master-Detail Front-End TypeA second query may be needed, or it may not be. But if it is required, my point is that it would be better to call a single stored procedure that issues all of the queries and returns multiple result sets. CF can then loop through each result set.
I'm simply advocating encapsulation of business logic in stored procedures rather than in front end code.
-
I agree with you 100% I just said that at THAT moment it was a little too fast to get to any conlusions. If the App can be architected that way THAT'S definitely the way to go. There are cases where the user is presented with the Master records and the childs are only retrieved if requested
By no means I dissagreed with your answer, just commented on it
* Noel -
Glad that we cleared that up.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply