Multiple INNER JOINS in Access db from delphi

Question

I wrote simpliest program that connect to acceessdb and from Memo column I can execute SQL query.

Example query:

SELECT 
    company_name, date_of_order_start, date_of_order_finish
FROM 
    customers
INNER JOIN 
    orders ON customers.id_customer = orders.id_customer
WHERE 
    company_name='Ампер';

It works fine, but this query

SELECT 
    company_name, date_of_order_start, date_of_order_finish, amount
FROM 
    customers
INNER JOIN 
    orders ON customers.id_customer = orders.id_customer
INNER JOIN 
    invoice ON invoice.id_order = orders.id_order
WHERE 
    company_name='Ампер';

returns a syntax error

missing operator in expression 'customers.id_customer=orders.id_customer INNER JOIN invoice ON invoice.id_order=orders.id_orde' .

Yes, the final r in .id_order isn't showing.

Please help, where I made mistake.

Below the code of the program

    unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.DB, Vcl.StdCtrls, Vcl.DBCtrls,
  Vcl.Grids, Vcl.DBGrids, Vcl.ExtCtrls, Data.Win.ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    DataSource1: TDataSource;
    ADOQuery1: TADOQuery;
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    Button1: TButton;
    Button2: TButton;
    Memo1: TMemo;
    Label1: TLabel;
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Memo1Change(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
//проверим - есть ли текст в Memo. Если нет, выходим:
  if Memo1.Text = '' then begin
    ShowMessage('Вначале введите запрос!');
    Memo1.SetFocus;
    Exit;
  end;
  //текст есть. Очистим предыдущий запрос в наборе данных:
  ADOQuery1.SQL.Clear;
  //добавим новый запрос из Memo:
  ADOQuery1.SQL.Add(Memo1.Text);
  //открываем набор данных, т.е. выполняем запрос:
ADOQuery1.Open;

end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  Memo1.Clear;
end;

procedure TForm1.Memo1Change(Sender: TObject);
begin
  ADOQuery1.SQL.Add(Memo1.Text);
  ADOQuery1.SQL := Memo1.Lines;
end;


end.

Seems that the text of the query is truncated. I suggest to debug Delphi application and check what values is stored in text variable — Carlo, Jun 02 '16 at 12:16
Please show the code where you're retrieving the SQL text from the memo control and putting it into your ADOQuery.SQL. I'd also suggest you alias all of your tables and columns, as in `SELECT c.company_name, o.date_of_order_start, o.date_of_order_finish FROM customers c INNER JOIN orders o ON c.id_customer = o.id_customer`, to avoid any accidental conflicts with column names (and to type less). — Ken White, Jun 02 '16 at 12:37
Your assignments to ADOQuery1.SQL in Memo1Change seem to be in conflict, but I assume that you don't need either of them. — Chris Thornton, Jun 02 '16 at 13:54
Possible duplicate of [Multiple INNER JOIN SQL ACCESS](http://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access) — Val Marinov, Jun 02 '16 at 15:39

score 2 · Answer 1 · answered Jun 02 '16 at 14:56

2

Great thanks everyone who try to help me!

In access db JOIN must be in (), so this query works fine:

 SELECT 
company_name, date_of_order_start, date_of_order_finish, amount
    FROM 
(customers
    INNER JOIN orders ON customers.id_customer=orders.id_customer)
    INNER JOIN invoice ON invoice.id_order=orders.id_order
    WHERE company_name='Ампер';

answered Jun 02 '16 at 14:56

Medvedev A.

305
2
11

Correct name of the question is very important. Please rename your question with more accurate description of the problem. Here's the answer of your question with a bad name : http://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access – Val Marinov Jun 02 '16 at 15:34
You are right, but when I wrote question I don`t understand where is the problem. – Medvedev A. Jun 02 '16 at 16:10
Yes of course, but now let's do it useful for other people :) – Val Marinov Jun 02 '16 at 16:22

score 1 · Answer 2 · answered Jun 02 '16 at 14:01

1

I would suggest adding error handling around your query:

Try
  ....
  // setup and execute your query

    except on e: exception do
      begin
        LogMyErrorSomehow(format('ERROR: [%s-%s] SQL:[%s]',
           [e.ClassType.ClassName, e.Message, ADOQuery1.SQL.text]));
      end;
    end;
end;

answered Jun 02 '16 at 14:01

Chris Thornton

15,620
5
37
62

This doesn't solve the problem which IP is facing - it only provides the ability to report the error more appropriately. – Jerry Dodge Jun 02 '16 at 14:14
I think the actual problem is that the OP doesn't have basic debugging/troubleshooting skills, so yes this does help solve his problem. – Chris Thornton Jun 02 '16 at 14:27
Yer, you are right) I haven`t) actually I am beginner web developer and this task for my university work. – Medvedev A. Jun 02 '16 at 14:59

score 1 · Answer 3 · answered Jun 02 '16 at 15:09

one more information: here you write the Memo1.Text two times to ADOQuery1.SQL

procedure TForm1.Memo1Change(Sender: TObject);
begin
  ADOQuery1.SQL.Add(Memo1.Text);       // <-- first time
  ADOQuery1.SQL := Memo1.Lines;        // <-- 2nd time
end;

you write the statement to the ADOQuery1 here:

 //текст есть. Очистим предыдущий запрос в наборе данных:
  ADOQuery1.SQL.Clear;
  //добавим новый запрос из Memo:
  ADOQuery1.SQL.Add(Memo1.Text);

Multiple INNER JOINS in Access db from delphi

3 Answers3