Here is the query:

DECLARE @zip VARCHAR(20)
SET @zip = '90210'

--Get Partial Zip Matches
SELECT DISTINCT p.* FROM dbo.[Plan] p
INNER JOIN dbo.[PlanCounty] pc ON p.PlanID=pc.PlanID
INNER JOIN dbo.[PlanCountyPartialZip] ppz ON pc.PlanCountyID=ppz.PlanCountyID
INNER JOIN dbo.[Zip] z ON ppz.ZipID=z.ZipID
WHERE z.Zipcode=@zip
UNION ALL
--Get Non-Partial Zip Matches
SELECT DISTINCT p.* FROM dbo.[Plan] p
INNER JOIN dbo.[PlanCounty] pc ON p.PlanID=pc.PlanID
INNER JOIN dbo.[County] c ON pc.CountyID=c.CountyID
INNER JOIN dbo.[CountyZip] cz ON c.CountyID=cz.CountyID
INNER JOIN dbo.[Zip] z ON cz.ZipID=z.ZipID
WHERE (pc.PlanCountyID NOT IN (SELECT DISTINCT PlanCountyID FROM PlanCountyPartialZip))
AND (z.Zipcode=@zip)

I would like to convert this using HQL or Criteria but I don't know where to get started.

Can anyone help me with an approach that would work best?

Thanks,

jensenkd

jensenkd,
basiclly HQL do not allow Union, all other conditions could be set in the HBM files
So assuming in here that you have all your tables properly linked to objects throw hibernate, here is what you should do