join two tables and only return the latest data for the child table

I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:

select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?

I tried to create a function to get the latest sentence for a chargeID like the following:

select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?

thanks,

if you are on 2005, you can use row_number(), as follows:

select * from(
select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
) t
where rn=1
|||

Your suggestion works, thanks.

But when I modified the query to:

select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

where rn=1

the query fails with the error "Invalid column name 'rn'.", why?

|||Because Where applies to the source fields, not the result fields, so your alias "as rn" hasn't been applied yet.

That's why Akuz nested it inside another Select. The outer Select/Where can use the alias.

Join stuck...

Hi guys,

I'm stuck with this one... I have two tables....a parent and a child table...(parent*-1child).

What I am trying to do is retrieve all the parent rows where the child must contain two different values in one of its columns that is not the primary key.

Ok so Parent table structure:

ParentID

and child table structure:

ChildID (PK)

ParentID

Col3

So return all records from Parent where the Parent can have two different values for Col3. Or even three different values.

Try this query:

SELECT * FROM Parent

WHERE ParentID IN

(SELECT ParentID FROM Child

WHERE Col3 = 'something' OR Col3 = 'something else')

Or

SELECT * FROM Parent INNER JOIN Child ON Parent.ParentID = Child.ParentID

WHERE Child.Col3 = 'something' OR Child.Col3 = 'something else'

I hope this answers your question.

Best regards,

Sami Samir

|||

If I understand you correctly, you want the parent records where there are 2 or more child records with different values. If that is correct, perhaps something like this will help (for SQL 2005):

Code Snippet

DECLARE @.Parent table
( ParentID int )

DECLARE @.Child table
( ChildID int,
ParentID int,
SomeValue int
)

SET NOCOUNT ON

INSERT INTO @.Parent Values ( 1 )
INSERT INTO @.Parent Values ( 2 )
INSERT INTO @.Parent Values ( 3 )
INSERT INTO @.Parent Values ( 4 )
INSERT INTO @.Parent Values ( 5 )
INSERT INTO @.Child Values ( 1, 1, 1 )
INSERT INTO @.Child Values ( 2, 1, 2 )
INSERT INTO @.Child Values ( 3, 2, 1 )
INSERT INTO @.Child Values ( 4, 2, 2 )
INSERT INTO @.Child Values ( 5, 2, 3 )
INSERT INTO @.Child Values ( 6, 3, 1 )
INSERT INTO @.Child Values ( 7, 4, 1 )
INSERT INTO @.Child Values ( 8, 4, 1 )
INSERT INTO @.Child Values ( 8, 4, 2 )
INSERT INTO @.Child Values ( 7, 5, 1 )
INSERT INTO @.Child Values ( 8, 5, 1 )

SELECT ParentID
FROM @.Parent

EXCEPT

-- Remove singletons
SELECT ParentID
FROM @.Child
GROUP BY ParentID
HAVING count(1) = 1

EXCEPT

-- Remove Two Records, same Parent and Value
SELECT ParentID
FROM @.Child
GROUP BY ParentID, SomeValue
HAVING ( count(1) = 2
AND ParentID NOT IN (SELECT ParentID
FROM @.Child
GROUP BY ParentID
HAVING count(1) > 2
)
)

This 'feels' a bit awkward. Perhaps someone will have a better idea.

|||

Building on Arnie's Test Data. I think the query you want is:

Code Snippet

DECLARE @.Parent table

( ParentID int )

DECLARE @.Child table

( ChildID int,

ParentID int,

SomeValue int

)

SET NOCOUNT ON

INSERT INTO @.Parent Values ( 1 )

INSERT INTO @.Parent Values ( 2 )

INSERT INTO @.Parent Values ( 3 )

INSERT INTO @.Parent Values ( 4 )

INSERT INTO @.Parent Values ( 5 )

INSERT INTO @.Child Values ( 1, 1, 1 )

INSERT INTO @.Child Values ( 2, 1, 2 )

INSERT INTO @.Child Values ( 3, 2, 1 )

INSERT INTO @.Child Values ( 4, 2, 2 )

INSERT INTO @.Child Values ( 5, 2, 3 )

INSERT INTO @.Child Values ( 6, 3, 1 )

INSERT INTO @.Child Values ( 7, 4, 1 )

INSERT INTO @.Child Values ( 8, 4, 1 )

INSERT INTO @.Child Values ( 8, 4, 2 )

INSERT INTO @.Child Values ( 7, 5, 1 )

INSERT INTO @.Child Values ( 8, 5, 1 )

SELECT *

FROM @.PARENT

WHERE ParentID IN (

SELECT ParentID

FROM @.CHILD

GROUP BY ParentID

HAVING (COUNT(DISTINCT SomeValue) >1)

)

The marked inner query returns a list those parents IDs in the child table with more than one distinct value in SomeValue.

|||

Dhericean,

Thanks, that is much better. It was late and my thinking was not working properly. (Stuck in a bad WHILE loop, I think...)

Actually, if there is a defined PK-FK relationship between the tables, the outer query is not required. The solution then becomes:

SELECT ParentID
FROM @.CHILD
GROUP BY ParentID
HAVING ( count( DISTINCT ValueCol ) > 1 )

|||

--Hmmm...ok let me explain again....

DECLARE @.Parent table
( ParentID int )

DECLARE @.Child table
( ChildID int, PrimaryKey
ParentID int,
SomeValue int
)

INSERT INTO @.Parent Values ( 1 )
INSERT INTO @.Parent Values ( 2 )
INSERT INTO @.Parent Values ( 3 )
INSERT INTO @.Parent Values ( 4 )

INSERT INTO @.Parent Values ( 5 )
INSERT INTO @.Child Values ( 1, 1, 1 )
INSERT INTO @.Child Values ( 2, 1, 2 )
INSERT INTO @.Child Values ( 3, 2, 1 )
INSERT INTO @.Child Values ( 4, 2, 1 )
INSERT INTO @.Child Values ( 5, 2, 2 )
INSERT INTO @.Child Values ( 6, 3, 1 )
INSERT INTO @.Child Values ( 7, 3, 1 )
INSERT INTO @.Child Values ( 8, 3, 1 )
INSERT INTO @.Child Values ( 9, 4, 1 )
INSERT INTO @.Child Values ( 10, 4, 1 )
INSERT INTO @.Child Values ( 11, 4, 2 )

INSERT INTO @.Child Values ( 12, 5, 2 )

INSERT INTO @.Child Values ( 13, 5, 2 )

INSERT INTO @.Child Values ( 14, 5, 2 )

--Now what I want to retrieve is all the parents that have BOTH 1 and 2 in its child values. .ie. The parents 1,2 and 4 will be returned but not 3 and 5.

|||

So, I don't see the problem.

This query returns 1,2,4

Actually, if there is a defined PK-FK relationship between the tables, the outer query is not required. The solution then becomes:

SELECT ParentID
FROM @.CHILD
GROUP BY ParentID
HAVING ( count( DISTINCT ValueCol ) > 1 )

|||

Thanks...but is there another way of doing this. Reason being is that sometimes you only have the value for one child. Eg. Return parents that have only 1 in its child value. That means 1, 2, 3 and 4 will be returned.

Or return parents that have 2 in its child value which will return 1,2,3,4 and 5

The combination can be anything for child and can be one or more differnt child value combinations.

The front end simply allows the user to select a parent value and then select child value/s that belongs to the selected parent. Later on the user can select a different parent value as long as it contains the existing child value/s selected.

Thanks in advanced.

|||

Basically a child can have may parents...so I need a list of all the parents that have the same children passed.

JOIN Process Order and Performance Comparisons

Hi all,
A common SQL that I do is joining parent and child tables together (1-M
relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
number of rows.
Q1) Compare the two statements (that give the same result) below, from a
programming point of view, which one is more efficient?
Statement 1
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
AND IvoItm.ProductType = 1 --This line is processed in WHERE.
Statement 2
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
AND IvoItm.ProductType = 1 --This line is processed in JOIN.
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
This is something which I have been wondering for quite sometime. After
reading MSDN article "Join Fundamentals" stating, it says the JOIN statement
s
are processed first.
Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
process the JOIN?
Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm,
before doing the JOINS? Therefore improving performance, as the amount of
data to join is reduced in the IvoItm?
Q4) Using the same analogy in Q3, would there be performance gain if I
rewrite the statement using sub-query to do the filtering 1st?
SELECT *
FROM Invoice Ivo
INNER JOIN (
SELECT *
FROM InvoiceItem
WHERE ProductType = 1 --This line is processed in sub-query.
) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
Q5) And would above be efficient than using the Statement 1 and 2?Answers inline:
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:3E92F8CB-3E8A-4A4C-A0D9-71B707658D1A@.microsoft.com...
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
These two statements are probably going to perform equivalently, as they are
mathematically equivalent. If this was an outer join, then it will make a
difference. First thing to do is to check the plan using Query analyzer. It
should be the exact same plan.
Logically, all of the JOIN operators will be dealt with, building the set
with Invoice.* + InvoiceLineItem, eliminating rows where the join criteria
fails. Then for every row in the output you apply the where clause.
However, the optimizer can can reorganize the query to make it execute
better as long as the same results would be the same.
For the rest of your questions, try looking at the plan first. It may answer
the questions for you as the answers may be different based on the number of
rows in each table.

> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN
> statements
> are processed first.
>
This is true logically, but it is not required if the results are the same

> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in
> IvoItm,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?|||As noted by Louis, for Inner Joins it doesn't matter whether you specify
the predicates in the WHERE clause or in the JOIN ON clause. For Outer
Joins the meaning is different.
What the query optimizer will do, is analyse which indexes your tables
have and whether they can be sed or scanned in order to reduce the
I/O needed to retrieve the actual data. Then it will do an access path
analysis to see in which order the joins would be fastest. If there is
an appropriate index, then physically, the (partial) filtering will
occur before the join.
Suppose you have a clustered index on Invoice(Date). Then you will
probably see a clustered index s on table Invoice, regardless whether
you used syntax 1 or 2. BTW: the only way to really tell is check the
query plan.
HTH,
Gert-Jan
HardKhor wrote:
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN stateme
nts
> are processed first.
> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm
,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?

Join Problem..........

Hi,
I need help. I have one Parent table P1 and a Child Table C1. I have 3 records in table P1 and 9 records in C1 (3 records for each records of P1).

When I am doing the inner join of these tables i am getting 9 records, where as actually I want only 3 records. I need all 3 rows from P1 and one row each from the C1 against the corresponding rows of P1. Single row from C1 will come from the criteria based on the Date column of the C1 table. Like the row that will be selected from the table C1 for the row from tbale P1 will have the MAX(DATE) value among all the rows in it C1).

By inner join i am able to extract all the 3 rows where as i need only the row that contains the MAX(DATE).

Kindly help me in this regard.

Thanks,
Rahul Jhaselect P1.foo
, P1.bar
, M.qux
, M.date
from P1
inner
join C1 as M
on M.flim = P1.flam
and M.date =
( select max(date)
from C1
where flim = P1.flam )|||Thanks. :-)

This Will Work. Donno y this din click in my mind.

Thnaks Once Again

Rahul Jha