Solved

Quering problem

Posted on 2006-11-02
5
181 Views
Last Modified: 2010-04-05
I'm trying to query two dbf tables one that is there at my database with a program on foxpro 2.6 and the other is one that i created apart on excel, if i do something like this:

Table 1

1   jerry  programmer
2   Mary  analyst

Table 2

1  project1  
2  project2

i must be able to do this.

Select * from table1, table2 where table1.field1 = table2.field1

and get as result something like that

1   jerry  programmer   1 project1
2   Mary  analyst           2 project2


My problem seems to be that i created a file from a excel and save it as dbf file but when i try to do a query as simple as the one shown, it throws the messege error i configure it to throw.

By the way i'm using a Delphi interface i develope to query those tables and don't know the structure of the dbf files on foxpro. Basically i use the my app to query and get info that i need to know that the fox pro program does not give me.

How can i solve this? Any suggestions will be appreciated.

Thanks ahead!!
0
Comment
Question by:sisiliano
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 17861842
Who will be processing the query "Select * from table1, table2 where table1.field1 = table2.field1". You have different DBs... My advice is do import these 2 tables in real DB and execute the query there.

If you want to do it your way, you will have to do it in loop organized by yourself. But it will be a problem to handle N:M relations...
0
 
LVL 1

Author Comment

by:sisiliano
ID: 17862722
And how may i do that, how to import both tables to a database, let say table1 is already in a database the one on foxpro, how do i import the other one i created on excel to these database or both to the same db, i guess i would not work just to copy paste it, would it?

Hope you could at least give a clue.

Thax so far Ivanov
0
 
LVL 12

Assisted Solution

by:Ivanov_G
Ivanov_G earned 50 total points
ID: 17862870
Almost every database have utilities for import/export. I have no experience with Paradox, so I can not answer you. Maybe there is something in the documentation.

The task can also be done using MS SQL Import and Export Wizard if you have it.

Otherwise you have to do that programatically. Open 2 connection from a test Delphi application and use "While not Table1.EOF" query for first database to insert in the second one.
0
 
LVL 6

Accepted Solution

by:
wimmeyvaert earned 50 total points
ID: 17864529
I guess you can Create an MS-Access db and import the dbf's into your access-db.

I gues there is a many-to^many relationship between Table1 and Table2, isn't it ?

If this is the case, then you should have a third table :
Person
   PersonID    Name
   1               jerry  programmer
   2               Mary  analyst

Project
   ProjectId     Name
   1                project1  
   2                project2

ProjPerson
   ID   PersonID    ProjectID
   1     1              1
   2     2              1
   3     1              2

Thne your query could look like :
SELECT p.Name AS [Person], Pr.Name AS [Project]
FROM T_Person p, T_Project pr, ProjPerson pp
WHERE p.PersonID = pp.PersonID
     AND pr.ProjectId = pp.ProjectID
ORDER BY p.Name, pr.Name

Output will look like
   Person                      Project
   ----------------------------------------
   jerry  programmer     Project1
   Mary  analyst             Project1
   jerry  programmer     Project2  


0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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

19 Experts available now in Live!

Get 1:1 Help Now