January 20, 2015 at 10:48 am
First, hello!! 🙂 I hope this is the right place to post such a beginner question. I am a complete novice, at best, but really like sql/php/et al; desire without knowledge causes hair pulling-out 😀 I'm also new to sql server but finally got 2014 installed.
I am trying to join a few tables, but using the below code I lose many records. The records table has about 27000 rows but after joining the tables there's only 7000.
select *
from Service_Record
join vendor_List
on Service_Record.Vendor_Nbr = Vendor_List.Vendor_Nbr
join Engineer_List
on Service_Record.Engineer_ID = Engineer_List.Engineer_ID
join Parts_Replaced
on Service_Record.Service_Record_Nbr = Parts_Replaced.Service_Record_Nbr
As you can probably infer, I'm trying to combine parts used, engineer, and any vendor info associated with the service records onto one table, which I'll then export to excel to filter/pivot.
Any direction would be greatly appreciated.
Josh
January 20, 2015 at 10:58 am
dmj120 (1/20/2015)
First, hello!! 🙂 I hope this is the right place to post such a beginner question. I am a complete novice, at best, but really like sql/php/et al; desire without knowledge causes hair pulling-out 😀 I'm also new to sql server but finally got 2014 installed.I am trying to join a few tables, but using the below code I lose many records. The records table has about 27000 rows but after joining the tables there's only 7000.
select *
from Service_Record
join vendor_List
on Service_Record.Vendor_Nbr = Vendor_List.Vendor_Nbr
join Engineer_List
on Service_Record.Engineer_ID = Engineer_List.Engineer_ID
join Parts_Replaced
on Service_Record.Service_Record_Nbr = Parts_Replaced.Service_Record_Nbr
As you can probably infer, I'm trying to combine parts used, engineer, and any vendor info associated with the service records onto one table, which I'll then export to excel to filter/pivot.
Any direction would be greatly appreciated.
Josh
If any of the tables you are joining to have 'missing' rows – ie, there is a value in the main table but with no match in the table it is joining to – you will lose rows.
Best way to find out is by building up the joins one at a time, running the query & watching the count of rows returned each time.
I would also suggest that
a) You learn about and start using table aliases
b) You always include the underlying schema name to qualify your table names
Here is your code rewritten with these changes in place:
select sr.*
from dbo.Service_Record sr
join dbo.vendor_List vl on sr.Vendor_Nbr = vl.Vendor_Nbr
join dbo.Engineer_List el on sr.Engineer_ID = el.Engineer_ID
join dbo.Parts_Replaced pr on sr.Service_Record_Nbr = pr.Service_Record_Nbr
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 20, 2015 at 11:08 am
Thank you! I will try that, and start looking into your two suggestions :w00t:
January 20, 2015 at 11:11 am
dmj120 (1/20/2015)
I am trying to join a few tables, but using the below code I lose many records. The records table has about 27000 rows but after joining the tables there's only 7000.
select *
from Service_Record
join vendor_List
on Service_Record.Vendor_Nbr = Vendor_List.Vendor_Nbr
join Engineer_List
on Service_Record.Engineer_ID = Engineer_List.Engineer_ID
join Parts_Replaced
on Service_Record.Service_Record_Nbr = Parts_Replaced.Service_Record_Nbr
In addition to what Phil said, specifying just JOIN will default to INNER JOIN. Inner join will only return those rows that exist in both tables. In this case, for a Service_Record to be returned it needs to satisfy all 3 joins. Try using a LEFT JOIN and you will probably get what you thought you wanted.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 20, 2015 at 11:19 am
Awesome!! Adding left join did the trick!
This looks like a pretty cool site. Going to check out the video and training links.
Thanks for the help and suggestions
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply