SELECT command: col1 from table1 and col2, col3 etc from the first matching record in table2 1

[Rajesh Karunakaran]

Dear friends,

Apologies for that ambiguous title but I couldn't write an appropriate one!

I have a table with customer names (unique names). I have another where there are multiple records for same customer name. I am trying to combine these by select names from the first table and the columns from the 1st matching record (on name) from the 2nd table. But my select command mixes up things and gives me as many records as in the 2nd table, ie all records are selected from the 2nd table whereas I want only the values from the first matching record.

table1:
=======
susan
john
thomas
etc...

table2:
======
susan, 22, 2nd street
susan, 22 second street,
john, A/120, St Jean
john, A-120, St.Jean
thomas, 120B, Royal road
thomas, 120-B Royal road,
etc....

Code I tried:

SELECT table1.name, table2.add1, table2.add2 FROM table1 LEFT OUTER JOIN table2 ON table1.name == table2.name

Can you help?

Thanks in advance
Rajesh

 

[Mike Lewis]

If I've understood your question, the problem is that you are using a left outer join, whereas you should be using an inner join. Just remove the words LEFT OUTER from your query and it should do what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Hi Mike,
Thank you for your reply.
But, in fact, I tried INNER JOIN first as I remember, but it was the same result.
That's why I was trying LEFT OUTER.
May be I missed something somewhere. Let me try it as you suggested.
Thanks
Rajesh

 

[Olaf Doschke]

There is no join type only giving you the first join.

Both outer and inner joins give you all matches, outer join also gives the major table record without a match found and places NULL values for the not found records.

Wanting only the top 1 join per original row has no SQL base join type, you'll need to GROUP BY columns meaning one group per record of table1. But then there is no simple aggregation type for the first row, you can select MIN(add1) or MAX(add1) etc to only get one address column, but applied to add1, add2, etc you can get a mix of records, eg add1 of the first and add2 of the second record of some person. MS Access Jet SQL engine offers First(expression), but not VFP SQL.

A thing you can do on the result is INDEX ON name TAG name UNIQUE and then all records with the same name as already existing in a previous record are suppressed. Or you need a correlated select picking the first row by some order. I should know this from the top of my head, but I need to look at how this was done, it isn't that straight-forward.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Rajesh Karunakaran]

Dear Mike and Olaf,

Sorry for this delayed reply.

As you both suggested, I have seen that in JOINs all records are appearing if the condition is met.
Now, I extracted the records I need from table2 into another temporary cursor and then used the JOIN to combine table1 and the new cursor.
Thus, I solved the problem by using 2 SELECT statements.
Thank you for your time,
Rajesh

 

[Mike Lewis]

Good to hear that you have solved the problem, Rajesh. Thanks for letting us know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads