Displaying a financial statement using two different tables

NiallThistle

I'm fairly sure I should be using JOIN in some way to do this, but I have never attempted it before, so I'm looking for some guidance and advice.

I have two tables.

One is called val_valuations and one is called val_payments.

val_valuations contains fields valuation_id, valuation_date, valuation_amount & valuation_con

val_payments contains fields payment_id, payment_date, payment_amount & payment_con

The common field between the two is *_con, i.e. payment_con = valuation_con

I want to create an financial account statement, that will look a little like the attached jpg.

Can anyone start me in the right direction?

NiallThistle

To clarify, I've added some more info the planned output.

jorgepinho

what is the table with the foreign key ?

you have to do something like:

FROM val_valuations vv INNER JOIN val_payments vp ON vv.valuation_con = vp.payment_con

the foreign key should be the table after the JOIN.

NiallThistle

Sorry, what do you mean by foreign key?

jorgepinho

A primary key (PK) states that a field uniquely identifies the entire record

A foreign key (FK) states that this field is a primary key in another table.

It is not mandatory to have this PK-FK to join tables.

To JOIN tables you just "link" to fields that have the same meaning and will alow two records from diferent table to display as one.

Play with JOINs at will on Workbench (if MySQL)

And read stuff on SQL.

johanafm

jorgepinho;10988361 wrote:
A foreign key (FK) states that this field is a primary key in another table.

Not true. It states that the value of the field(s) defined as a foreign key must exist in the table it refers to. The referenced field(s) do not have to be the primary key or part thereof, but the referenced field(s) do have to constitute a candidate key.

jorgepinho;10988361 wrote:
It is not mandatory to have this PK-FK to join tables.

But there is absolutely no reason for not using FKs. Well, there possibly might be, but if that should be the case, such an example eludes me at the moment. Making use of FKs helps assure that you keep your database in a consistent state by enforcing the FK constraints.

Moreover, it also makes managing your database easier through the use of CASCADE, SET DEFAULT, SET NULL, RESTRICT, NO ACTION.

jorgepinho;10988361 wrote:
To JOIN tables you just "link" to fields that have the same meaning

Usually (since it makes sense) but there is no enforcement of this, most likely since the DB can't know what "having the same meaning" is. But you can actually supply anything there, just like you can in the WHERE clause, including values of primitive types.

FROM firstTable ft
JOIN otherTable ot ON ot.someField = 3 AND ft.otherField = 'three'