November 10, 2010 at 4:28 pm
I'm sure this must be answered somewhere in this forum but I couldn't find it.
I have a primary table that links to two other tables that are a one to many relationship with the primary table but they have no relationship with each other.
The primary table has one row, the first 1:m has 2 rows, and the second 1:m has three rows, they each link directly with the primary table.
When I create my query, I get 6 rows returned but I only expect/want 3.
I get the result like this:
DaveDonnaMustang
DaveDonnaMalibu
DaveDonnaJeep
DaveKevinMustang
DaveKevinMalibu
DaveKevinJeep
What I want is:
Dave Donna Mustang
Dave Kevin Malibu
Dave Jeep
Below is the query. Please explain what is happening? And how I could get the result I want.
declare @FamilyMember table
(
FamilyId int,
FamilyName varchar(100)
)
Insert into @FamilyMember select 1,'Dave'
select * from @FamilyMember
declare @Kids table
(
FamilyId int,
ChildId int,
ChildName varchar(100)
)
Insert into @Kids select 1, 1, 'Donna'
Insert into @Kids select 1, 2, 'Kevin'
select * from @Kids
declare @Cars table
(
FamilyId int,
CarId int,
CarName varchar(100)
)
Insert into @Cars select 1,1,'Mustang'
Insert into @Cars select 1,2,'Malibu'
Insert into @Cars select 1,3,'Jeep'
select * from @Cars
select FamilyName, ChildName, CarName
from @FamilyMember f
join @Kids k on f.FamilyId = k.FamilyId
join @Cars c on f.FamilyId = c.FamilyId
November 10, 2010 at 4:45 pm
Sorry, doublepost.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 4:45 pm
The short version is you've got your relationships all twisted up. You're not chaining ChildID into the cars table, you're chaining familyID, which is causing you to link 2*3 times (2 rows in Child with FamilyID 1, and 3 rows in Car with FamilyID 1)
What you're looking to do is something more like this:
declare @FamilyMember table
(FamilyId int IDENTITY(1,1),
FamilyName varchar(100))
Insert into @FamilyMember (FamilyName) select 'Dave'
declare @Kids table
(ChildId int IDENTITY( 1,1),
FamilyId int,
ChildName varchar(100) )
Insert into @Kids (FamilyID, ChildName) select 1, 'Donna'
Insert into @Kids (FamilyID, ChildName) select 1, 'Kevin'
Insert into @Kids (FamilyID, ChildName) select 1, 'FamilyCar'
declare @Cars table
(CarId int IDENTITY( 1, 1),
FamilyID INT,
ChildID int,
CarName varchar(100) )
Insert into @Cars (FamilyID, ChildID, CarName) select 1, 1,'Mustang'
Insert into @Cars (FamilyID, ChildID, CarName) select 1, 2,'Malibu'
Insert into @Cars (FamilyID, ChildID, CarName) select 1, 3,'Jeep'
select
f.FamilyName,
k.ChildName,
c.CarName
from
@FamilyMember f
join
@Kids k
onf.FamilyId = k.FamilyId
join
@Cars c
-- Note the two table connection here
onf.FamilyId = c.FamilyId
AND k.childID = c.ChildID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 4:58 pm
Thanks for your reply Craig, but your code doesn't even compile.
Editor: response removed.
November 10, 2010 at 5:13 pm
Ed Srdoc (11/10/2010)
Thanks for your reply Craig, but your code doesn't even compile.Maybe I could get somebody that's not such a dick to answer my question!
A) Code compiled just fine on my 2k5 box, should work in 2k8.
B) Not sure what I said that offended other than the relationships were twisted up. And explained why. Either way, Good Luck.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 5:14 pm
His code works for me. So does his solution. Wondering why you had to call him a name, especially when it looks like he was trying to help you.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 10, 2010 at 7:13 pm
My apologies.
I may have been put off by your initial response that ended in, 'We're not here to do your homework'. Maybe that wasn't directed at me. Maybe it was?
Anyway, your code does parse. I hadn't realized you changed the structure of one of the tables.
My problem is very simple, all I want to do is report the children and cars of a familymember.
A familymember might have sereveral children, maybe none and the same with cars.
There's no relationship between children and cars. In this design or in real life, children don't own cars. So putting ChildId on the Cars table was a bit of a head scratcher.
This issue I'm having is only a subset of a much larger design were there are several other 1:m tables involved. Tossing around foriegn keys onto a table that has no relationship, is what would make it a twisted design.
There's no problem with the design, I'm just looking for a technique and an explaination as to why I get 6 rows returned instead of 3.
Who names their kid 'FamilyCar' anyway?
November 10, 2010 at 8:19 pm
Ed Srdoc (11/10/2010)
I'm just looking for a technique and an explaination as to why I get 6 rows returned instead of 3.
Craig explained this:
which is causing you to link 2*3 times (2 rows in Child with FamilyID 1, and 3 rows in Car with FamilyID 1)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2010 at 8:21 pm
Ed Srdoc (11/10/2010)
My apologies.I may have been put off by your initial response that ended in, 'We're not here to do your homework'. Maybe that wasn't directed at me. Maybe it was?
Anyway, your code does parse. I hadn't realized you changed the structure of one of the tables.
My problem is very simple, all I want to do is report the children and cars of a familymember.
A familymember might have sereveral children, maybe none and the same with cars.
There's no relationship between children and cars. In this design or in real life, children don't own cars. So putting ChildId on the Cars table was a bit of a head scratcher.
This issue I'm having is only a subset of a much larger design were there are several other 1:m tables involved. Tossing around foriegn keys onto a table that has no relationship, is what would make it a twisted design.
There's no problem with the design, I'm just looking for a technique and an explaination as to why I get 6 rows returned instead of 3.
Who names their kid 'FamilyCar' anyway?
What you took as part of Craig's post is his comment in his signature block. You will see that in all his posts.
November 10, 2010 at 9:22 pm
Ed Srdoc (11/10/2010)
My apologies.I may have been put off by your initial response that ended in, 'We're not here to do your homework'. Maybe that wasn't directed at me. Maybe it was?
Problem fixed. That was my sig line. Been getting a bunch of students through here lately expecting something for nothing. It wasn't aimed at you specifically.
Anyway, your code does parse. I hadn't realized you changed the structure of one of the tables.
My problem is very simple, all I want to do is report the children and cars of a familymember.
A familymember might have sereveral children, maybe none and the same with cars.
There's no relationship between children and cars. In this design or in real life, children don't own cars. So putting ChildId on the Cars table was a bit of a head scratcher.
The reason I assumed you were trying to chain the data down was because of this result:
Dave Donna Mustang
Dave Kevin Malibu
Dave Jeep
It implies Dave's family, Donna's car, is the Mustang.
The reason for the implication is that usually you would return two unique result sets otherwise, if they have no direct association. Thus why the following:
This issue I'm having is only a subset of a much larger design were there are several other 1:m tables involved. Tossing around foriegn keys onto a table that has no relationship, is what would make it a twisted design.
There's no problem with the design, I'm just looking for a technique and an explaination as to why I get 6 rows returned instead of 3.
Who names their kid 'FamilyCar' anyway?
The FamilyCar was merely a placeholder for the car's entry so it could have a relation backwards.
So, to confirm, you're looking for an resultset related only familyID, that lists the Family name (Dave) and a row that has a kid and a car, and listing each possible combo only once, with no relation or care to who ends up on which line?
So any of these three possible results would be valid?
Dave Donna Mustang
Dave Kevin Malibu
Dave <implied NULL left as blank> Jeep
Dave Kevin Malibu
Dave Donna Jeep
Dave <implied NULL left as blank> Mustang
Dave Donna Mustang
Dave Kevin Jeep
Dave <implied NULL left as blank> Malibu
And, as an example, if we had more cars in the Dave family, something like this would also be valid:
Dave Donna Mustang
Dave Kevin Jeep
Dave <implied NULL left as blank> Malibu
Dave <implied NULL left as blank> Range Rover
Dave <implied NULL left as blank> Fiero
Dave <implied NULL left as blank> F-150
Making sure I understand your desired results, they are unusual.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 9:28 pm
New post in case of a cross post.
To answer your question more specifically as to what happened with the 6 rows, is this:
select FamilyName, ChildName, CarName
from @FamilyMember f
join @Kids k on f.FamilyId = k.FamilyId
join @Cars c on f.FamilyId = c.FamilyId
Would be better off shown as this:
Kids JOIN Family JOIN Cars. The reason is you end up with 2 kids * 1 FamilyMember * 3 Cars. Because cars and Kids don't link in any way, each kid is repeated for each possible link of car <-> FamilyMember.
I'm trying to puzzle out a way to do what I understood in my last post without prebuilding a temp table template and then trying to do some hack against the quirky update.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 11, 2010 at 12:15 am
Maybe this one will do. The trick is to number the childs and cars in any order you want and do a full outer join on the row numbers.
select
f.*, k.*, c.*
from
@FamilyMember f
left join
(
select
FamilyID, ChildId, ChildName,
row_number() over (partition by FamilyId order by ChildId) rn
from
@Kids
) k on k.FamilyID = f.FamilyID
full outer join
(
select
FamilyID, CarId, CarName,
row_number() over (partition by FamilyId order by CarId) rn
from
@Cars
) c on c.FamilyID = f.FamilyID and c.rn = k.rn
November 11, 2010 at 12:24 am
Correct. Since the two don't relate to each other, the order is insignificant.
I'm just thinking about how a user report would look.
A third 1:m table would be Jobs that this familymember may have held.
Maybe he held one job maybe several, but obviously unrelated to cars or kids.
If he had three jobs, does that mean I'll be getting 2 * 3 * 3 = 18 rows back?
The user just wants to see a list of kids, cars and jobs per family member in columns (gridview) without repeating values.
I don't think there is anything unusual about that.
November 11, 2010 at 12:35 am
Peter Brinkhaus (11/11/2010)
Maybe this one will do. The trick is to number the childs and cars in any order you want and do a full outer join on the row numbers.
select
f.*, k.*, c.*
from
@FamilyMember f
left join
(
select
FamilyID, ChildId, ChildName,
row_number() over (partition by FamilyId order by ChildId) rn
from
@Kids
) k on k.FamilyID = f.FamilyID
full outer join
(
select
FamilyID, CarId, CarName,
row_number() over (partition by FamilyId order by CarId) rn
from
@Cars
) c on c.FamilyID = f.FamilyID and c.rn = k.rn
The code above seemed to work for the samples given, but I messed up with the order of joins. Here's an improved version. I also added some more cases to the test set.
declare @FamilyMember table
(
FamilyId int,
FamilyName varchar(100)
)
Insert into @FamilyMember select 1,'Dave'
Insert into @FamilyMember select 2,'Jack'
Insert into @FamilyMember select 3,'Joan'
select * from @FamilyMember
declare @Kids table
(
FamilyId int,
ChildId int,
ChildName varchar(100)
)
Insert into @Kids select 1, 1, 'Donna'
Insert into @Kids select 1, 2, 'Kevin'
Insert into @Kids select 3, 1, 'Mark'
select * from @Kids
declare @Cars table
(
FamilyId int,
CarId int,
CarName varchar(100)
)
Insert into @Cars select 1,1,'Mustang'
Insert into @Cars select 1,2,'Malibu'
Insert into @Cars select 1,3,'Jeep'
Insert into @Cars select 2,1,'Volvo'
select * from @Cars
select FamilyName, ChildName, CarName
from @FamilyMember f
join @Kids k on f.FamilyId = k.FamilyId
join @Cars c on f.FamilyId = c.FamilyId
select
f.*, kc.*
from
@FamilyMember f
join
(
select
coalesce(k.FamilyId, c.FamilyId) FamilyId,
k.ChildId, k.ChildName,
c.CarId, c.CarName,
coalesce(k.rn, c.rn) rn
from
(
select
FamilyID, ChildId, ChildName,
row_number() over (partition by FamilyId order by ChildId) rn
from
@Kids
) k
full outer join
(
select
FamilyID, CarId, CarName,
row_number() over (partition by FamilyId order by CarId) rn
from
@Cars
) c on c.FamilyID = k.FamilyID and c.rn = k.rn
) kc on kc.FamilyID = f.FamilyID
November 11, 2010 at 12:42 am
Peter, thank you very much!
That will work just fine.
It just seems like a lot of work to do for something that should be much simpler.
Thanks again.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply