Error in a sub-query

[fionama]

Hi all,
I have a group of tables containing containing relating to a maternity episode. Each episode (INCIDENT table) has a number of questions and answers associated with it (MAT_QUESTIONS and MAT_ANSWERS), which are identified using ID fields for each.
I want to extract all those patients who, prior to this episode did not have a caesarian section (question id=72637 and answer id one of 1601167,1601168,1601169,1601170,1601171,1601172), and from those 120 records, extract those that have a natural birth for this current episode (question id 72467, and answer id 1600170...etc)...(there should be 24 of those).
I want to use EXISTS in the SQL. When I run the SQL below however, it brings back the results of the outside query, and does not look at the inside query. The total figure should be ~9 patients, but it's giving me back 120 records. I'd really appreciate help!
Thanks, Fiona

select I.INCIDENTID,P.PAS_NO, P.SURNAME, P.FIRNAME, D.BIRTH_DATE,Q.DESCRIP, QS.QUESTIONTEXT,A.ID,
A.ANSWER
from MAT_INCIDENTS I, MAT_DELIVERY D, PASMAIN P,
MAT_QUESTIONNAIRE Q, MAT_QUESTIONNAIREINCIDENTS QI, MAT_QUESTIONS QS,
MAT_ANSWERS A, MAT_INCIDENTANSWERS IA
WHERE I.INCIDENTID=D.INCIDENTID
AND I.INCIDENTID=QI.INCIDENTID
AND D.INCIDENTID=IA.INCIDENTID
AND D.MOTHER_PAS_NO=P.PAS_NO
AND Q.ID=QI.QUESTIONNAIREID
AND QI.QUESTIONNAIREID=QS.QUESTIONNAIREID
AND QS.ID=A.QUESTIONID
AND A.ID=IA.ANSWERID
AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-02-01 00:00:00'
AND Q.ID IN (221)
AND QS.ID IN (72467)
AND A.ID IN (1600170,1600171,1600172,1601155)
AND EXISTS (SELECT distinct Ib.INCIDENTID,Pb.PAS_NO, Pb.SURNAME, Pb.FIRNAME, Db.BIRTH_DATE,
Qb.DESCRIP, QSb.QUESTIONTEXT,Ab.ID,Ab.ANSWER
from MAT_INCIDENTS Ib, MAT_DELIVERY Db, PASMAIN Pb,
MAT_QUESTIONNAIRE Qb, MAT_QUESTIONNAIREINCIDENTS QIb, MAT_QUESTIONS QSb,
MAT_ANSWERS Ab, MAT_INCIDENTANSWERS IAb
WHERE Ib.INCIDENTID=Db.INCIDENTID
AND Ib.INCIDENTID=QIb.INCIDENTID
AND Db.INCIDENTID=IAb.INCIDENTID
AND Db.MOTHER_PAS_NO=Pb.PAS_NO
AND Qb.ID=QIb.QUESTIONNAIREID
AND QIb.QUESTIONNAIREID=QSb.QUESTIONNAIREID
AND QSb.ID=Ab.QUESTIONID
AND Ab.ID=IAb.ANSWERID
AND Db.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-02-01 00:00:00'
AND Qb.ID =2
AND QSb.ID =72637
AND Ab.ID IN (1601167,1601168,1601169,1601170,1601171,1601172))
ORDER BY P.PAS_NO ASC

 

[SQLBill]

What do you get if you run just the subquery?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLDenis]

because you are using OLD STYLE joins you have probably forgotten a condition. Why don't you use JOINS like we do in this century, not last century, that will make it much easier to debug

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLBill]

BTW-when you do EXISTS or IN, you just need enough information to make the comparison.

What you have, returns too much information. Have you tried changing the SELECT in the subselect to:

SELECT distinct Ib.INCIDENTID from

HINT: When using DISTINCT the combination of every column being returned must be distinct.

For example:

FName DOB DateEntered
Bill 1950-10-23 2007-01-02
Bill 1950-10-23 2007-01-02
Bill 1950-10-23 2007-01-03

If you select DISTINCT FName, you will only get one row back. If you select DISTING FName, DOB you will only get one row back. BUT if you include DateEntered in the DISTINCT (SELECT DISTINCT FName, DateEntered), you will get TWO rows back. THere are two distinct DateEntered values.

When doing comparisons, all you normally need is one value to compare to especially when using IN or EXISTS.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLDenis]

you don't need distinct with EXISTS since it is either true or false. the EXISTS does not return anything, it just lets the outer query know if there is a match or not

take a look at this

CREATE TABLE testnulls (ID INT) 
INSERT INTO testnulls VALUES (1) 
INSERT INTO testnulls VALUES (2) 
INSERT INTO testnulls VALUES (1) 
INSERT INTO testnulls VALUES (2) 
INSERT INTO testnulls VALUES (1) 
INSERT INTO testnulls VALUES (2) 
INSERT INTO testnulls VALUES (1) 
INSERT INTO testnulls VALUES (2) 
INSERT INTO testnulls VALUES (null) 

CREATE TABLE testjoin (ID INT) 
INSERT INTO testjoin VALUES (1) 
INSERT INTO testjoin VALUES (3)

as you can see the testnulls table has a bunch of dups
we will get back only one row when running this query below

SELECT * FROM testjoin j 
WHERE  EXISTS (SELECT n.ID 
FROM testnulls n 
WHERE n.ID = j.ID)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[fionama]

When I run the sub-query, I get 24 records. Thanks for the tip re extracting too much from the sub-query.
And thank you for your tip re joins. Unfortunately, I am relatively new at this, so the old-style joins are easier for me to follow. And I have double checked them, and that's not the problem.
Thanks again.

 

[fionama]

Have removed the DISTINCT - doesn't make any difference

 

[SQLDenis]

with exists you don't need any columns to return

in the code I gave you before it doesn't matter if you have this

SELECT * FROM testjoin j 
WHERE  EXISTS (SELECT 123456
FROM testnulls n 
WHERE n.ID = j.ID)

this

SELECT * FROM testjoin j 
WHERE  EXISTS (SELECT *
FROM testnulls n 
WHERE n.ID = j.ID)

or this

SELECT * FROM testjoin j 
WHERE  EXISTS (SELECT 1
FROM testnulls n 
WHERE n.ID = j.ID)

it does not use that at all, exists returns true or false it there is a match

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Rememeber that old style joins only work if you are doing inner joins. They will not return correct results consistently when doing outer joins. It is worth it to learn the join syntax from the start rather than when you get involved in more complex queries.

"NOTHING is more important in a database than integrity." ESquared

 

[fredericofonseca]

SQL reformated for easier reading. Really, as others have said, get used to the current format. Also because new versions of SQL will not allow that old standard.

select I.INCIDENTID,
       P.PAS_NO,
       P.SURNAME,
       P.FIRNAME,
       D.BIRTH_DATE,
       Q.DESCRIP,
       QS.QUESTIONTEXT,
       A.ID,
       A.ANSWER
from MAT_INCIDENTS I,
INNER JOIN MAT_DELIVERY D,
   ON I.INCIDENTID = D.INCIDENTID
INNER JOIN PASMAIN P,
   ON D.MOTHER_PAS_NO = P.PAS_NO
INNER JOIN MAT_QUESTIONNAIREINCIDENTS QI,
   ON I.INCIDENTID = QI.INCIDENTID
INNER JOIN MAT_QUESTIONNAIRE Q,
   ON Q.ID = QI.QUESTIONNAIREID
  AND Q.ID IN (221)
INNER JOIN MAT_QUESTIONS QS,
   ON QI.QUESTIONNAIREID = QS.QUESTIONNAIREID
  AND QS.ID IN (72467)
INNER JOIN MAT_ANSWERS A,
   ON QS.ID = A.QUESTIONID
  AND A.ID IN (1600170,1600171,1600172,1601155)
INNER JOIN MAT_INCIDENTANSWERS IA
   ON D.INCIDENTID = IA.INCIDENTID
  AND A.ID = IA.ANSWERID
WHERE D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-02-01 00:00:00'
AND EXISTS (SELECT distinct 1
            from MAT_INCIDENTS Ib,
            INNER JOIN MAT_DELIVERY Db,
               ON Ib.INCIDENTID = Db.INCIDENTID
              AND Db.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-02-01 00:00:00'
            INNER JOIN PASMAIN Pb,
               ON Db.MOTHER_PAS_NO = Pb.PAS_NO
            INNER JOIN MAT_QUESTIONNAIREINCIDENTS QIb,
               ON Ib.INCIDENTID = QIb.INCIDENTID
            INNER JOIN MAT_QUESTIONNAIRE Qb,
              AND Qb.ID = QIb.QUESTIONNAIREID
              AND Qb.ID  = 2
            INNER JOIN MAT_QUESTIONS QSb,
               ON QIb.QUESTIONNAIREID = QSb.QUESTIONNAIREID
              AND QSb.ID  = 72637
            INNER JOIN MAT_ANSWERS Ab,
               ON QSb.ID = Ab.QUESTIONID
              AND Ab.ID IN (1601167,1601168,1601169,1601170,1601171,1601172)
            INNER JOIN MAT_INCIDENTANSWERS IAb
               ON Db.INCIDENTID = IAb.INCIDENTID
              AND Ab.ID = IAb.ANSWERID
             )
ORDER BY P.PAS_NO ASC

By looking at the code there is no relation between the outer SQL and the exists one.

The way it is, as long as the sql within the Exists returns 1 record, then the outer ones will return ALL records as if there was no EXISTS on your SQL.

In order to get what you wish with the EXISTS, you NEED to relate the outside tables with the inside tables.


Also you say

did not have a caesarian section (question id = 72637 and answer id one of 1601167,1601168,1601169,1601170,1601171,1601172)

That would probably mean you wish to use the "NOT EXISTS" instead anyway, as if they did had the ID = 72637 you do not want to report them. I may have gotten this wrong though.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fionama]

Frederico,
Thanks so much. I totally overlooked the fact that there was no relation between the outer SQL and the exists one. When I relate them, it works perfectly. Thank you also for re-working the syntax. Thanks for all other help also.
Fiona