hard to figure out select syntax

[impulse24]

Hi,

I am getting stumped on how to do this query.

I have two tables:

Platter
Header

Platter has:
ImgPtr|PlatterId

Header has:
DocumentIndex|ImgPtr|Pagenum

I need a query that only returns records from the header that are complete in platter. The ImgPtr is linked from Platter to Header.

Example:
Platter
SXST.1|BBD1
STSU.1|BBD1
TVDM.1|BBD3
TXRZ.1|BBD8

Header:
12345|SXST.1|1
12345|SXSU.1|2
12345|LTX1.1|4
12456|TVDM.1|1
12456|TXRZ.1|2

So in the query, I only want to see 12456. 12345 should not be displayed because one of it's imgptr's(LTX1.1) does not exist in the platter table. I think I have to use max and min for the pagenum. If a documentindex has an imageptr not in the plattertable, than I don't want to see the documentindex. Since a document index can have a few imgptr's that exist in the platter table, and a few that don't I don't know how to do the right query.

Hope this makes sense. Please help. Thanks.

 

[bperry]

What exactly to you mean when you say
"I only want to see 12456"....

(A) Do you only need to see one 'summary row' for 12456, or do you want to see all (in this case 2) rows for 12456.
(B) In the result, do you need to show all columsn from Header? What about PlatterId from Platter?

If you provide a few more details like this, it will be easier for someone to help you out.

 

[bperry]

This will work in MS SQL Server.
It returns the DocInd# from Header where all records are completed in Platter, and the count of those completed records.

SELECT dt1.DocInd,
dt2.DesiredCount as CompletedRecords
FROM
(
Select h.DocInd, Count(*) as ActualCount
from Header h INNER JOIN Platter p
ON h.ImgPtr = p.ImgPtr
Group By DocInd
) as dt1

INNER JOIN

(
Select h.DocInd, Count(*) as DesiredCount
from Header h LEFT OUTER JOIN Platter p
ON h.ImgPtr = p.ImgPtr
Group By DocInd
) as dt2

ON dt1.DocInd = dt2.DocInd
where DesiredCount = ActualCount

 

[bperry]

Here's a more compact version of my earlier solution:

Select h.DocInd,
Count(*) as DesiredCount,
Count(p.ImgPtr) as ActualCount
from Header h LEFT OUTER JOIN Platter p
ON h.ImgPtr = p.ImgPtr
Group By DocInd
Having Count(*) = Count(p.ImgPtr)

 

[bperry]

Here's another way, which is probably better than the approach I posted earlier. Both versions of this report only 'completed' records. The first lists every record from Header for a given DocInd, while the second just lists one summary record per DocInd.
--------------------------------------
-- Report All Records for each DocInd
--------------------------------------
Select DocInd, ImgPtr
From Header h1 Where NOT EXISTS
(Select * from Header h2
LEFT OUTER JOIN Platter p
ON h2.ImgPtr = p.ImgPtr
Where h2.DocInd = h1.DocInd
and p.ImgPtr IS NULL
)
-------------------------------------
-- Report one Record for each DocInd
-------------------------------------
Select DocInd, Count(*) as 'Completed Records'
From Header h1 Where NOT EXISTS
(Select * from Header h2
LEFT OUTER JOIN Platter p
ON h2.ImgPtr = p.ImgPtr
Where h2.DocInd = h1.DocInd
and p.ImgPtr IS NULL
)
Group by DocInd
Order by DocInd
-----------------------------