Join not returning records if one missing.

How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
TomTry:
Select PositionID,JobTitle,Category
from Position p
Left Join JobCategory j on p.categoryCode = j.categoryCode
where p.PositionID = 54
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u4uWz2gRFHA.3296@.TK2MSFTNGP15.phx.gbl...
How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
Tom|||Witout DDL for the tables, it's hard to be sure, but I think what you are
trying to do would require an Outer Join. In an Outer Join, all the records
from one side will be produced, even if there's no match from the other side
on the Join condidtions
Select PositionID,JobTitle,Category
From Position p
Left Outer Join JobCategory j
On j.categoryCode = p.categoryCode
Where PositionID = 54
"tshad" wrote:

> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>|||Dear tshad,
Try following .....
-- If U want all records from Position
Select P.PositionID,J.JobTitle,J.Category from Position P
Left Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
-- If U want all records from JobCategory
Select P.PositionID,J.JobTitle,J.Category from Position P
Right Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
With Regards,
Rakesh Ranjan
Mail me on -- rakesh.ranjan@.3i-infotech.com
"tshad" wrote:

> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>