3 tables ...

Hey
I have 3 tables :
Jobs table
jobID
jobname
JobQuestion
id
jobID
questionID
points
Question
questionID
questionText
I'd like to join this tables so that I have all names of jobs and points for
given question. It doesn't matter if the question has it's row in
jobQuestion table. I tried to use left / right joins but when I use WHERE
QuestionID = xxx I got only row where they exists in jobsQuestions Table.
Now it looks like this:
Select Job.Name, JobQuestion.Points
FROM Job Left Join Job.JobId = JobQuestion.JobId
WHERE JobQuestion.QuestionID = @.someParam
I don't use in this join 3 table but maybe I should but I don't know how to
join them so they work as I wrote above.
JarodCould you please so kind to provide us DDL for a better comprehension?
Thanks,
"Jarod" wrote:

> Hey
> I have 3 tables :
> Jobs table
> jobID
> jobname
> JobQuestion
> id
> jobID
> questionID
> points
> Question
> questionID
> questionText
> I'd like to join this tables so that I have all names of jobs and points f
or
> given question. It doesn't matter if the question has it's row in
> jobQuestion table. I tried to use left / right joins but when I use WHERE
> QuestionID = xxx I got only row where they exists in jobsQuestions Table.
> Now it looks like this:
> Select Job.Name, JobQuestion.Points
> FROM Job Left Join Job.JobId = JobQuestion.JobId
> WHERE JobQuestion.QuestionID = @.someParam
> I don't use in this join 3 table but maybe I should but I don't know how t
o
> join them so they work as I wrote above.
> Jarod
>|||> Could you please so kind to provide us DDL for a better comprehension?
> Thanks,
>
I wrote this in that way because the tables are a bit more complicated and
field names aren't written in English so let's stay with this form.
Jarod|||Is this what you're after...
select * from Question a
LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
LEFT JOIN Jobs c on b.JobID = c.JobID
WHERE a.QuestionID = 1
HTH. Ryan
"Jarod" <blueice@.NOSPAM.gazeta.pl> wrote in message
news:%2308KwhRHGHA.2912@.tk2msftngp13.phx.gbl...
> I wrote this in that way because the tables are a bit more complicated and
> field names aren't written in English so let's stay with this form.
> Jarod|||> select * from Question a
> LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
> LEFT JOIN Jobs c on b.JobID = c.JobID
> WHERE a.QuestionID = 1
>
And the problem is when I do somethink like this I got only one row which
exist in JobQuestion table. And what I wanted is all rows from Job table and
values for them from jobQuestion table. To be more precise Names of the Job
and Points ;)
Jarod|||"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uQaBhmRHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Is this what you're after...
> select * from Question a
> LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
> LEFT JOIN Jobs c on b.JobID = c.JobID
> WHERE a.QuestionID = 1
OR a.Question IS NULL
When I added OR a.Question IS NULL it works ;)
Jarod|||Better move the a.Question = 1 to the join:
...
LEFT JOIN JobQuestion b on (a.QuestionID = b.QuestionID) and (a.Question = 1
)
...
There was a discussion on this issue a few days ago:
http://msdn.microsoft.com/newsgroup...2430&sloc=en-us
I believe you should be aware of potential issues.
ML
http://milambda.blogspot.com/