Left Join Help - 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

General SQL Server Forums

New to SQL Server Programming

Left Join Help

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-07-11 : 23:37:54

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
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-12 : 05:04:15

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

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-07-12 : 05:27:19

Sir,

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

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-12 : 05:37:24

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 09:39:20

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/

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-07-12 : 20:01:43

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 20:18:50

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources