joining 2 tables - outer join

I have the following 2 tables and data:
CREATE TABLE [Applications] (
[Applicant] [char] (20) NULL ,
[PositionID] [int] NULL
) ON [PRIMARY]
go
CREATE TABLE [positions] (
[PositionID] [int] NULL ,
[JobID] [int] NULL
) ON [PRIMARY]
go
insert positions values(1,25)
insert positions values (2,37)
insert positions values (3,15)
insert positions values (12,15)
insert positions values (18,12)
insert applications values('tom',2)
insert applications values('frank',1)
insert applications values('tom',12)
insert applications values('larry',2)
insert applications values('mary',15)
I want to join the tables to show all the positions and put a star next to
the ones that 'tom' is in. I tried using an outer join just to get the
applicant names to show and expected Nulls in the applicants, something
like:
select Applicant,a.positionID,JobID from positions p left outer join
applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
and wanted:
null 1 25
tom 2 37
null 3 15
tom 12 15
null 18 12
What I got was:
tom 2 37
tom 12 15
I then want to change the name to just show a "*" for name field and get rid
of the null (blank).
1 25
* 2 37
3 15
* 12 15
18 12
Can I do this in one select?
Thanks,
tomTry something like.
select Case Applicant When 'Tom' then '*' else '' end,a.positionID,JobID
from positions p left outer join
applications a on (p.PositionID = a.PositionID)
Ryan
"tshad" wrote:

> I have the following 2 tables and data:
> CREATE TABLE [Applications] (
> [Applicant] [char] (20) NULL ,
> [PositionID] [int] NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [positions] (
> [PositionID] [int] NULL ,
> [JobID] [int] NULL
> ) ON [PRIMARY]
> go
> insert positions values(1,25)
> insert positions values (2,37)
> insert positions values (3,15)
> insert positions values (12,15)
> insert positions values (18,12)
> insert applications values('tom',2)
> insert applications values('frank',1)
> insert applications values('tom',12)
> insert applications values('larry',2)
> insert applications values('mary',15)
> I want to join the tables to show all the positions and put a star next to
> the ones that 'tom' is in. I tried using an outer join just to get the
> applicant names to show and expected Nulls in the applicants, something
> like:
> select Applicant,a.positionID,JobID from positions p left outer join
> applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
> and wanted:
> null 1 25
> tom 2 37
> null 3 15
> tom 12 15
> null 18 12
> What I got was:
> tom 2 37
> tom 12 15
> I then want to change the name to just show a "*" for name field and get r
id
> of the null (blank).
> 1 25
> * 2 37
> 3 15
> * 12 15
> 18 12
> Can I do this in one select?
> Thanks,
> tom
>
>|||Try,
select
a.applicant,
b.PositionID,
b.JobID
from
Applications as a
inner join
positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
union all
select
' ',
b.PositionID,
b.JobID
from
Applications as a
right join
positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
where
a.applicant is null
order by
b.PositionID
go
AMB
"tshad" wrote:

> I have the following 2 tables and data:
> CREATE TABLE [Applications] (
> [Applicant] [char] (20) NULL ,
> [PositionID] [int] NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [positions] (
> [PositionID] [int] NULL ,
> [JobID] [int] NULL
> ) ON [PRIMARY]
> go
> insert positions values(1,25)
> insert positions values (2,37)
> insert positions values (3,15)
> insert positions values (12,15)
> insert positions values (18,12)
> insert applications values('tom',2)
> insert applications values('frank',1)
> insert applications values('tom',12)
> insert applications values('larry',2)
> insert applications values('mary',15)
> I want to join the tables to show all the positions and put a star next to
> the ones that 'tom' is in. I tried using an outer join just to get the
> applicant names to show and expected Nulls in the applicants, something
> like:
> select Applicant,a.positionID,JobID from positions p left outer join
> applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
> and wanted:
> null 1 25
> tom 2 37
> null 3 15
> tom 12 15
> null 18 12
> What I got was:
> tom 2 37
> tom 12 15
> I then want to change the name to just show a "*" for name field and get r
id
> of the null (blank).
> 1 25
> * 2 37
> 3 15
> * 12 15
> 18 12
> Can I do this in one select?
> Thanks,
> tom
>
>|||"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:14B02A9A-01ED-4025-902D-37DF7F8C980D@.microsoft.com...
> Try something like.
> select Case Applicant When 'Tom' then '*' else '' end,a.positionID,JobID
> from positions p left outer join
> applications a on (p.PositionID = a.PositionID)
Does the job.
Thanks,
Tom
> Ryan
> "tshad" wrote:
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:FAF40495-536F-425B-B4C3-17C68E32BFFB@.microsoft.com...
> Try,
> select
> a.applicant,
> b.PositionID,
> b.JobID
> from
> Applications as a
> inner join
> positions as b
> on a.PositionID = b.PositionID and a.applicant = 'tom'
> union all
> select
> ' ',
> b.PositionID,
> b.JobID
> from
> Applications as a
> right join
> positions as b
> on a.PositionID = b.PositionID and a.applicant = 'tom'
> where
> a.applicant is null
> order by
> b.PositionID
> go
>
This one does what I was looking for.
Is there performance hit doing these 2 selects versus Ryans which does the
outer join?
Thanks,
Tom
> AMB
>
> "tshad" wrote:
>|||I do not get expected result using Ryan's suggestion.
use northwind
go
CREATE TABLE [Applications] (
[Applicant] [char] (20) NULL ,
[PositionID] [int] NULL
) ON [PRIMARY]
go
CREATE TABLE [positions] (
[PositionID] [int] NULL ,
[JobID] [int] NULL
) ON [PRIMARY]
go
insert positions values(1,25)
insert positions values (2,37)
insert positions values (3,15)
insert positions values (12,15)
insert positions values (18,12)
insert applications values('tom',2)
insert applications values('frank',1)
insert applications values('tom',12)
insert applications values('larry',2)
insert applications values('mary',15)
go
select
'*',
b.PositionID,
b.JobID
from
Applications as a
inner join
positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
union all
select
'',
b.PositionID,
b.JobID
from
Applications as a
right join
positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
where
a.applicant is null
order by
b.PositionID
-- Ryan's idea
select
case Applicant When 'Tom' then '*' else '' end,
a.positionID,
JobID
from
positions p
left outer join
applications a
on (p.PositionID = a.PositionID)
go
drop table Applications, positions
go
Result:
PositionID JobID
-- -- --
1 25
* 2 37
3 15
* 12 15
18 12
(5 row(s) affected)
positionID JobID
-- -- --
1 25
* 2 37
2 37
NULL 15
* 12 15
NULL 12
(6 row(s) affected)
AMB
"tshad" wrote:

> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:FAF40495-536F-425B-B4C3-17C68E32BFFB@.microsoft.com...
> This one does what I was looking for.
> Is there performance hit doing these 2 selects versus Ryans which does the
> outer join?
> Thanks,
> Tom
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:09F515E4-D541-4215-89A8-BB229205F8C1@.microsoft.com...
>I do not get expected result using Ryan's suggestion.
You're right.
I had to look at it awhile to figure it out. I tried both the right and
left join and they all gave me extra rows, which they should have - and was
not what I was looking for. It was more apparent when I took out the case
statement and saw the Nulls.
The union gave me what I was looking for, which was to give me all the
applications and if 'tom' was there, indicate it.
Thanks,
Tom
> use northwind
> go
> CREATE TABLE [Applications] (
> [Applicant] [char] (20) NULL ,
> [PositionID] [int] NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [positions] (
> [PositionID] [int] NULL ,
> [JobID] [int] NULL
> ) ON [PRIMARY]
> go
> insert positions values(1,25)
> insert positions values (2,37)
> insert positions values (3,15)
> insert positions values (12,15)
> insert positions values (18,12)
> insert applications values('tom',2)
> insert applications values('frank',1)
> insert applications values('tom',12)
> insert applications values('larry',2)
> insert applications values('mary',15)
> go
> select
> '*',
> b.PositionID,
> b.JobID
> from
> Applications as a
> inner join
> positions as b
> on a.PositionID = b.PositionID and a.applicant = 'tom'
> union all
> select
> '',
> b.PositionID,
> b.JobID
> from
> Applications as a
> right join
> positions as b
> on a.PositionID = b.PositionID and a.applicant = 'tom'
> where
> a.applicant is null
> order by
> b.PositionID
> -- Ryan's idea
> select
> case Applicant When 'Tom' then '*' else '' end,
> a.positionID,
> JobID
> from
> positions p
> left outer join
> applications a
> on (p.PositionID = a.PositionID)
> go
> drop table Applications, positions
> go
>
> Result:
> PositionID JobID
> -- -- --
> 1 25
> * 2 37
> 3 15
> * 12 15
> 18 12
> (5 row(s) affected)
> positionID JobID
> -- -- --
> 1 25
> * 2 37
> 2 37
> NULL 15
> * 12 15
> NULL 12
> (6 row(s) affected)
>
> AMB
>
> "tshad" wrote:
>|||I'd seriously hesitate to use joins on these tables since Primary keys are
not defined, and no uniqueness is guaranteed.
--Untested
SELECT
CASE
WHEN
(
SELECT MAX(a.Applicant) --MAX guarantees 1 return, null if no match
FROM Applications a
WHERE a.PositionID = p.PositionID and a.Applicant = 'tom'
) is not null
THEN '*'
ELSE ''
END --Case
p.PositionID,
p.JobID
FROM
Positions p
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eEVI10WBFHA.1388@.TK2MSFTNGP09.phx.gbl...
> I have the following 2 tables and data:
> CREATE TABLE [Applications] (
> [Applicant] [char] (20) NULL ,
> [PositionID] [int] NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [positions] (
> [PositionID] [int] NULL ,
> [JobID] [int] NULL
> ) ON [PRIMARY]
> go
> insert positions values(1,25)
> insert positions values (2,37)
> insert positions values (3,15)
> insert positions values (12,15)
> insert positions values (18,12)
> insert applications values('tom',2)
> insert applications values('frank',1)
> insert applications values('tom',12)
> insert applications values('larry',2)
> insert applications values('mary',15)
> I want to join the tables to show all the positions and put a star next to
> the ones that 'tom' is in. I tried using an outer join just to get the
> applicant names to show and expected Nulls in the applicants, something
> like:
> select Applicant,a.positionID,JobID from positions p left outer join
> applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
> and wanted:
> null 1 25
> tom 2 37
> null 3 15
> tom 12 15
> null 18 12
> What I got was:
> tom 2 37
> tom 12 15
> I then want to change the name to just show a "*" for name field and get
rid
> of the null (blank).
> 1 25
> * 2 37
> 3 15
> * 12 15
> 18 12
> Can I do this in one select?
> Thanks,
> tom
>|||On Fri, 28 Jan 2005 11:10:41 -0800, tshad wrote:
(snip)
>I want to join the tables to show all the positions and put a star next to
>the ones that 'tom' is in. I tried using an outer join just to get the
>applicant names to show and expected Nulls in the applicants, something
>like:
>select Applicant,a.positionID,JobID from positions p left outer join
>applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
(snip)
Hi Tom,
You were nearly there - just move the test on applicant from the WHERE to
the JOIN clause and select PositionID from the positions table instead of
the applications table and you're set.
(snip)
>I then want to change the name to just show a "*" for name field and get ri
d
>of the null (blank).
> 1 25
>* 2 37
> 3 15
>* 12 15
> 18 12
>Can I do this in one select?
Yes. After making the changes indicated above, use a CASE to change 'tom'
to '*' and NULL to ' '. The end result will look like this:
SELECT CASE WHEN a.Applicant IS NULL THEN ' ' ELSE '*' END,
p.PositionID, p.JobID
FROM positions AS p
LEFT OUTER JOIN applications AS a
ON a.PositionID = p.PositionID
AND a.Applicant = 'tom'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q33ov0dnuntdva9u6ujdrv09gee5oub30c@.
4ax.com...
> On Fri, 28 Jan 2005 11:10:41 -0800, tshad wrote:
> (snip)
> (snip)
> Hi Tom,
> You were nearly there - just move the test on applicant from the WHERE to
> the JOIN clause and select PositionID from the positions table instead of
> the applications table and you're set.
> (snip)
> Yes. After making the changes indicated above, use a CASE to change 'tom'
> to '*' and NULL to ' '. The end result will look like this:
> SELECT CASE WHEN a.Applicant IS NULL THEN ' ' ELSE '*' END,
> p.PositionID, p.JobID
> FROM positions AS p
> LEFT OUTER JOIN applications AS a
> ON a.PositionID = p.PositionID
> AND a.Applicant = 'tom'
That does do it also, without the Join. I just want to make sure here. The
above will do the same as:
select '*', b.PositionID,b.JobID
from Applications as a inner join positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
union all
select '',b.PositionID,b.JobID
from Applications as a right join positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
where a.applicant is null
order by
b.PositionID
Also, why does it matter whether it "applicant='tom'" is in the Join or the
Where clause?
Is it because the all the records are selected first and then everything
that is not ='tom' gets thrown out (including the outer join rows)?
Thanks,
Tom
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)