Many to one join help for flat file

flatflat-filejoin;one-to-many

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?

Best Answer

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?

Related Solutions

C# – the syntax for an inner join in LINQ to SQL

It goes something like:

from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}

It would be nice to have sensible names and fields for your tables for a better example. :)

Update

I think for your query this might be more appropriate:

var dealercontacts = from contact in DealerContact
                     join dealer in Dealer on contact.DealerId equals dealer.ID
                     select contact;

Since you are looking for the contacts, not the dealers.

SQL join: selecting the last records in a one-to-many relationship

This is an example of the greatest-n-per-group problem that has appeared regularly on StackOverflow.

Here's how I usually recommend solving it:

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
    (p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id)))
WHERE p2.id IS NULL;

Explanation: given a row p1, there should be no row p2 with the same customer and a later date (or in the case of ties, a later id). When we find that to be true, then p1 is the most recent purchase for that customer.

Regarding indexes, I'd create a compound index in purchase over the columns (customer_id, date, id). That may allow the outer join to be done using a covering index. Be sure to test on your platform, because optimization is implementation-dependent. Use the features of your RDBMS to analyze the optimization plan. E.g. EXPLAIN on MySQL.

Some people use subqueries instead of the solution I show above, but I find my solution makes it easier to resolve ties.

Best Answer

Related Solutions

C# – the syntax for an inner join in LINQ to SQL

SQL join: selecting the last records in a one-to-many relationship

Related Topic