3 table join - need to return single record

[ccampbell]

Hello,
I have 3 tables

Table A - Parent Table
Table B - Child of A with a 1...n relationship
Table C - Child of A with a 1...n relationship

When I do a simple select
SELECT *
FROM table A x left outer JOIN
table B y on x.id = y.id left outer JOIN
table C z on x.id = z.id

It will return multiple records because of the 1...n relationship between the parent and child. I need to be able to return the max record for each child table so I only get 1 unique record for each parent record? Any help would be greatly appreciated

 

[gmmastros]

>> I need to be able to return the max record for each child table

How do you define 'Max Record'?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ccampbell]

Sorry, max record should be defined as the record with the most current date in each of the child tables. So something like select max(update_date) from table B, etc.

 

[kaht]

SELECT [!]top 1[/!] *
FROM    table A x left outer JOIN
table B y on x.id = y.id left outer JOIN
table C z on x.id = z.id
[!]order by y.maxCriteriaForTableB desc, z.maxCriteriaForTableC desc[/!]

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[ccampbell]

Thanks kaht. That is currently what I have, but table B and table C have the capability to have multiple rows for each parent record. I need to return every row in the parent table (table A) and then a single element in table B based on the most current row (determined by date column in the table), and a single element in table C based on the most current row (also determined by date column in the table). Does that make sense? I am thinking that I need subselect statements in my select statement, but I don't know how to structure it for sure.

 

[SQLDenis]

select t1.*
from tablA t1
left join
(select id,max(date) as MaxDate
from tableB
group by id) t2 on t1.date =t2.Maxdate
and t1.id =t2.id
left join
(select id,max(date) as MaxDate
from tableC
group by id) t3
on t1.date =t3.Maxdate
and t1.id =t3.id

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Subselects would probably work. As an alternative, here is a derived table method that should also work. I'm going to build this in stages so you can see how it works.

First, let's identify the max update_date for each ID in TableC.

[COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]max[/color](Update_date) [COLOR=blue]As[/color] UpdateDate
[COLOR=blue]From[/color]   TableC
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id

When you copy/paste this to Query Analyzer, you'll see each id and whatever the max(update_date) is. Now, let's take this query and make it a derived table so that you can see how to get all of the details from TableC for the max date.

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   TableC
    [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
      [!]Select Id, max(Update_date) As UpdateDate
      From   TableC
      Group By Id[/!]
      ) [COLOR=blue]As[/color] A
      [COLOR=blue]On[/color] Tablec.Id = A.Id
      And TableC.update_date = A.UpdateDate
) [COLOR=blue]As[/color] TC
[COLOR=blue]On[/color] TableA.Id = TC.Id

Notice the part in red is the original query, but now it is considered a derived table. Derived tables must be surrounded with parenthesis and also have an alias (As TC). You can then treat this derived table as though it were a regular table. You should also notice that each ID has exactly 1 row in the output, and all of the data in the table is returned, but only where the data matches the max update_date.

Now, we can make this a derived table and join it to TableA so that we can get the data out of that table too. It would look like this...

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   TableA
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] *
         [COLOR=blue]From[/color]   TableC
                [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
                  [COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]max[/color](Update_date) [COLOR=blue]As[/color] UpdateDate
                  [COLOR=blue]From[/color]   TableC
                  [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id
                  ) [COLOR=blue]As[/color] A
                  [COLOR=blue]On[/color] Tablec.Id = A.Id
                  And TableC.update_date = A.UpdateDate
         ) [COLOR=blue]As[/color] TC
         [COLOR=blue]On[/color] TableA.Id = TC.Id
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] *
         [COLOR=blue]From[/color]   TableB
                [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
                  [COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]max[/color](Update_date) [COLOR=blue]As[/color] UpdateDate
                  [COLOR=blue]From[/color]   TableB
                  [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id
                  ) [COLOR=blue]As[/color] B
                  [COLOR=blue]On[/color] Tablec.Id = B.Id
                  And TableB.update_date = B.UpdateDate
         ) [COLOR=blue]As[/color] TB
         [COLOR=blue]On[/color] TableA.Id = TB.Id

Notice that there is code here for TableB also, but it is essentially the same as TableC.

Does this make sense?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ccampbell]

This helps me with the sub select structure, thanks a lot. When I break the query down and run it piece by piece, I notice the the inner most select statement

Select Id, max(Update_date) As UpdateDate
From TableC
Group By Id

returns the single record like I am anticipating. When I add the next layer (the select that surrounds that) I get both records back rather than the max record that I need.

Select *
From TableC
Inner Join (
Select Id_number, max(Update_date) As UpdateDate
From TableC
Group By Id_number
) As A
On TableC.Id_number = A.Id_number
) As TC

I tried just sticking with the first select and joining to to my parent table but the first select does not return the column that I need, I modified it to the following

Select *
From TableA
Left Join (
Select status, ID_NUMBER, max(data_effective_date) As UpdateDate
From TableC
Group By ID_NUMBER
) As TC
On TableA.CARRIER_ID_NUMBER = TC.CARRIER_ID_NUMBER

But I get an error

Column 'status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. The status column is all that I need from that TableC but I need it based on the max(data_effective_date) in that table. Hope this makes sense. Thanks so much for your posts, I am continuing to play with it to see what I can come up with.

 

[gmmastros]

You need to join on BOTH columns. The Id_Number column AND the date column.

Look at my example again.

       Left Join (
         Select *
         From   TableC
                Inner Join (
                  Select Id, max(Update_date) As UpdateDate
                  From   TableC
                  Group By Id
                  ) As A
                  [blue]On Tablec.Id = A.Id
                  [!]And[/!] TableC.update_date = A.UpdateDate[/blue]
         ) As TC
         On TableA.Id = TC.Id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ccampbell]

When I take your query and paste it into SQL analyzer I get the following 3 errors

Msg 8156, Level 16, State 1, Line 1
The column 'Id' was specified multiple times for 'TB'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TableB.Id" could not be bound.
Msg 8156, Level 16, State 1, Line 1
The column 'Id' was specified multiple times for 'TC'.

Can you get yours to build and run?

 

[ccampbell]

Thanks, I was able to get it and it looks like it is working correctly. I need to do further testing on it, but it appears now that it is working. I had to modify the query a bit for my purposes but finally got it. Thanks again.