May 16, 2012 at 9:43 am
I have two tables:
t1 (dow smallint)
t2 (custId int, dow smallint, data1 varchar(128), data2 float)
I then insert 7 records into t1, one for each day of the week (1-7).
Now, I want to query these tables so that I will always get 7 records from my SELECT statement. Here is the query:
declare @CUSTID int
select @CUSTID = 1
select
b.custId,
a.dow,
b.data1,
b.data2
from
t2 b
left outer join t1 a on a.dow = b.dow
where
b.custId = @CUSTID
When I have no data in t2, I still want to get 7 rows of NULLS. This query, however, returns nothing. Is there a way I can do this?
May 16, 2012 at 9:46 am
james.jensen1350 (5/16/2012)
I have two tables:
t1 (dow smallint)t2 (custId int, dow smallint, data1 varchar(128), data2 float)
I then insert 7 records into t1, one for each day of the week (1-7).
Now, I want to query these tables so that I will always get 7 records from my SELECT statement. Here is the query:
declare @CUSTID intselect @CUSTID = 1
select
b.custId,
a.dow,
b.data1,
b.data2
from
t2 b
left outer join t1 a on a.dow = b.dow
where
b.custId = @CUSTID
When I have no data in t2, I still want to get 7 rows of NULLS. This query, however, returns nothing. Is there a way I can do this?
Change the LEFT OUTER JOIN to a RIGHT OUTER JOIN.
Or swap t1 and t2 in the FROM clause.
May 16, 2012 at 9:55 am
The table to the left of the keyword LEFT OUTER JOIN will always be kept in the result set irrespective of any matches found or not in the other table. So instead of
this
declare @CUSTID int
select @CUSTID = 1
select
b.custId,
a.dow,
b.data1,
b.data2
from
t2 b
left outer join t1 a on a.dow = b.dow
where
b.custId = @CUSTID
use this
declare @CUSTID int
select @CUSTID = 1
select
b.custId,
a.dow,
b.data1,
b.data2
from
t1 a
left outer join t2 b on a.dow = b.dow
where
b.custId = @CUSTID
If you watch carefully i hve switched the postitions of t1 a and t2 b.
You can use RIGHT OUTER JOIN, if u want all of the right tables data.
declare @CUSTID int
select @CUSTID = 1
select
b.custId,
a.dow,
b.data1,
b.data2
from
t2 b
right outer join t1 a on a.dow = b.dow
where
b.custId = @CUSTID
If you analyse, i have not swtiched the positions of tables in the query, instead used RIGHT OUTER JOIN, which brings back all the data from the right table.
Hope it is clear for you now!
May 16, 2012 at 9:59 am
Thanks for the reply Lynn. I tried that and id did not work.
However, I did just now try a FULL OUTER join and moved the condition in the where clause to the join statement. It worked then.
select
@CUSTID as "custId",
a.dow,
b.data1,
b.data2
from
t1 a
full outer join t2 b on a.dow = b.dow
and b.custId = @CUSTID
Predictably, I found the hint at the Microsoft MSDN site.
May 16, 2012 at 10:03 am
james.jensen1350 (5/16/2012)
Thanks for the reply Lynn. I tried that and id did not work.However, I did just now try a FULL OUTER join and moved the condition in the where clause to the join statement. It worked then.
select
@CUSTID as "custId",
a.dow,
b.data1,
b.data2
from
t1 a
full outer join t2 b on a.dow = b.dow
and b.custId = @CUSTID
Predictably, I found the hint at the Microsoft MSDN site.
I didn't look real close at your where clause. Yes, the way you wrote the where clause would turn the outer joins into a inner join. If you switcth the full outer join back to a left outer join it should still work with and b.custid = @CUSTID as part of the join criteria.
May 16, 2012 at 2:51 pm
Steve Jones - SSC Editor (5/16/2012)
A few outer join resources:
Thanks, Steve. All references are welcome with open arms.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply