[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Master/Details Complicated SELECT

Posted on 2007-07-31
4
Medium Priority
?
349 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:feesu
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
ispaleny earned 1400 total points
ID: 19603832
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
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 600 total points
ID: 19603965
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
 

Author Comment

by:feesu
ID: 19606648
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
 

Author Comment

by:feesu
ID: 19606732
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question