sql select syntax

[mwmark]

Firt time using "add command" in the database expert in crystal 2011 for a sql database and need a syntax check.
I have a working report created with crystals database linking but the tables are huge and the report takes too long.
Attempting to speed up a report by only bringing in columns i need.

here is what i have:
SELECT INVOICE_NUMBER, ESTIMATE_NUMBER, INVOICE_DATE FROM HISTHDR;
SELECT ESTIMATE_NUMBER,CLASS,ACCOUNT_CODE, ITEM_NUMBER, QUANTITY, DECLINED, COST, SELL_PRICE, SELL_LABOR, SALESMAN FROM HISTLINE;
SELECT MECHANIC_NAME FROM MECHANIC;
INNER JOIN HISTHDR.ESTIMATE_NUMBER = HISTLINE.ESTIMATE_NUMBER;
INNER JOIN HISTLINE.SALESMAN = MECHANIC.MECHANIC.NAME;

Will this work?
Is there a better way to write it?

thanks for your help!

 

[briangriffin]

Not sure what your backend database is, but that would be unusual syntax - as long as your query returns a dataset in your SQL editor you can use it in a command object. I would expect something like this:

SELECT
HISTHDR.INVOICE_NUMBER,
HISTHDR.ESTIMATE_NUMBER,
HISTHDR.INVOICE_DATE,
HISTLINE.ESTIMATE_NUMBER,
HISTLINE.CLASS,ACCOUNT_CODE,
HISTLINE.ITEM_NUMBER,
HISTLINE.QUANTITY,
HISTLINE.DECLINED,
HISTLINE.COST,
HISTLINE.SELL_PRICE,
HISTLINE.SELL_LABOR,
HISTLINE.SALESMAN,
MECHANIC.MECHANIC_NAME

from HISTHDR inner join HISTLINE
on HISTHDR.ESTIMATE_NUMBER = HISTLINE.ESTIMATE_NUMBER

INNER JOIN MECHANIC
on HISTLINE.SALESMAN = MECHANIC.MECHANIC.NAME

 

[mwmark]

I am using a sql database.

When I copied this to the command box I do not get any errors (no error after correct "." to a "_" in the INNER JOIN MECHANIC
on HISTLINE.SALESMAN = MECHANIC.MECHANIC.NAME) but i also do not get any data.

I removed the table "mechanic" and the second inner join and will get all my data.