Left Outer Joining A Table with empty Table

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

General SQL Server Forums

New to SQL Server Programming

Left Outer Joining A Table with empty Table

Author

Topic

vkkishore_s
Starting Member

5 Posts

Posted - 2010-03-31 : 00:52:08

Dear All

Can any body tell me how to left outer join a table with empty table

in my query i am join a table employee emp_code is a primary key

with AmcAssignedEmployee on right i am getting a result set if there is no condition in where clause .if i keep a condition in where clause every thing failed

employee table
emp_code,emp_name ,emp_lname
-----------------------------
1 abcd xyz
2 sss ijk

AmcAssignedEmployee table blank table
emp_code ,assignId
------------------ --- 0 records

------------------

my query is

select e.emp_code As EmpId,AssignedAMCID from employee E
full outer join AmcAssignedEmployee AE
on e.emp_code=AE.emp_code

Now if i insert one row in AmcAssignedEmployee as below
AmcAssignedEmployee table blank table
emp_code ,assignId
------------------ --- 0 records
1 1
2 NULL
3 NULL
4 NULL
... So On
------------------
SELECT e.emp_code As EmpId,AssignedAMCID from employee E
full outer join AmcAssignedEmployee AE
on e.emp_code=AE.EmpCode

upto here its working fine, but now if i use the below lines everything is failed (i want to get the records whose assignId is not 1)

where AE.AssignedAMCID<>1

Please advise me

Thanks and Regards
Krishna Kishore

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-31 : 00:59:49

Do you mean the AmcAssignedEmployee table is empty?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vkkishore_s
Starting Member

5 Posts

Posted - 2010-03-31 : 02:00:08

Yes,AmcAssignedEmployee is not having any records

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-31 : 02:07:53

what is your expected result?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-31 : 02:26:44

quote:
Originally posted by vkkishore_s

Yes,AmcAssignedEmployee is not having any records

Why you want to join an empty table? and how u think it can possible?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-31 : 02:28:41

If you want an empty column you can try this

select *,null as empty_record from table_name

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

vkkishore_s
Starting Member

5 Posts

Posted - 2010-03-31 : 02:41:22

Thanks senthil thanks for reply.

My second table is not an empty table (this is not my question)
i want to check the value of a column which is a null column after doing left outer join .
My only question is i am gettting null values for un matched records .

i want to get the records beased on the below condition
AssignedAMCID =1 (1 is for matching records)
or
AssignedAMCID <> 1 (i should get the unmatched records, other than the records having one)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-31 : 03:35:51

This "where AE.AssignedAMCID<>1" converts the query into an "inner join"
convert this statement into an "ON" clause.

Kristen
Test

22859 Posts

Posted - 2010-03-31 : 03:58:34

"AssignedAMCID <> 1 (i should get the unmatched records, other than the records having one)"


(AssignedAMCID <> 1 OR AssignedAMCID IS NULL)

maybe?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources