SQL join on two colums

[coldan]

In my app I'm trying to extract 2 names from a table (table1) which has two fields - lets say PNUM1 and PNUM2 in a record.

The actual names for these these namenumbers are held in another table where field REALNAME is kept against a reference to a PNUM field



SELECT * FROM table1 AS a inner join table2 AS b ON a.pnum11 = b.pnum OR a.pnum2 = b.pnum INTO Cursor mycursor

I don't mind if a record from table1 is extracted in 2 rows
in mycursor.

Is this a valid SQL join?

My actual SQL would/is more complex but I need guidance on the rationale of the above

 

[cbasoz]

While the SQL itself seems to be valid it looks like it is not what you meant in your description (and worse you are describing a bad data design). Would you give some real samples?
You can easily create some sample data from your existing cursors/tables in text form using this utility:

http://www.foxite.com/faq/default.aspx?id=50

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Coldan,

The actual names for these these namenumbers are held in another table where field REALNAME is kept against a reference to a PNUM field

So, are you saying that PNUM1 adnd PNUM2 are the not the actual field names? If so, then your SQL cannot work, given that those names are hard-coded in the command.

You might need to construct the command programmatically, and then macro-execute it. But it would help if you could explain a bit more clearly what you are trying to achieve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[coldan]

Thanks Mike and Cetin

What I'm saying is that the code I have given is 'similar' to the actual code required but for simplicity I have used other names.

Thanks

 

[cbasoz]

And aren't you going to give us part of real data using the utility that I gave a link for?

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

And please also let us know what problem you are having with the SELECT.

You asked if it's a valid JOIN. Have you actually tried running it?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[alan92rttt]

I think this is what you need.

SELECT * FROM table1 main;
  left join table2 name1 ON main.pnum1 = name1.pnum ;
  left join table2 name2 on main.pnum2 = name2.pnum;
  INTO Cursor  mycursor;

Alan