Master/Details Complicated SELECT

Experts,
Ive got two tables which have the following names and fields:

News_Master:
ValueDate      
[Time]      
Language_IKey      
RowsA      
RowsE      
HeadE      
HeadA      

News_Details:
ValueDate      
[Time]      
Language_IKey      
TRow      
Text

My problem:
1      The master table has the title of the news (head) and the details table has the body of the news (text).
2      Master table and details table link via a double key field which are [time] & [language_ikey].
3      The corresponding text in the details table is not in one row, it is spitted into multiple rows. The [text] field in these rows corresponding to the master table is to be selected and concatenated by sequence of the [TRow].
4      [TRow] has the index value of which you may select the the news detail multiple rows and ordered by its value to get the proper sentences at the end.
5      The fields [RowsA] or [RowsE] according to the specified language, have the total count of the rows in the corresponding details table.

What I need to do is: to select the news which is completed and display them in one piece; [news title] and [news body]. This takes a check on the maximum value of the [RowsA,E] field and compare it with the [TRow] field, and when they match then the news is complete.

This is a bit tough one. Thanks in advance to all.
feesuAsked:
Who is Participating?
 
ispalenyConnect With a Mentor Commented:
Run SQL and merge on client:

SELECT
    M.[Time]
    ,M.Language_IKey
    ,M.HeadE
    ,M.HeadA
    ,D.Text
    ,D.TRow
from News_Master M
join News_Details D
on
    M.[Time]=D.[Time]
AND
    M.Language_IKey=D.Language_IKey
join
(
    select
        [Time]      
        ,Language_IKey
       ,MAX(TRow) M_TRow
    from News_Details
    group
        by
        [Time]      
        ,Language_IKey
) AD
on
    M.[Time]=AD.[Time]
AND
    M.Language_IKey=AD.Language_IKey
AND
    ISNULL(M.RowsA,0)+IsNull(M.RowsE,0) = AD.M_TRow
ORDER BY
    M.[Time]
    ,M.Language_IKey
    ,D.TRow
 ;

Or create a scalar UDF dbo.fn_MergeDetailText scanning Detail table and merging texts for one Master.
It would have a limited output size in SQL Server 2000.

SELECT
    M.[Time]
    ,M.Language_IKey
    ,CAST(IsNull(M.HeadE,'') AS VARCHAR(8000))
    +IsNull(M.HeadA,'')
    +CHAR(10)
    +dbo.fn_MergeDetailText(M.[Time],M.Language_IKey)
    Message
from News_Master M
join
(
    select
        [Time]      
        ,Language_IKey
       ,MAX(TRow) M_TRow
    from News_Details
    group
        by
        [Time]      
        ,Language_IKey
) AD
on
    M.[Time]=AD.[Time]
AND
    M.Language_IKey=AD.Language_IKey
AND
    ISNULL(M.RowsA,0)+IsNull(M.RowsE,0) = AD.M_TRow
ORDER BY
    M.[Time]
    ,M.Language_IKey
 ;
0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
Here is a start, I'm not clear on how you determine whether a news item is complete or not.  You should be able to add a WHERE clause to the query below to take care of that.

SELECT M.*, dbo.udfConcatNews(M.[Time], M.Language_IKey)
FROM News_Master AS M

CREATE FUNCTION dbo.udfConcatNews(@Time datetime, @LanguageKey int)
AS nvarchar(MAX)
BEGIN
DECLARE @Text nvarchar(MAX)
SET @Text = ''
SELECT @Text = @Text + [Text]
FROM News_Details
WHERE [Time] = @Time
   AND Language_IKey = @LanguageKey
ORDER BY TRow

RETURN @Text
END
0
 
feesuAuthor Commented:
ispaleny,
Your code is very good, and helped alot. But there is a gap in it. Sometimes you might find the maximum TRow equal to the RowsA/E but there is a row missing in between in the details table!

i.e. having 5 rows for this master item, but in the details table you find (1,3,4,5) so number 2 is  missing. Looking for only the maximum row doesn't solve it perfectly.

Note: i do prefer to solve it all on SQL Server (2000).

BriCrowe,
Your function is perfect!
0
 
feesuAuthor Commented:
ispaleny,
I only had to change the max to count in your query and it worked fine for me.
Thanks alot for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.