SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Left Join Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gerald30
Starting Member

Philippines
46 Posts
Posted - 07/11/2012 :  23:37:54  Show Profile  Reply with Quote
Hello All,

Good after noon.

Can you help me out. I have2 tables with left joins

Lets Say table 1 is the Item Item qty , Date and Shift.

The other table is for the receive quantity, Item, date and shift.

Can I do some case when in Left join condition that if it will retrieve some records on table 1 it will consider the table1.shift = table2.shift and if no records dis regard the condition?

Thank you

Grifter
Posting Yak Master

214 Posts
Posted - 07/12/2012 :  05:04:15  Show Profile  Reply with Quote
If you run below code you should see the results all from table 1 and data from table 2 where it matches, otherwise null will be returned from table 2 where there is no match


select t1.shift, t2.shift
from table1 t1 left join table2 t2 on t1.shift = t2.shift
Edited by - Grifter on 07/12/2012 05:04:40
Go to Top of Page

Gerald30
Starting Member

Philippines
46 Posts
Posted - 07/12/2012 :  05:27:19  Show Profile  Reply with Quote
Sir,

I have tried that 1 but when the table 1 has no records while table 2 has,
It shows no record at all.
Go to Top of Page

Grifter
Posting Yak Master

214 Posts
Posted - 07/12/2012 :  05:37:24  Show Profile  Reply with Quote
If table 2 is your master table then you need to have either right join in query above or put the table 2 first in the left join.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 07/12/2012 :  09:39:20  Show Profile  Reply with Quote
quote:
Originally posted by Gerald30

Sir,

I have tried that 1 but when the table 1 has no records while table 2 has,
It shows no record at all.


so you want records from both table regardless of match in other?
then you should be using full join

select columns...
from table1 t1
full join table2 t2
on t2.shift = t1.shift


or if you want table2 to be base table with matches from table1 if present use


select columns...
from table2 t2
left join table1 t1
on t2.shift = t1.shift



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Gerald30
Starting Member

Philippines
46 Posts
Posted - 07/12/2012 :  20:01:43  Show Profile  Reply with Quote
Thank you sirs,

I will try to use full join.

Table 1 should still be the base table.

I want to show records weather is has a match in table 1 and table 2.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 07/12/2012 :  20:18:50  Show Profile  Reply with Quote
quote:
Originally posted by Gerald30

Thank you sirs,

I will try to use full join.

Table 1 should still be the base table.

I want to show records weather is has a match in table 1 and table 2.

full join means it will return from both tables regardless of match in other

suggest you to learn join basics

http://www.w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000