crazy super complex select query

[impulse24]

Below is a query I perform to unique documentindex values. 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 works to get the unique documentindex

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

 

[bperry]

Perhaps try this instead?

SELECT hsxxx.Documentindex, indextable.*
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
ANDhsxxx.hscnt = hrxxx.hrcnt

INNER JOIN indextable
ON indextable.documentindex = hsxxx.documentindex

brian perry