Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

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
0
mSchmidt
Asked:
mSchmidt
1 Solution
 
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
 
NievergeltCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
NievergeltCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now