Solved

SQL QUESTION: How will i combine 3 tables?

Posted on 2004-09-29
11
170 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
Comment Utility
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_
Comment Utility
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
Comment Utility
try this SQL :

SELECT E.NAME, SUM(S.SALARY)
FROM SALARY S,
          EMPLOYEE E
WHERE S.INDEX = E.INDEX
GROUP BY E.NAME
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very very very MUCH!!!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now