Help with Join

H
I have two tables
Create table company
( firm char(32),
p_name char(40),
proj_code char(8),
proj_start_dt datetime
)
go
insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
go
Create table employee
( firm char(32),
p_name char(40),
proj_end_dt datetime
)
go
insert into employee values ("ABC","John Doe", "Nov 20 2003")
insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
go
I need to extract data for p_name with proj_start_dt > proj_end_dt
my query is not working
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns all values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC John Doe
DJOE Nov 12 2001 12:00AM
ABC Jane Doe
JANEDOE Oct 26 2000 12:00AM
ABC Bruce Smith
BRUCES Mar 1 2002 12:00AM
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c,
employee e
where c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns no values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
Could someone please help.
AjmisterThanks for posting the DDL and sample data. Could you show and explain
exactly what result you want. As far as I can see there are no rows where
Proj_start_dt is greater than Proj_end_dt for any given P_name.
David Portas
SQL Server MVP
--|||try this...
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
where c.proj_start_dt > e.proj_end_dt
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Did you try it? :-)
David Portas
SQL Server MVP
--|||Sorry abt my previous post, that was wrong.
here is the answer,
the company table has the proj_start_dt = Apr 15 2004 which is greater than
the proj_end date.But the value of p_name in the company table is Lisa Cox
which is not there in the employee table.So when you do a join you will not
get the result.
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Your first join is returning all rows because you are doing an outer join
which is telling SQL you want to return all row from the set on the left
(company) even if they don't match any rows in the set on the right
(employee). If you run the following command you can see you are not
returning any values from the employee table:
select c.*, e.*
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
Your second query seems to be doing what you want, and you are not getting
any rows returned because the are no company.proj_start_dt's that are > the
employee.proj_end_dt when the company.p_name and employee.p_name are equal.
Hope this helps explain why you are getting the results you posted.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ajmister" <ajmister@.optonline.net> wrote in message
news:etG3OUXCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>|||Thank you for a quick response, sorry I want to extract all Proj_start_dt
that do not have a proj_end_dt
Thanx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:taednVcaFv9azpzfRVn-sg@.giganews.com...
> Thanks for posting the DDL and sample data. Could you show and explain
> exactly what result you want. As far as I can see there are no rows where
> Proj_start_dt is greater than Proj_end_dt for any given P_name.
> --
> David Portas
> SQL Server MVP
> --
>|||Yes, but it did not return any values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
I would the query to return proj_start_dt whihc do not have proj_end_dt
(sorry about the previous error)
example
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
Thank you
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:yt6dnXskg5uRy5zfRVn-vg@.giganews.com...
> Did you try it? :-)
> --
> David Portas
> SQL Server MVP
> --
>|||Try this:
SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
FROM company AS C
LEFT JOIN employee AS E
ON C.p_name = E.p_name
AND C.proj_start_dt < E.proj_end_dt
WHERE C.proj_start_dt IS NOT NULL
AND E.proj_end_dt IS NULL
David Portas
SQL Server MVP
--|||Thank you sir. That gave me the correct output.
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OIydnb5UiOmFGZzfRVn-iA@.giganews.com...
> Try this:
> SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
> FROM company AS C
> LEFT JOIN employee AS E
> ON C.p_name = E.p_name
> AND C.proj_start_dt < E.proj_end_dt
> WHERE C.proj_start_dt IS NOT NULL
> AND E.proj_end_dt IS NULL
> --
> David Portas
> SQL Server MVP
> --
>