Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
velvettiger
Posting Yak Master
115 Posts |
 Posted - 2010-03-11 : 08:09:13
|
Hi Guys,I was given a list which contain a list of student's firstname, lastname, street line 1, street line 2, street line 3 and country.This list has in 400 unique students names.The name of the list is called GR_list.I need to add the student's telephone numbers. These numbers are found in a table called student_summary. The student_summary list contains Id, firstname, lastname, street line 1, street line 2, street line 3,telephone numbers etc. This table has in thousands of rows.The first thing i did was import the list in access and then the following code was writtenselect distinct s.ID ,g.firstname ,g.lastname ,g.streetline1 ,g.streetline2 ,g.streetline3 ,g.country ,s.telephonefrom GR_list g inner join student_summary s on (g.firstname= s.firstname) and (g.lastname=s.lastname) and (g.streetline1=s.streetline1) and (g.streetline2=s.streetline2) and (g.streetline3=s.streetline3) and (g.country=s.country) When I checked the results only 40 out of the 400 person were returned. And all 400 person are in the list. Could any one tell me why this is?Thanks |
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-03-11 : 08:17:28
|
quote:
Originally posted by velvettiger
Hi Guys,I was given a list which contain a list of student's firstname, lastname, street line 1, street line 2, street line 3 and country.This list has in 400 unique students names.The name of the list is called GR_list.I need to add the student's telephone numbers. These numbers are found in a table called student_summary. The student_summary list contains Id, firstname, lastname, street line 1, street line 2, street line 3,telephone numbers etc. This table has in thousands of rows.The first thing i did was import the list in access and then the following code was writtenselect distinct s.ID ,g.firstname ,g.lastname ,g.streetline1 ,g.streetline2 ,g.streetline3 ,g.country ,s.telephonefrom GR_list g inner join student_summary s on (g.firstname= s.firstname) and (g.lastname=s.lastname) and (g.streetline1=s.streetline1) and (g.streetline2=s.streetline2) and (g.streetline3=s.streetline3) and (g.country=s.country) When I checked the results only 40 out of the 400 person were returned. And all 400 person are in the list. Could any one tell me why this is?Thanks
Perhaps try this?<b>Left join</b> intead of Inner Joinpost your realtion btw these 2 tables |
 |
|
|
velvettiger
Posting Yak Master
115 Posts |
Posted - 2010-03-11 : 08:24:28
|
| okay ill try a left join. What do you mean by the relation between the two tables? |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-03-11 : 08:36:15
|
quote:
Originally posted by velvettiger
okay ill try a left join. What do you mean by the relation between the two tables?
Any relation you have created like studentid,i meaan Foreignkey.. |
|
|
|
velvettiger
Posting Yak Master
115 Posts |
Posted - 2010-03-11 : 08:40:08
|
| no the tables doesnt have and ids. I had to use firstname,lastname and streeline1 to get a unique field. |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-03-11 : 08:47:44
|
quote:
Originally posted by velvettiger
no the tables doesnt have and ids. I had to use firstname,lastname and streeline1 to get a unique field.
It's actually a Poor design.. GR_list g inner join student_summary s on (g.firstname= s.firstname) and (g.lastname=s.lastname) and (g.streetline1=s.streetline1) and (g.streetline2=s.streetline2) and (g.streetline3=s.streetline3) and (g.country=s.country)By seeing your query there are many combinations other than you mentioned belowquote:
firstname,lastname and streeline1
if only these 3,just have only these instead of all..Not necessarily..Meaning GR_list AS g LEFT join student_summary AS s on (g.firstname= s.firstname) and (g.lastname=s.lastname) and (g.streetline1=s.streetline1) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-11 : 09:07:32
|
I think it is a kind of circle.The GR_list is the result of a select on student_summary.I can imagine that the select to get the list is modifying the output.For example trimming the values to have no leading spaces and so on...Now the join conditions are not able to exactly match.Maybe this would help?... on (ltrim(rtrim(g.firstname))= ltrim(rtrim(s.firstname))) and (ltrim(rtrim(g.lastname))=ltrim(rtrim(s.lastname))) ...Or this?... on s.firstname like '%'+g.firstname+'%' and s.lastname like '%'+g.lastname+'%' ...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-03-11 : 09:12:27
|
quote:
Originally posted by webfred
For example trimming the values to have no leading spaces and so on...
No, you're never too old to Yak'n'Roll if you're too young to die.
very Good Imagination |
|
|
|
|
|
|
|
|
Query not returning all of results