simple left join not working! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

simple left join not working!

Author

Topic

rbh123456789
Starting Member

15 Posts

Posted - 2011-12-01 : 13:34:46

select * from
schema.scheduling_availability_overview_people p
left join schema.scheduling_availability_overview o on p.name = o.name

P is a view that has all of the names of people in the organization, lets say 100 rows.
O is the view that has all of the assignment information.

I want ALL the people from table P, regardless of whether or not they have an assignment in the O view

Both views have the NAME field, which is what i am linking on (seen above)

i have ran separate select statements on each view, and they are both working properly.

so why do i only get a subset of records?
Whether i use an left, right or inner, i have get the same number of rows; which is too little.

and ideas????

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-01 : 14:00:28

I am not able to figure out from the information you provided. I tried the following example, which seems to behave the way I would expect it to.

CREATE TABLE #tmp1([name] VARCHAR(32));
CREATE TABLE #tmp2([name] VARCHAR(32));

INSERT INTO #tmp1 VALUES ('a'),('b');
INSERT INTO #tmp2 VALUES ('a');

SELECT * FROM #tmp1 t1 LEFT JOIN #tmp2 t2 ON t1.name = t2.name;

DROP TABLE #tmp1;
DROP TABLE #tmp2;

May be you can simplify it with two test tables and just few rows of data, starting with something as simple as what I posted and try to figure out what is going on?

Edit: You don't have a WHERE clause that has columns from the second table by any chance,do you?

rbh123456789
Starting Member

15 Posts

Posted - 2011-12-01 : 14:13:25

thanks man. ive been working with sql for a couple years now. i am by no means an expert, but this is just so simple.
ive even gone as far as joining on the ID, rather than the name of the person, but i got the same results.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-01 : 14:30:02

quote:
Originally posted by rbh123456789

thanks man. ive been working with sql for a couple years now. i am by no means an expert, but this is just so simple.
ive even gone as far as joining on the ID, rather than the name of the person, but i got the same results.

I'm afraid there is not a lot of information available to provide a solution. If you can provide some sample data and the entire query you used, and tell us which rows are missing, we would be able to help.

rbh123456789
Starting Member

15 Posts

Posted - 2011-12-01 : 14:38:10

I understand. here is what is happening:

Contents of P

Name userID
person1 1
person2 2
person3 3
person4 4
person5 5
person6 6
person7 7

Contents of O

Name userID eventtype
person1 1 sick
person2 2 vacation
person3 3 vacation
person4 4 sick

Results of select

Name userID eventtype
person1 1 sick
person2 2 vacation
person3 3 vacation
person4 4 sick

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-01 : 14:48:38

A simple left join should give you the results like you mentioned. Can you please try running the below and let us know what the output is.

declare @scheduling_availability_overview_people table ([Name] varchar(100), userID int)
insert @scheduling_availability_overview_people
select 'person1',	 1
union all select 'person2',	 2
union all select 'person3',	 3
union all select 'person4',	 4
union all select 'person5',	 5
union all select 'person6',	 6
union all select 'person7',	 7

declare @scheduling_availability_overview table ([Name] varchar(100), userID int, eventtype varchar(50))
insert @scheduling_availability_overview

select 'person1',	 1	 ,'sick'
union all select 'person2',	 2,	 'vacation'
union all select 'person3',	 3,	 'vacation'
union all select 'person4',	 4,	 'sick'

select p.*,coalesce(o.eventtype,'') 
from
@scheduling_availability_overview_people p
left join @scheduling_availability_overview o on p.userID = o.userID

rbh123456789
Starting Member

15 Posts

Posted - 2011-12-01 : 15:20:28

thanks for your help; unfortunatley, same result.
i got the statement working though. now view P is showing all the records, and O is showing the ones that match.

select * from
schema.scheduling_availability_overview_people p
left join
(select * from schema.scheduling_availability_overview) o
on p.userid = o.userid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources