I want to create a view that uses these tables tblPoint,
tblInspectionPoint, tblInspectionRound (
http://fwhome.hopto.org/~arezafar/schema.jpg ) and lists all the
inspectionRoundIDs and the pointIDs that do not have a corresponding
inspectionPointID. Example with sample data
http://fwhome.hopto.org/~arezafar/sample.jpg

Basicly There are Rounds, for each Round there are couple of Points.
Each time an inspection is started a new row is added to
tblInspectionRound and for each Point inspected (belonging to the
Round being inspected) we add a row to tblInspectionPoint. So I want a
list of inspectionRoundIDs and the pointIDs which are NOT inspected
(so they do not have a row in tblInspectionPoint with that
inspectionRoundID).

Thanks,
Ali

---

Request for Question Clarification by lazerfx-ga on 20 Oct 2005 06:11 PDT

Hi,
Unfortunately, the linked images are not linking to images.

When you say you want pointID's that are not inspected, are you
looking for items in tblInspectionRound that don't have a
corresponding ID in tblInspectionPoint?

Thanks in advance,

LazerFX-ga

Hello Ali,
Try the following query and let me know:


select a.pointid, b.inspectionround inspectionroundid
  from tblpoint a,
       tblinspectionround b
where a.roundid = b.roundid(+)
  and not exists
      (select 1
        from tblinspectionpoint c
      where c.pointid = a.pointid)
UNION
SELECT a.pointid ,b.inspectionround inspectionroundid
  from tblinspectionround b,
       tblpoint a
 where b.roundid = a.roundid (+)
  and not exists
      (select 1
        from tblinspectionpoint c
       where c.inspectionroundid = b.inspectionround
        and c.pointid = a.pointid)

Thanks monic_b, this was the answer I was looking for.