SELECT Statement: 1

[VBAjedi]

Another SQL SELECT statement question:

I'm building a complex SELECT statement that involves six tables and a mess of criteria. Making progess, but stumped on the following:

My SELECT statement needs to return one and only one record for each record in my Sales table (which contains a unique key column named SalesID). For each record I return, I need to show (in 12 columns) a 12-month payment schedule (if one is established).

The data to make this possible is stored in a table named FieldValues, which contains three columns: SalesID, Period, and PmtAmt. For each unique Sales.SalesID, there will be as many FieldValues.SalesID records as there are payments set up. (i.e. if a sale was set up with three payments, there would be three FieldValues records where SalesID matches, with the payment amount in PmtAmt, and the payment due date in Period.)

I tried joining Sales and FieldValues, then testing Period date to see if the month matched each period, but that approach returned a record for each scheduled payment (instead of for each sale). How do I return the 12 "Scheduled payment Amount" fields for each Sales record?

VBAjedi

 

[r937]

i would do that in code, i.e. in the scripting language that you are using to pass queries and receive results

otherwise it's a twelve-table join


rudy

 

[VBAjedi]

I was afraid of that. . . I am just using "mySQL - Front" to build my query right now - I haven't selected a scripting language at all yet.

VBAjedi

 

[VBAjedi]

I was afraid of that. . . I am just using "mySQL - Front" to build my query right now - I haven't selected a scripting language at all yet.

Thanks for the advice!

VBAjedi

 

[sleipnir214]

I recommend PHP. MySQL and PHP play well together.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!