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.
| Author |
Topic |
|
drsloat
Starting Member
45 Posts |
 Posted - 2001-12-18 : 18:25:33
|
| I'm trying to figure out the syntax for an Outer Join on a derived table:Table1 Left Outer Join ( Select a derived table ) as D ON (table1.something = d.somethin)Table1 is also derived, but I don't think that should matter. I've tried a lot of variations w/o success, so I'm really just wondering if there's any reason why this is not possible? Maybe I just haven't hit the right syntax yet?I guess my alternative would be to put the derived table D into a temporary table and make my outer join to that table. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-18 : 18:34:23
|
| Can you provide the full SQL statement you're using? It's definitely possible to JOIN multiple derived tables, but we'll need the entire statement to troubleshoot it.What exactly is not working, by the way? Are you getting a syntax error? If you have a WHERE clause, it may be affecting the LEFT JOIN condition and will only return the equivalent of an INNER JOIN. |
 |
|
|
drsloat
Starting Member
45 Posts |
Posted - 2001-12-18 : 18:47:53
|
| whoa! In trying to write it out for you I musta changed something and fixed it caus it works now!!! Thanks for your bueno help. Take a look at this beast. I'm dealing with a table structure which I don't think is quite logically sound. I also used my first ever cross join. Can I join to a view? I bet I could simplify things by making a view.Select SiteUserQuestions.question_id, SiteUserQuestions.siteuser_ID, q.question_text, ta.answerFromquestions q,(Select question_id, siteuser_id from ( select q.question_id From questions q, survey_questions sq, surveys s Where s.survey_id = 1401 And sq.question_id = q.question_id And sq.survey_id = s.survey_id )as SurveyQuestions Cross Join SiteUsers s ) as SiteUserQuestions Left Outer Join ( Select s.siteuser_id,qa.question_id, Coalesce(a.question_choice_id, string) as Answer From siteusers s, siteuser_answers sa, answers a, question_answers qa Where s.siteuser_id = sa.siteuser_id And sa.answer_id = a.answer_id And a.answer_id = qa.answer_id ) as ta ON (SiteUserQuestions.Question_id = ta.question_id And SiteUserQuestions.SiteUser_id = ta.SiteUser_id) Where SiteUserQuestions.question_id = q.question_idOrder by SiteUserQuestions.Siteuser_ID |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-18 : 23:33:19
|
| Yeah, you can use views for joining.-------------------------"Success is when Preparedness meets Opportunity" |
|
|
|
|
|
|
|
|
Outer Join On a derived table?