?
Solved

SQL QUESTION: How will i combine 3 tables?

Posted on 2004-09-29
11
Medium Priority
?
179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 500 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

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…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month11 days, 4 hours left to enroll

770 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