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)

Join to a single date rate table?

Given an exchange rate table like:
Create Table XRates
(
EffectiveDate smalldatetime,
FromCury char(3),
ToCury char(3),
Rate float
)
and Transaction table like
Create Table Trans
(
TranDate smalldatetime,
TranCury char(3),
TranAmt float
)
What would be the best SQL query to join from the Transaction table to the
correct exchange rate based on the transaction date? Any sugestions?
Thanks in advanceHere is one way (SQL Server 2005):
WITH Transactions
AS
(SELECT T.TranDate, T.TranCury, T.TranAmt,
R.ToCury, R.Rate,
ROW_NUMBER() OVER(
PARTITION BY R.FromCury
ORDER BY R.EffectiveDate DESC) AS seq
FROM Trans AS T
JOIN XRates AS R
ON T.TranCury = R.FromCury
AND T.TranDate >= R.EffectiveDate)
SELECT TranDate, TranCury, TranAmt, ToCury, Rate
FROM Transactions
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Join Question

Hi
I drop table employee
go
create table employee
( fname char(20),
lname char(36),
dept char(6),
in_dt char(6)
)
insert into employee values ('Joe','Doe','legal','980622')
insert into employee values ('Joe','Doe','legal','990313')
insert into employee values ('Joe','Doe','legal','990704')
insert into employee values ('Joe','Doe','legal','991015')
insert into employee values ('Joe','Doe','legal','000329')
insert into employee values ('Joe','Doe','legal','010503')
drop table work_day
go
create table work_day
(fname char(20),
lname char(36),
dept char(6),
out_dt char(6)
)
insert into work_day values ('Joe','Doe','legal','990228')
insert into work_day values ('Joe','Doe','legal','000617')
insert into work_day values ('Joe','Doe','legal','010407')
select c.fname,
c.lname,
c.dept,
c.in_dt,
e.out_dt
from employee c
left join work_day e
on c.fname = e.fname
and c.lname=e.lname
and c.dept = e.dept
and convert(datetime,c.in_dt) < convert(datetime,e.out_dt)
go
I am getting the multiple records
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 980622
000617
Joe Doe legal 980622
010407
Joe Doe legal 990313
000617
Joe Doe legal 990313
010407
Joe Doe legal 990704
000617
Joe Doe legal 990704
010407
Joe Doe legal 991015
000617
Joe Doe legal 991015
010407
Joe Doe legal 000329
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
and I need the following output
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
Any Suggestions
AjHello,
Thank you for including DDL, sample data and expected result.
However, there are a few problems:
1. Your DDL does not include primary keys (and other constraints)
2. You use char(6) instead of datetime. That's really bad, for (at
least) two reasons:
- performance: converting the values to datetime prevents SQL Server
from using indexes
- data integrity: in a char(6) you can store a value that is not a
valid date and you won't notice until it's too late
3. The expected result... is not quite what I expected. Either the
provided expected result is be wrong or I am unable to understand what
it should contain. If the expected result would have been this:
fname lname dept in_dt out_dt
-- -- -- -- --
Joe Doe legal 980622 990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015 NULL
Joe Doe legal 000329 000617
Joe Doe legal 010503 NULL
Then a possible solution is this:
SELECT fname, lname, dept, in_dt, (
SELECT MIN(out_dt)
FROM work_day e
WHERE e.fname=c.fname and e.lname=c.lname
AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
AND NOT EXISTS (
SELECT *
FROM employee d
WHERE d.fname=c.fname and d.lname=c.lname
AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
)
) AS out_dt
FROM employee c
Razvan|||Thank you, I agree with the char date field but that is what the table was
initially created with and I am extracting data from it. Your script gave
me the output I will looking for.
Aj
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116436704.952637.73930@.g49g2000cwa.googlegroups.com...
> Hello,
> Thank you for including DDL, sample data and expected result.
> However, there are a few problems:
> 1. Your DDL does not include primary keys (and other constraints)
> 2. You use char(6) instead of datetime. That's really bad, for (at
> least) two reasons:
> - performance: converting the values to datetime prevents SQL Server
> from using indexes
> - data integrity: in a char(6) you can store a value that is not a
> valid date and you won't notice until it's too late
> 3. The expected result... is not quite what I expected. Either the
> provided expected result is be wrong or I am unable to understand what
> it should contain. If the expected result would have been this:
> fname lname dept in_dt out_dt
> -- -- -- -- --
> Joe Doe legal 980622 990228
> Joe Doe legal 990313 NULL
> Joe Doe legal 990704 NULL
> Joe Doe legal 991015 NULL
> Joe Doe legal 000329 000617
> Joe Doe legal 010503 NULL
> Then a possible solution is this:
> SELECT fname, lname, dept, in_dt, (
> SELECT MIN(out_dt)
> FROM work_day e
> WHERE e.fname=c.fname and e.lname=c.lname
> AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
> AND NOT EXISTS (
> SELECT *
> FROM employee d
> WHERE d.fname=c.fname and d.lname=c.lname
> AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
> AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
> )
> ) AS out_dt
> FROM employee c
> Razvan
>