Solved

SQL QUESTION: How will i combine 3 tables?

Posted on 2004-09-29
11
174 Views
Last Modified: 2010-04-05
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
Comment
Question by:girlswants_me
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12178035
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
 
LVL 2

Expert Comment

by:DarkCore_
ID: 12178126
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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12178366
try this SQL :

SELECT E.NAME, SUM(S.SALARY)
FROM SALARY S,
          EMPLOYEE E
WHERE S.INDEX = E.INDEX
GROUP BY E.NAME
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 12

Expert Comment

by:esoftbg
ID: 12178745
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12178788
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12178799
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
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12181936
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
 

Author Comment

by:girlswants_me
ID: 12186363
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12187749
I could make an example using multiple Paradox-tables. Will it be helpfull, or you need exactly DBase tables ?
0
 
LVL 17

Accepted Solution

by:
Wim ten Brink earned 125 total points
ID: 12188151
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
 

Author Comment

by:girlswants_me
ID: 12199003
Thank you very very very MUCH!!!!
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

778 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