Query on multiple tables on multiple fields

Posted on 2011-05-11
Last Modified: 2012-05-11
Could someome please point me in the right direction. I am using DXE and Mydac to connect to a MySQL database. I am very new to MySQL.

I have 3 tables and in those tables I have 3 fields that I need to sum and place those values in variables to send to my report.

I appreciate any help.
Question by:DigitalNam
    LVL 24

    Accepted Solution

    You can use SQL query, such as:

    Select Sum(T1.FieldA) Total from Table1 T1

    Select Sum(T2.FieldB) Total from Table2 T2

    Select Sum(T3.FieldC) Total from Table3 T3

    And then use DBQuery.FieldByName('Total').AsFloat (or AsInteger) to assign the total into the variables.
    LVL 9

    Assisted Solution

    Drop TMyQuery and connect it with TMyConnection and use the following example

    procedure TForm1.Button1Click(Sender: TObject);
    var MyField: string;
        Sum: real;
    MyField := 'sum(Field1)+sum(Field2)+sum(Field3)'; // Fields of the Table1
    MyQuery1.SQL.Text := 'select '+MyField+' from table1';
    Sum := MyQuery1.FieldByName(MyField).AsFloat;
    MyField := 'sum(Field1)+sum(Field2)+sum(Field3)'; // Fields of the  Table2
    MyQuery1.SQL.Text := 'select '+MyField+' from table2';
    Sum := MyQuery1.FieldByName(MyField).AsFloat + sum;
    MyField := 'sum(Field1)+sum(Field2)+sum(Field3)'; // Fields of the Table3
    MyQuery1.SQL.Text := 'select '+MyField+' from table3';
    Sum := MyQuery1.FieldByName(MyField).AsFloat + sum;
    showmessage('Total is '+FloattoStr(Sum));

    Open in new window

    LVL 1

    Author Comment

    Thank you for the replies. Something came up and I will only be able to test the code tomorrow. Will keep you updated.
    LVL 1

    Author Closing Comment

    thank you, you guys put me on the correct way to achieve this.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
    Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now