Joined table -- display in datagrid

Ok here goes. I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes). With me so far? Easy stuff, now for the hard part.

Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control. I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case. I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it.

Any ideas on how to attack this?

You are saying you have the query working. Are you having problem displaying the data? If so I can move the post to Datagrid section where you have a better chance of receiving help.|||

ndinakar:

Are you having problem displaying the data?

I think its more sql based than grid based -- might be both (probably is). Here's an example.

Table - Cases : caseID (pk), CaseNumber, Notes
Table - Outcomes : outcomeID (pk), outcome
Table - CasesOutcome : caseID (fk), outcomeID (fk)

Data - Cases :
caseID : 1
CaseNumber : 2007xx45
Notes : (empty)

Data - Outcomes :
outcomeID : 1
outcome : guilty as charged by judge

outcomeID : 2
outcome : pled guilty to felony assault charge

outcomeID : 3
outcome : pled guilty to felony theft charge

Data - CasesOutcome:
caseID : 1
outcomeID : 2

caseID : 1
outcomeID : 3

When this is all said and done, a grid is generated with two rows, repeating the data (shows the case number twice) which is not really desired -- the outcome however is. Ideally the two of those items would show up under the gridview in 1 column (when being edited, they change to dropdowns) -- but I'm not sure if this is even possible.

|||Can you post the query you have, and the expected result.

JOIN Statement Help

I am very new to SQL and need to create a statement that will JOIN data from 3 tables into my datagrid. The following are the tables:
Table A: Compliance
- FinancialsID
-NetWorth
-DebtRatio
-WorkCapital

Table B: Financials
- FinancialsID
- cAssets
- TransDate
- CustomerID

Table C: CompanyInfo
- CustomerID
- Company
- Agent

I need to be able to display Company.CompanyInfo, NetWorth.Compliance, DebtRatio.Compliance, WorkCapital.Compliance in a datagrid and make sure that it ONLY displays the most current entry for the Company.

The Compliance table has a relationship to the Financials table through the FinancialsID field and the Financials table is related to the CompanyInfo table through the CustomerID field. The TransDate is a date field in the Financials table.

This seems extremely confusing to me, but I am sure its easier than what I am trying to make it.

Any help would be GREATLY appreciated.

Thanks
Garrettto start with :

SELECT Company.CompanyInfo, NetWorth.Compliance, DebtRatio.Compliance, WorkCapital.Compliance
FROM CompanyInfo ,Financials ,Compliance
WHERE companyInfo.customerid = Financials.customerid AND Financials.FinancialsID=Compliance.FinancialsID

now what do you mean by most current ? does it have anything to do with the date ?|||Thanks for the help on this.

There can be multiple financial records for each company. There is a date field in the Financials table named TransDate so that I can differentiate the records within a company.

The main purpose of my web app is to create a sort of 'PipeLine'. When a user logs into the page he/she is supposed to see the most current financials for each company in their respective pipeline.

For this reason, I need to ensure that only the most current record is returned for each company.

Any Ideas?-

Thanks|||yes but I dont understand what you mean by "current" do you mean today ? what is the condition for the date ? give technical details.|||you need to include the date in ur sql statement and order by date|||I have been able to JOIN the tables and display the correct fields in the datagrid, however, I have been unable to reconstruct the SQL statement so that only the LATEST financial records row is displayed for each Company.

Here is what I have:

SELECT CompanyInfo.Company, CompanyInfo.customerID, CompanyInfo.uname,
Compliance.FinancialsID, Compliance.NetWorth, Compliance.uname,
Compliance.DebtRatio, Compliance.WorkCapital FROM CompanyInfo INNER JOIN
Financials ON CompanyInfo.customerID = Financials.customerID INNER JOIN Compliance
ON Financials.FinancialsID = Compliance.FinancialsID ORDER BY TransDate DESC"

How do I simple return one record for each company and make sure that record is the most current by date (TransDate)?

Thanks|||Think I figured it out.

"SELECT CompanyInfo.Company, CompanyInfo.CustomerID, CompanyInfo.uname, " & _
"Compliance.FinancialsID, Compliance.NetWorth, Compliance.uname, " & _
"Compliance.DebtRatio, Financials.TransDate, Compliance.WorkCapital " & _
"FROM CompanyInfo " & _
"INNER JOIN Financials ON CompanyInfo.CustomerID = Financials.CustomerID " & _
"INNER JOIN Compliance ON Financials.FinancialsID = Compliance.FinancialsID " & _
"WHERE Financials.TransDate = (SELECT MAX(TRansDate) FROM Financials F1 " & _
"WHERE F1.CustomerID = Financials.CustomerID)"

Thanks for everyone's help.