sql server 2000, left join 1

[pssheba]

Seems i dont know how to use it.
Hi !
I'm having 2 tables. The first one containes 2 rows which i get wherby importing a text file. It has all the information i need except Phone numbers.
The other one contains 100000 rows and contains all the clients plus their telephones.
I want to add table 1 the missing tel.numbers whereby using "left join". In both tables i dont use primary keys !

select 
a.lastname,
b.phone1
into
temp
from
table1 as a left join table2 as b
on
a.lastname=b.lastname

I get temp table containing at least 100000 rows !!!
I want only 2 rows, thosee of table 1 + phone number imported from table 2.
Can any one tell me whats wrong with my "left join" ?
Could it be the lack of primary key ? Is there a remedy to the lack of Primary key assuming this is the problem?
Thanks a lot !

 

[AngelB]

Try using an inner join instead since you only want matching records.

Hope this helps


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[bborissov]

With LEFT JOIN you get ALL the records from first table and ALL MATCHING RECORDS from second table.
And don't tell me that you didn't have clients with the same LastName.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pssheba]

Inner join didnt help either...
Any idea how i can get 2 rows only ?

 

[Catadmin]

If I understand your question correctly, you've got the following situation.

Table 1
Name Address City State
Bob Here Dunno IA
OtherBob There Somewhere FL

Table 2
Name Phone
Bob 555-2256
Bob 888-2285
Bob 999-7413
OtherBob 555-5478
OtherBob 222-8857
OtherBob 996-7741
OtherBob 444-2584

And you want your data to look like this:

Name Phone
Bob 555-2256 888-2285 999-7413
OtherBob 555-5478 222-8857 996-7741 444-2584

Is this correct? If so, check out faq183-6466. If not, please give us an example of the data in both tables so we can more effective help you out.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bborissov]

SELECT a.lastname,
       b.phone1
from table1 as a
INNER JOIN (SELECT LastName, MAX(Phone1) AS Phone1
                   FROM Table2
                   GROUP BY LastName) b
on a.lastname=b.lastname

BUT this query can give you unpredictable results, becuas it takes only MAX() phone for LastName.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Catadmin]

And as has been mentioned before, joining only on last name is a horrible way to do this because if you have 100 Smiths in table 2, you're going to get the top phone number of the first Smith in the list, not necessarily the phone number of Tom Smith, who is the person you're looking for.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[pssheba]

Hi Catadmin !
Relating to your first question: This is not the case and the link you provided didnt relate to my problem.
The problem is:table 1 contains 2 rows: Bob,Smith.
Table 2 contains 200000 rows of which 50000 contain Bob as lastname and 100000 contain Smith as lastname.
I only want Bob's Tel number as well as Smith's and add it to table 2 so it remains with 2 rows but one more column.
My "Left join" yielded a table of at least 200000 rows rather then only 2.
Thanks for your efforts !