JOIN Query Problem

Hi, I'm not that experienced with this so I need some help :)
I've got 2 tables a Header and a Line table
Now I want to select all headers but I want to filter out those that
have lines with the Status Finished
So I came up with this query:
SELECT *
FROM Header
LEFT JOIN Line ON (Header.ID=Line.HeaderID AND
Line.Status<>'FINISHED')
WHERE Header.ID<>'';
The problem here is that I get all headers even the ones with the have
lines with the status Finished. Only those lines don't show, but the
orders do.
Can't I do this in 1 single query?
Thanks in advanceHi
SELECT *
FROM Header
JOIN Line ON (Header.ID=Line.HeaderID ) WHERE Line.Status<>'FINISHED' AND
Header.ID<>''
<kenny.vaes@.gmail.com> wrote in message
news:1133263151.681455.181630@.z14g2000cwz.googlegroups.com...
> Hi, I'm not that experienced with this so I need some help :)
> I've got 2 tables a Header and a Line table
> Now I want to select all headers but I want to filter out those that
> have lines with the Status Finished
> So I came up with this query:
> SELECT *
> FROM Header
> LEFT JOIN Line ON (Header.ID=Line.HeaderID AND
> Line.Status<>'FINISHED')
> WHERE Header.ID<>'';
> The problem here is that I get all headers even the ones with the have
> lines with the status Finished. Only those lines don't show, but the
> orders do.
> Can't I do this in 1 single query?
> Thanks in advance
>|||And this here don=B4t work for you ?
SELECT *
FROM Header
LEFT JOIN Line ON (Header.ID=3DLine.HeaderID
WHERE Header.ID<>'' AND Line.Status<>'FINISHED'=20
HTH, jens Suessmeyer.|||Without that parentethes which priduces an error:
SELECT *
FROM Header
LEFT JOIN Line ON Header.ID=Line.HeaderID
WHERE Header.ID<>'' AND Line.Status<>'FINISHED'|||Jens
Yep, that's exactly what I've posted , in that case he does not need to
have LEFT JOIN . It produces the same result
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133264002.077565.231590@.z14g2000cwz.googlegroups.com...
> Without that parentethes which priduces an error:
> SELECT *
> FROM Header
> LEFT JOIN Line ON Header.ID=Line.HeaderID
> WHERE Header.ID<>'' AND Line.Status<>'FINISHED'
>|||When I use this syntax I do not see the Headers that do not have a
corresponding line.
When i do not use the filter AND Line.Status<>'FINISHED' I see all
Headers, even the ones that have no lines. But when I now filter on the
status. All the ones With status = finished dissappear but also the
empty ones.
And i really need to see the empty ones also :(|||Or is there a way to select only the headers that have no Lines? That
way I can put all the ID's in a collection and do a select at the end
of al those ID's.
So it would be a select of all the ones that have not been finished and
afterwards one woth all the empty ones.|||kenny.vaes@.gmail.com wrote on 29 Nov 2005 04:59:49 -0800:

> When I use this syntax I do not see the Headers that do not have a
> corresponding line.
> When i do not use the filter AND Line.Status<>'FINISHED' I see all
> Headers, even the ones that have no lines. But when I now filter on the
> status. All the ones With status = finished dissappear but also the
> empty ones.
> And i really need to see the empty ones also :(
SELECT *
FROM Header
LEFT JOIN Line ON Header.ID=Line.HeaderID
WHERE Header.ID<>'' AND (Line.Status<>'FINISHED' or Line.Status IS NULL)
Dan