×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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:

CODE -->

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




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

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

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

(OP)
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

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

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

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

(OP)
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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close