[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

SQL QUESTION: How will i combine 3 tables?

I have employee.dbf, salary.dbf, information.dbf (all of these are ONLY a sample fields)

employee.dbf fields are
  index - string;
  name - string
 
salary.dbf fields are
 index - string;
 empIndx - string;
 salary - float

information.dbf
 index - string;
 empIndx - string;
 others - string;


Note: under salary.dbf the employee will have different salary value each week. well, if you are going to get the sum of all salary per employee we would have an extra field inside tquery component having the TOTAL as the field.

 I would like to display the ff fields in the tquery component

name     others     salary    total

0
girlswants_me
Asked:
girlswants_me
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Wim ten BrinkSelf-employed developerCommented:
You want one record per employee? Well, then a sum of the salary can be calculated easily. However, you might still have multiple information records per employee. Furthermore, if you're using DBase then it would just be easier to use multiple tables that have a master-detail relation to each other. This would have a lot higher performance.
0
 
DarkCore_Commented:
dBase does support nested querys, so doing something like
SELECT ..., (SELECT SUM( s.Salary ) FROM salary s WHERE s.empIndex = employee.index ) As Total FROM employee ....
but it's terrible slow ( maybe if you have only one field ... but if it's a big table it will be terrible slow )

is possible. Maybe a better solution is what Alex pointed .... creating a calculated field, which curiously is speediest.

 QueryOnCalcFields( DataSet : TDataSet )
  Begin
     AnotherQuery.Close;
     AnotherQuery.SQL.Text := Format( 'SELECT SUM( Salary ) As Total FROM salary WHERE salary.empIndex = %d' , [ DataSet.FieldByname('Index').Asinteger ] )
     AnotherQuery.Open;
 
    DataSet.FieldByname('Total').AsFloat := AnotherQuery.FieldByname('Total').AsFloat;
  End;

Edu
0
 
Ivanov_GCommented:
try this SQL :

SELECT E.NAME, SUM(S.SALARY)
FROM SALARY S,
          EMPLOYEE E
WHERE S.INDEX = E.INDEX
GROUP BY E.NAME
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
esoftbgCommented:
Try this SQL statement:

 SELECT e.NAME, SUM(s.SALARY) as SUM_SALARY
 FROM EMPLOEE e
 LEFT JOIN SALARIES s ON s.EMPLOEE_ID = e.ID
 GROUP BY e.NAME
0
 
esoftbgCommented:
SELECT e.NAME, i.OTHERS, SUM(s.SALARY) as SUM_SALARY
 FROM emploee e
 LEFT JOIN SALARY s ON s.empIndx = e.index
 LEFT JOIN OTHERS i ON i.empIndx = e.index
 GROUP BY e.NAME, i.OTHERS
0
 
esoftbgCommented:
please ignore my above two comments ...

SELECT e.NAME, i.OTHERS, SUM(s.SALARY) as SUM_SALARY
 FROM emploee e
 LEFT JOIN SALARY s ON s.empIndx = e.index
 LEFT JOIN INFORMATION i ON i.empIndx = e.index
 GROUP BY e.NAME, i.OTHERS
0
 
Wim ten BrinkSelf-employed developerCommented:
Left joins with DBase... I've done that in the past and believe me, you have time to get a cup of coffee, add some sugar, stir it, wait until it is cooled down a bit and drink it before the BDE is done with it. In my experience, the combination BDE/SQL is just too slow.

I stick to what I said: if you want an overview of multiple tables with the BDE then use multiple tables! It's a lot faster.
0
 
girlswants_meAuthor Commented:
What do you mean by multiple tables? how will i use that using the problems above? Can you show me some codes for that please.
0
 
esoftbgCommented:
I could make an example using multiple Paradox-tables. Will it be helpfull, or you need exactly DBase tables ?
0
 
Wim ten BrinkSelf-employed developerCommented:
Just drop three TTable components and one TDataSource component on your form. Table1 for employee.dbf, Table2 for salary.dbf and Table3 for information.dbf, then set DataSource1.Dataset to Table1. For Table2 and 3, set the IndexFieldNames to 'empIndx' and the MasterFields to 'index'. This will link the three tables to each other. Now, when you walk through Table1 (record by record) then Table2 and Table3 will only contain those records that are linked to the current record in Table1.

Now, to display this all, you could use e.g. three DBGrid components and two more Datasource components. Link each grid to a datasource and make sure each datasource links to a table. thus:
DBGrid1->DataSource1->Table1
DBGrid2->DataSource2->Table2
DBGrid3->DataSource3->Table3
Then if you compile and run this all, you see your Employee table and for the currently selected record all salaries and information records. Go to the next record and this information will change too. If you want to display the total salaries of one employee, you could even just select this employee and then walk through the Salaries table yourself, calculating the sum of all salaries. With the BDE, this is about as fast as using a query to calculate it all.

While Paradox and DBase used to be very powerful in the past, they do give me a lot of grievances these days since all other database systems have improved a lot while these two haven't improved much in the last decade. Working with DBase used to be fun because it was bloody fast compared to other databases about 5 to 10 years ago. But these days other databases have improved so much that the speed difference is almost gone, and these other databases offer a lot more functionality.
0
 
girlswants_meAuthor Commented:
Thank you very very very MUCH!!!!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now