How to set relations between two tables 1

[Niki_S]

I have two tables and one is in foxpro and the other one is in SQL. What I want is, those tables have same fields but the field names are different.

foxpro table
nStylecode          cLotName
12345               A1

SQL table
csty_no         clot
12345           A1

Now I need to get records from foxpro table that only have records in SQL table. Something like this,

SELECT * from _DelClr where nStylecode in (SELECT csty_no FROM MIS.dbo.vInvFinal) AND clotname in (SELECT clot FROM MIS.dbo.vInvFinal)

_DelClr = foxpro table
How can I do this?

Thank you

 

[Chris Miller]

Hi Niki,

there you have your root problem of trying to combine VFP data and SQL data in a query that only can see SQL data and single value parameters, but not VFP workareas or tables.

I bet you create the foxpro table from an SQL Server select. Well, and there is your solution, go back to the SQL source, a query you send to SQL Server should be extended with an INNER JOIN of MIS.dbo.vInvFinal to only return the result rows that fullfil your condition.

In the situation you have, you would need to "upload" the FoxPro _DElCLr data to SQL Server, so that it can join that to MIS.dbo.vInvFinal. And that's not the solution to aim for, solve the problem at the root, not at the situation you have now and don't create _DelClr with only half the query you need, select only the data you want in the first place.

Chriss

 

[Niki_S]

Thank you for the help