Solved

SQL QUESTION: How will i combine 3 tables?

Posted on 2004-09-29
11
175 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Suggested Solutions

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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

829 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