Combining tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining tables

Author

Topic

fionama
Starting Member

6 Posts

Posted - 2007-12-20 : 08:45:56

Hi all,
I am working from a database containing sets of questions and answers relating to maternity episodes. The answer tables are broken up into two tables, freetext answers and standard drop-down answers. The questions relating to these answers are held in the same table, MAT_QUESTIONS.
When I want to view the freetext answers and questions for a certain episode (based on incidentid), the following code works..
SELECT P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, FA.ANSWERTEXT
FROM MAT_FREEANSWERS FA, MAT_QUESTIONS QS,
MAT_INCIDENTS I, MAT_DELIVERY D,
PASMAIN P
WHERE FA.QUESTIONID=QS.ID
AND FA.INCIDENTID=I.INCIDENTID
AND I.INCIDENTID=D.INCIDENTID
AND D.MOTHER_PAS_NO=P.PAS_NO
AND D.BIRTH_DATE BETWEEN '2007-01-01 00:00:00' AND '2007-01-31 23:59:59'
AND QS.QUESTIONNAIREID=2
AND FA.INCIDENTID=4501

and if I want to view the answers to the standard drop down questions, I use the following code...

select P.SURNAME, P.FIRNAME,QS.ID,QS.QUESTIONTEXT, 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-01-31 23:59:59'
AND Q.ID=2
AND I.INCIDENTID=4501

However, I would like to join the two together, as I would like to see a list of all questions and answers posed to a particular patient. But, as there are so many tables in common, I'm having difficulty doing this.

Would appreciate any help!
Fiona

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 08:54:29

Use "UNION" or "UNION ALL" to combine the two queries.
And please learn ANSI JOIN style.

E 12°55'05.25"
N 56°04'39.16"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-20 : 08:55:02

try UNION ALL

select 1,2,3
union all
select 5,6,7

elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 09:01:01

[code]SELECT P.SURNAME,
P.FIRNAME,
QS.ID,
QS.QUESTIONTEXT,
FA.ANSWERTEXT
FROM MAT_FREEANSWERS AS FA
INNER JOIN MAT_QUESTIONS AS QS ON QS.ID = FA.QUESTIONID
INNER JOIN MAT_INCIDENTS AS I ON I.INCIDENTID = FA.INCIDENTID
INNER JOIN MAT_DELIVERY AS D ON D.INCIDENTID = I.INCIDENTID
INNER JOIN PASMAIN AS P ON P.PAS_NO = D.MOTHER_PAS_NO
WHERE D.BIRTH_DATE >= '2007-01-01'
AND D.BIRTH_DATE < '2007-02-01'
AND QS.QUESTIONNAIREID = 2
AND FA.INCIDENTID = 4501

SELECT P.SURNAME,
P.FIRNAME,
QS.ID,
QS.QUESTIONTEXT,
A.ANSWER
FROM MAT_INCIDENTS AS I
INNER JOIN MAT_DELIVERY AS D ON D.INCIDENTID = I.INCIDENTID
INNER JOIN PASMAIN AS P ON P.PAS_NO = D.MOTHER_PAS_NO
INNER JOIN MAT_QUESTIONNAIREINCIDENTS AS QI ON QI.INCIDENTID = I.INCIDENTID
INNER JOIN MAT_QUESTIONNAIRE AS Q ON Q.ID = QI.QUESTIONNAIREID
INNER JOIN MAT_QUESTIONS AS QS ON QS.QUESTIONNAIREID = QI.QUESTIONNAIREID
INNER JOIN MAT_ANSWERS AS A ON A.QUESTIONID = QS.ID
INNER JOIN MAT_INCIDENTANSWERS AS IA ON IA.INCIDENTID = D.INCIDENTID
WHERE A.ID = IA.ANSWERID
AND D.BIRTH_DATE >= '2007-01-01'
AND D.BIRTH_DATE < '2007-02-01'
AND Q.ID = 2
AND I.INCIDENTID = 4501[/code]

E 12°55'05.25"
N 56°04'39.16"

fionama
Starting Member

6 Posts

Posted - 2007-12-20 : 09:43:51

For some reason, it didn't like the aliases, so I removed them. The individual queries work fine separately, but when I use union, I get an error...
"Error: Dynamic SQL Error. SQL error code = -104. Invalid command. Data type unknown. (State:HY000, Native Code: FFFFFF98)"

SELECT PASMAIN.SURNAME, PASMAIN.FIRNAME, MAT_QUESTIONS.ID, MAT_QUESTIONS.QUESTIONTEXT, MAT_FREEANSWERS.ANSWERTEXT
FROM MAT_FREEANSWERS
INNER JOIN MAT_QUESTIONS ON MAT_QUESTIONS.ID = MAT_FREEANSWERS.QUESTIONID
INNER JOIN MAT_INCIDENTS ON MAT_INCIDENTS.INCIDENTID = MAT_FREEANSWERS.INCIDENTID
INNER JOIN MAT_DELIVERY ON MAT_DELIVERY.INCIDENTID = MAT_INCIDENTS.INCIDENTID
INNER JOIN PASMAIN ON PASMAIN.PAS_NO = MAT_DELIVERY.MOTHER_PAS_NO
WHERE MAT_DELIVERY.BIRTH_DATE >= '2007-01-01'
AND MAT_DELIVERY.BIRTH_DATE < '2007-02-01'
AND MAT_QUESTIONS.QUESTIONNAIREID = 2
AND MAT_FREEANSWERS.INCIDENTID = 4501
UNION ALL
SELECT PASMAIN.SURNAME, PASMAIN.FIRNAME, MAT_QUESTIONS.ID, MAT_QUESTIONS.QUESTIONTEXT, MAT_ANSWERS.ANSWER
FROM MAT_INCIDENTS
INNER JOIN MAT_DELIVERY ON MAT_DELIVERY.INCIDENTID = MAT_INCIDENTS.INCIDENTID
INNER JOIN PASMAIN ON PASMAIN.PAS_NO = MAT_DELIVERY.MOTHER_PAS_NO
INNER JOIN MAT_QUESTIONNAIREINCIDENTS ON MAT_QUESTIONNAIREINCIDENTS.INCIDENTID = MAT_INCIDENTS.INCIDENTID
INNER JOIN MAT_QUESTIONNAIRE ON MAT_QUESTIONNAIRE.ID = MAT_QUESTIONNAIREINCIDENTS.QUESTIONNAIREID
INNER JOIN MAT_QUESTIONS ON MAT_QUESTIONS.QUESTIONNAIREID = MAT_QUESTIONNAIREINCIDENTS.QUESTIONNAIREID
INNER JOIN MAT_ANSWERS ON MAT_ANSWERS.QUESTIONID = MAT_QUESTIONS.ID
INNER JOIN MAT_INCIDENTANSWERS ON MAT_INCIDENTANSWERS.INCIDENTID = MAT_DELIVERY.INCIDENTID
WHERE MAT_ANSWERS.ID = MAT_INCIDENTANSWERS.ANSWERID
AND MAT_DELIVERY.BIRTH_DATE >= '2007-01-01'
AND MAT_DELIVERY.BIRTH_DATE < '2007-02-01'
AND MAT_QUESTIONNAIRE.ID = 2
AND MAT_INCIDENTS.INCIDENTID = 4501

Does anyone know what this could mean?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:03:13

Which tool are you using?

E 12°55'05.25"
N 56°04'39.16"

fionama
Starting Member

6 Posts

Posted - 2007-12-20 : 10:05:57

Win SQL Lite version 5.0.53.560

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:18:31