Complicated outer join, join on all with where sentence...

Hi i have two tables

torsk_person table
containing Person_id(INT) and person_name(NVARCHAR)

another table containing some boolean attributes for specific year for the specific person
torsk_deltag table
containing person_id (FK,INT), delt_year (INT),delt_deltag(BIT),delt_afbud(BIT)

now what i wish to do is select all persons and retrieve their data for a specific year
i tried this... however it does not work when the where sentence is used... what can i do to get this information ???
select person_navn,delt_deltog,delt_afbud,delt_nomineret,delt_torsk from torsk_person left outer  join torsk_deltag on torsk_deltag.person_id = torsk_person.person_id where delt_year=2004


Thanks
mSchmidtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
what do you mean when you say "it doesn't work"?  do you get an error, or do you NOT get the records that you expect?

The query looks ok, except for possible mis-spellings of field names.

What database are you using? Sql Server?  Oracle?

AW
0
BillAn1Commented:
try  this instead :

select person_navn,delt_deltog,delt_afbud,delt_nomineret,delt_torsk
from torsk_person left outer  join torsk_deltag on torsk_deltag.person_id = torsk_person.person_id
and delt_year=2004
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NievergeltSenior SW DevCommented:
If you have a foreign key constraint on torsk_deltag.person_id, you do not need a left outer join, but an INNER JOIN:

SELECT person_navn, delt_deltog, delt_afbud, delt_nomineret, delt_torsk
FROM torsk_person INNER JOIN torsk_deltag ON torsk_deltag.person_id = torsk_person.person_id
WHERE delt_year=2004
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I am with Nievergelt.
What for do you need the LEFT OUTER JOIN?
0
tnewc59Commented:
select person_navn,delt_deltog,delt_afbud,delt_nomineret,delt_torsk from torsk_person
left outer  join torsk_deltag on torsk_deltag.person_id = torsk_person.person_id
AND delt_year=2004
0
tnewc59Commented:
The problem is that you have an optional foreign key in torsk_deltag and so you need to do an outer join.

You can accomplish this via a where clause, but you need to account for the fact that the delt_year could be null.

e.g.

select person_navn,delt_deltog,delt_afbud,delt_nomineret,delt_torsk from torsk_person
left outer  join torsk_deltag on torsk_deltag.person_id = torsk_person.person_id
WHERE (delt_year=2004
            OR torsk_deltag.person_id is null)

This is an annoying problem with the left join concept.  I usually add the conditional checks in the join syntax.  If it gets terribly complex, you can use either an UDF (user defined function) that returns a table and then  join to it (e.g. fn_torsk_deltagGetByYear(2004).  This isn't necessary in your situation.
0
pedros7Commented:
Y, Nievergelt  is correct.
0
tnewc59Commented:
Actually, an inner join would not be valid if it is an OPTIONAL foreign key.  I see nothing that states it is a required foreign key.

If you do perform an inner join, you will only get the employees back that have data in torsk_deltag.

As an illustration of this, let's use northwind...

select *
from employees
      left join employees mgr
            on employees.reportsTo = mgr.employeeId
            and  mgr.title = 'Sales Manager'

This query gives you all employees, and for those employees that report to a Sales Manager, it gives you their managers information as well.

Whereas this query:
select *
from employees
      inner join employees mgr
            on employees.reportsTo = mgr.employeeId
WHER mgr.title = 'Sales Manager'
or this query:
select *
from employees
      left join employees mgr
            on employees.reportsTo = mgr.employeeId
WHER mgr.title = 'Sales Manager'

Only give you data for employees that report to a Sales Manager.  It appears BillAn1 already gave a solution to this though.

It appears the "what do you need a left join" response is assuming that the person_id is a REQUIRED foreign key in torsk_person.  I don't see anything in this to indicate it is required...

The solution depends upon what data you want returned...
0
NievergeltSenior SW DevCommented:
Hi tnewc59

What about:
> another table containing some boolean attributes for specific year for the specific person
> torsk_deltag table
> containing person_id (FK,INT), delt_year (INT),delt_deltag(BIT),delt_afbud(BIT)
____________________A
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.