complex query with min, and max 1

[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.

 

[stsuing]

Let me know if this works

SELECT hsxxx.Documentindex
FROM (SELECT Hs.Documentindex, COUNT(*) AS hscnt
FROM Header hs INNER JOIN
Platter ON Hs.Imgptr = Platter.imgptr
GROUP BY Hs.Documentindex) hsxxx INNER JOIN
(SELECT Hrt.Documentindex, COUNT(*) AS hrcnt
FROM Header Hrt
GROUP BY Hrt.Documentindex) hrxxx ON
hsxxx.Documentindex = hrxxx.Documentindex AND
hsxxx.hscnt = hrxxx.hrcnt

 

[taasarge]

This is os/390 DB2 systax but here is my try...

select Hs.Documentindex
FROM Header Hs
where Hs.Documentindex NOT IN
(select Hs.Documentindex
from Header h1
where H1.Imgptr NOT IN
(select Imgptr
from platter))
I may be off a bit but the idea is to create a list in the inner select of documents that do not exist on the platter table. Then the outer select will choose (hopefully) the correct document.

 

[Andel]

You can also do this:

select distinct DocumentIndex from header h join platter p
on h.ImgPtr = p.ImgPtr where DocumentIndex not in
(select DocumentIndex from header h left join platter p
on h.ImgPtr = p.ImgPtr where p.ImgPtr is null)

Andel
andel@barroga.net

http://andyb.8m.com

 

[impulse24]

Thanks for all the replies. Stsuing's response worked out perfectly. However I have one new twist to throw in. Now that I have only the documentindex's that I want, within the same query can I query another table for its matching results.
Below is the syntax that worked:

SELECT hsxxx.Documentindex
FROM (SELECT Hs.Documentindex, COUNT(*) AS hscnt
FROM Header hs INNER JOIN
Platter ON Hs.Imgptr = Platter.imgptr
GROUP BY Hs.Documentindex) hsxxx INNER JOIN
(SELECT Hrt.Documentindex, COUNT(*) AS hrcnt
FROM Header Hrt
GROUP BY Hrt.Documentindex) hrxxx ON
hsxxx.Documentindex = hrxxx.Documentindex AND
hsxxx.hscnt = hrxxx.hrcnt

I want to use each of the hsxxx.documentindex to query from indextable and return all the fields for the document index. I tried the below syntax and got an error. Please help.

select *
from( SELECT hsxxx.Documentindex
FROM (SELECT Hs.Documentindex, COUNT(*) AS hscnt
FROM Header hs INNER JOIN
Platter ON Hs.Imgptr = Platter.imgptr
GROUP BY Hs.Documentindex) hsxxx INNER JOIN
(SELECT Hrt.Documentindex, COUNT(*) AS hrcnt
FROM Header Hrt
GROUP BY Hrt.Documentindex) hrxxx ON
hsxxx.Documentindex = hrxxx.Documentindex AND
hsxxx.hscnt = hrxxx.hrcnt)
indextable where indextable.documentindex = hsxxx.documentindex

Any help would be greatly appreciated

 

[stsuing]

SELECT *
FROM (SELECT hsxxx.Documentindex
FROM (SELECT Hs.Documentindex, COUNT(*)
AS hscnt
FROM Header hs INNER JOIN
Platter ON Hs.Imgptr = Platter.imgptr
GROUP BY Hs.Documentindex) hsxxx INNER JOIN
(SELECT Hrt.Documentindex, COUNT(*)
AS hrcnt
FROM Header Hrt
GROUP BY Hrt.Documentindex) hrxxx ON
hsxxx.Documentindex = hrxxx.Documentindex AND
hsxxx.hscnt = hrxxx.hrcnt) docI1 INNER JOIN
IndexTable ON
DocI1.documentindex = IndexTable.Documentindex

 

[impulse24]

This worked out perfectly. Thanks for all your help. Now I just have one more request. I tried to use DTS to export the results of the query, and it seems like it just keeps exporting everything in both tables in an endless loop. Is there something I should do differently to export the query to a file? Please help.