SQL Joining on Multiple columns

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Joining on Multiple columns

Author

Topic

Vogel515
Starting Member

5 Posts

Posted - 2011-12-09 : 10:30:27

I'm fairly new to SQL and I did a search and could not quite locate what I wanted to find, so I'm going to apologize in advance if I missed it.

Anyway, I'd like to know why my inner join using three columns produces a multiple of lines. As a preface, I know this is not the best way do accomplish what I am doing, but I want to understand why this is happening.

I have three tables, Jobs, SubJobs, SubJobExpense.

Jobs - fiscal year(fy), fiscal period (fp), JobsID... all three are primary keys.

Subjobs - fy, fp, jobsid, subjobsid... all four are primary keys. For each Fiscal Yr / Period / Job and sub job there is a line item.

Subjobsex - fy, fp, jobsid, subjobsid, year to date expenses

I want to create a view that gives me

FY, FP, Jobsid, subjobsid, FYTD expenses

code:

select
jobs.fy,
jobs.fp,
jobs.jobsid,
subjobs.subjobsuct,
ex.FYTD_expense
from
dbo.jobs as jobs
inner join
dbo.subjobs as subjobs
on
subjobs.fy = jobs.fy
and subjobs.fp = jobs.fp
and subjobs.jobsid = jobs.jobsid
inner join
dbo.expense as ex
on
ex.fy = jobs.fy
and ex.fp = jobs.fp
and ex.jobsid = jobs.jobsid
and ex.subjobs = subjobs.subjobs
where
jobs.fy = '2012'
and jobs.fp = '1'
order by
jobs.jobsid

I know I should be expecting 900 rows, the first join gives me 3500, if there is one subjob, I get one line. If there are two subjobs I'm getting 4 lines, two for each subjob. If there are three subjobs I get 9 lines, 3 for each subjob.

What's going on? I'm trying to wrap my brain around this one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 10:36:09

i think you need to join on subjobsid as well
if you can post detail data we can specify correct solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vogel515
Starting Member

5 Posts

Posted - 2011-12-09 : 10:44:37

quote:
Originally posted by visakh16

i think you need to join on subjobsid as well
if you can post detail data we can specify correct solution

Ahh, sorry that wasn't clear, where the expenses are being joined, they are being joined on the subjobsid.

So there are two large inner joins, the first:

jobs.FY->subjobs.FY
jobs.FP->subjobs.FP
jobs.Job->subjobs.Job

This links jobs to subjobs, it's at this point where I start to see the multiplication of rows.

The second join is

jobs.fy->ex.fy
jobs.fp->ex.fp
jobs.job->ex.job
subjobs.subjob->ex.subjob

There are no additional rows added here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 10:49:27

ok so sounds like what you expect is one row per job, in that case you need t aggregate on jobsid and then apply some kind of aggregation over subjobs to make them one per job.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vogel515
Starting Member

5 Posts

Posted - 2011-12-09 : 11:04:55

Goal is to have it aggregated on one line per subjob. There are 900 subjobs.

And I think I just answered my own question... these tables were created from documents which I broke up for a warehouse, I wanted to be able to recreate the original document for error checking. Which means when I broke up the document in its most raw form there are job lines for each subjob. IE there are multiple duplicate job lines. A distinct jobs query fixes it.

Tough start on the forum. Thanks for the quick responses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources