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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-05 : 08:16:20
|
| Doug writes "Here Goes:Group Table PK = Group_IdCompany Table PK = Company_IdSize Table PK = Size_IdGroup record contains a Company_Id fieldSize record contains a Company_Id fieldA Group can only belong to one Company, but a Company can have several Groups.A Size record can only have one Company reference, but a Company can have several Size RecordsOur goal: Select all Group records, bring in any available Company records, and bring in only the highest Size record based on its End_Date field.The Problem: When we do normal joins (typically Left), we get multiple records coming back as a result of the one-to-many relationship between Company and Size. We need to select specific fields from each table, and have tried the Max function, Top X, etc. Help!BTW, SQL Server V7, Windows NT 4" |
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-05 : 11:57:46
|
assuming each Group always has a Company, we can use INNER JOINassuming Company may not have any Size, we will use LEFT JOINjoin the Company to the Size row with the max End_Date for that Company, using a correlated subselectselect G.Group_id, G.otherstuff , C.Company_id, C.otherstuff , S.Size_id, S.otherstuff from GroupTable Ginner join CompanyTable C on G.Company_id = C.Company_idleft join SizeTable S on C.Company_id = S.Company_id where S.End_Date = ( select max(End_Date) from SizeTable where Company_id = C.Company_id ) if Size is missing for a given Company, those columns will be nullp.s. "Group" as a table name might get you in trouble -- it's a reserved word  rudyhttp://rudy.ca/ |
 |
|
|
|
|
|
|
|
One-To-Many relationship causing multiple returns