difficult select statement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

difficult select statement

Author

Topic

abomb
Starting Member

13 Posts

Posted - 2008-12-29 : 13:56:30

This question is regarding a custom survey application database and has 6 relevant tables:

substance
- substanceID

field
- fieldID

surveyfield
- surveyfieldID
- substanceID
- fieldID

survey
- surveyID

surveyfieldanswer
- surveyFieldID
- supplierSurveyID
- answer

suppliersurvey
- suppliersurveyID
- surveyID

My select statement should return all possible combinations of substanceID and fieldID for each suppliersurvey, and the answer or NULL depending if there is a record in surveyfield or surveyfieldanswer.

There are 7 field, 31 substance, 14 suppliersurvey records so the query should return 3038 records.

Can anyone help me solve this problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 14:07:11

[code]
select sy.*,t.*
from suppliersurvey ss
left join
(
select sfa.supplierSurveyID,sfa.answer,sfa.surveyFieldID,s.*,f.*
from surveyfieldanswer sfa
on ss.supplierSurveyID=sfa.supplierSurveyID
inner join surveyfield sf
on sf.surveyfieldID=sfa.surveyfieldID
inner join substance s
on s.substanceID=sf.substanceID
inner join field f
on f. fieldID=sf.fieldID
)t
on t.supplierSurveyID=ss.supplierSurveyID
left join survey sy
on sy.surveyID=ss.surveyID
[/code]

abomb
Starting Member

13 Posts

Posted - 2008-12-29 : 14:11:47

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'on'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 14:14:10

quote:
Originally posted by abomb

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'on'.

copy paste mistake.


select sy.*,t.*
from suppliersurvey ss
left join 
(
select sfa.supplierSurveyID,sfa.answer,sfa.surveyFieldID,s.*,f.*
from surveyfieldanswer sfa
inner join surveyfield sf
on sf.surveyfieldID=sfa.surveyfieldID
inner join substance s
on s.substanceID=sf.substanceID
inner join field f
on f. fieldID=sf.fieldID
)t
on t.supplierSurveyID=ss.supplierSurveyID
left join survey sy
on sy.surveyID=ss.surveyID

abomb
Starting Member

13 Posts

Posted - 2008-12-29 : 14:25:10

that can't be right because it only returns 937 rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 14:37:32

ok.seems like what you're after is cross join


select *
from
(
select ss.suppliersurveyID,
ss.surveyID,
sy.*,
s.substanceID,
f.fieldID
from suppliersurvey ss
inner join survey sy
on sy.surveyid=ss.surveyid
cross join substance s
cross join field f
)t1
left join
(
select sfa.surveyfieldID,
sfa.supplierSurveyID,
sfa.answer,
sf.substanceID,
sf.fieldID
from surveyfield sf
inner join surveyfieldanswer sfa
on sfa.surveyfieldID=sf.surveyfieldID
)t2
on t2.supplierSurveyID=t1.supplierSurveyID
and t2.fieldID=t1.fieldID
and t2.substanceID=t1.substanceID

abomb
Starting Member

13 Posts

Posted - 2008-12-29 : 14:39:54

I kinda tweaked yours a bit and I think I got it.

SELECT t.*, v.answer
FROM (SELECT DISTINCT ss.supplierSurveyID, s.substanceID, f.fieldID
FROM Compliance.Substance s, Compliance.Field f, Compliance.SupplierSurvey ss)t
LEFT JOIN (
SELECT DISTINCT surveyFieldID, substanceID, fieldID
FROM Compliance.SurveyField)u
ON t.substanceID = u.substanceID AND t.fieldID = u.fieldID
LEFT JOIN (
SELECT supplierSurveyID, surveyFieldID, answer
FROM Compliance.SurveyFieldAnswer sfa
)v
ON t.supplierSurveyID = v.supplierSurveyID AND u.surveyFieldID = v.surveyFieldID

abomb
Starting Member

13 Posts

Posted - 2008-12-29 : 14:54:09

thanks for your suggestion visakh16!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 14:54:59

welcome