Many to one join help for flat file

Question

I’m not sure if this has already been asked – I looked around for quite a bit but couldn’t find anything.

My end result is to get data from several of my SQL database tables into a comma delimited flat file. Normally this would not be a problem but due to their many to one relationship and the format I’m forced to put it in it’s proving to be.

1st I’ve got one table, the Report table which has all basic info for a report. For example

CREATE TABLE tblReportExample
(
    ReportID int,
    ReportMonth smalldatetime,
    ReportDetails varchar(500)
)

2nd I’ve got another table with cases for each report.

CREATE TABLE tblReportCasesExample
(
    ReportID int,
    ReportCase varchar(50)
)

3rd I have a flat file definition with the following:

ReportID, ReportMonth, ReportDetails, ReportCase1, ReportCase2, ReportCase3

What I need to do is add take the top three cases from the tblReportCasesExample for a report, join it somehow with the data in tblReportExample and add it to the flat file as ReportCase1, 2 and 3.

I’ve been looking at this thing all day and just can’t figure it out.

Any ideas?

Answer 1

Please make sure you're using the correct ids here. I'm confused with what you're after.

As I could understand your problem is either one of these two:

1. single report points to multiple cases cases can be assigned to multiple reports

that'd require a many-to-many connection.

2. single report points to multiple cases single case can ba assigned to a single report

that's one-to-many

In the 2nd point, I can't see what causes you the trouble, please elaborate.

Answer 2

The ReportID in the case table is the same ReportID in the reports table. Scenario 2 is my problem. I can get one case with a left outter join but can't find a creative way to get to the other two:

SELECT re.ReportID, re.ReportMonth, re.ReportDeails, rce.ReportCase 
FROM tblReportExample re 
LEFT OUTTER JOIN tblReportCasesExample rce 
ON(re.ReportID = rce.ReportID)

Answer 3

You just need to add more cases to the cases table, and add an id to distinguish them:

CREATE TABLE tblReportCasesExample
(
    CaseID int,
    ReportID int,
    ReportCase varchar(50)
)

What causes my confusion is why you need the text file?

Answer 4

In my actual code I do have an incrementing ID field which is my primary; my example table’s just an example. What I need to do is get the report information from the tblReportExample table and use the ReportCase information from the tblReportCasesExample table to populate the ReportCase1, ReportCase2, ReportCase3 fields in the text file – all in one record, doing the same for each report number in the tblReportExample table.

How I visualize doing it would be:

Step 1: Get ReportID, ReportMonth and ReportDetails from the tblReportExample table – these would be used for the ReportID, ReportMonth and ReportDetails fields in the text file for that record.

Step 2: Loop through the tblReportCasesExample table for each ID and if there is a case for that report add it for ReportCase1 in the text file, if there is a 2nd case in the table add it to ReportCase2 – so on up to ReportCase3 – ending with it all in one record in the flat file.

In any other case I would have just used XML however this is for an older system owned by someone else in which I’m at the mercy of using a flat text file to feed the data with.

Hope I’m making a little sense – thanks for your patience as I try to explain this.

Answer 5

OK... so after playing around a little bit more I almost have what I need using the following:

SELECT 
re.ReportID, 
re.ReportMonth, 
re.ReportDeails, 
rce.ReportCase as Case1,
rceb.ReportCase as Case2,
rcec.ReportCase as Case3
FROM tblReportExample re 
    LEFT OUTER JOIN tblReportCasesExample rce 
    ON(re.ReportID = rce.ReportID)
     LEFT OUTER JOIN tblReportCasesExample rceb
     ON(re.ReportID = rceb.ReportID
     AND rce.ReportCase != rceb.ReportCase)
      LEFT OUTER JOIN tblReportCasesExample rcec
      ON(re.ReportID = rcec.ReportID
      AND rcec.ReportCase != rcea.ReportCase
      AND rcec.ReportCase != rceb.ReportCase)

This ALMOST gives me what I'm looking for...

It gives me back more then one record where there is more then one case number for a report in the tblReportCasesExample table. The data is correct but repeated, the ReportCase data is just switched around between the Case fields in the repeating rows.

ReportID | Case1 | Case2 | Case3

100 | EX100 | EX200 | EX300

100 | EX200 | EX100 | EX300

100 | EX300 | EX200 | EX100

... and so forth.

I know I must be missing something conceptually. Any ideas?

Answer 6

Suggested update to your question: Not just "flat file", but "CSV file". Presumably, the CSV file is an export to another system, or just to somebody who likes to view things in Excel :-)

Another comment from an old timer (meaning, sombody who has done MUCH file transfer system integration work): Not all the world is an SQL database. Especially if you don't need "ACID" for a task (other than a transaction around the initial extract(s)).

Dump (the relevant portions of) the two tables into a pair of CSV files. Then assemble the final CSV file "procedurally". The Practical Extraction and Reporting Language (aka "perl") is a good tool for this sort of thing, but there are others.

Read the "tblReportCasesExample" data into an indexed data structure of some kind.

Iterate through the "tblReportExample" data:

• select your "favorite" tblReportCasesExample entries

• dump the tblReportExample fields, followed by the select (key) values from the relevant tblReportCasesExample rows / lines.

Find a CSV handling library if you have any special chars in the data. Otherwise, just use "split" (perl and java both have something like this, I suspect the .NET library does as well).

Need more details / care at all?

Answer 7

I haven't tested this on MS SQL Server (which I infer from your use of the smalldatetime data type), but I've seen some articles about tricks you can do with FOR XML PATH.

SELECT r.*,
  (SELECT TOP 3 c.ReportCase + ',' AS [text()]
   FROM tblReportCasesExample c
   WHERE c.ReportId = r.ReportId
   FOR XML PATH('')
  ) AS ReportCaseList
FROM tblReportExample r;

The result should have the columns of tblReportExample, plus a comma-separated string of the top three report cases. Then perhaps in the CSV file, you won't know that some of the commas were part of that string instead of separating columns. :-)