Many To One Join Statement Help

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

Many To One Join Statement Help

Author

Topic

jake929
Starting Member

5 Posts

Posted - 2011-12-28 : 16:27:47

I have two tables that I need to join.

TABLE1 is the primary table and I need to join TABLE2.
TABLE2 can have multiple records that relate to TABLE1 and can sometimes not have any at all.

I need my join statement to pull only one record regardless if TABLE2 has many or no associated records.

This script is being used inside a Crystal Report XI and I have not had much luck using the MAX or Group Function.

Thanks
jake

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-28 : 16:36:05

LEFT JOIN

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jake929
Starting Member

5 Posts

Posted - 2011-12-28 : 16:50:07

Left join yeilds multiple records because TABLE2 has multiple records.

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-12-28 : 17:04:56

You need to have a action plan if there's more than one entry in table 2. You need to determine if you want to min max or first last record values and then code appropriately. and yes LEFT join

Likes to run, hates the runs!

jake929
Starting Member

5 Posts

Posted - 2011-12-28 : 17:16:19

How would I code a maximum then? Say the field is:
TABLE2.AMOUNT

jake929
Starting Member

5 Posts

Posted - 2011-12-28 : 17:51:57

I got the Max function to work but when I try and join another table I get the error:
"Column fun.funded is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

select
gen.file_id,
fun.funded,
MAX(gen.loan_amt) as amount
from gen
left join fun on fun.file_id = gen.file_id

group by gen.file_id

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-28 : 19:21:53

In most cases, each column in the select list should be either contained within an aggregate function (MAX, MIN, AVG etc.), or must be included in the group by list. So your choices are:

1. Apply an aggregate function to fun.funded

select
   gen.file_id,
   MAX(fun.funded) as FundedMax,
   MAX(gen.loan_amt) as amount
from 
   gen
   left join fun on fun.file_id = gen.file_id
group by 
   gen.file_id

2. Include fun.funded in the group by clause

select
   gen.file_id,
   fun.funded,
   MAX(gen.loan_amt) as amount
from 
   gen
   left join fun on fun.file_id = gen.file_id
group by 
   gen.file_id,
   fun.funded

The results would of course be different depending on which option you choose.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources