left join problem ... 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

SQL Server 2005 Forums

Transact-SQL (2005)

left join problem ... Help

Author

Topic

kasabb
Starting Member

8 Posts

Posted - 2009-04-09 : 11:28:02

I am trying to do what I thought would be a simple left join but I am not getting the desired results. I must be thinking of this the wrong way.
I have a table of values that I am doing a count on and grouping it by the description. If no results are returned for a specific descrition then
I still need that description to be in the results with a count of zero.

--begin sql for example
declare @lookupTable table
(
[code] int,
[lkpDescription] varchar(50),
[type] int
)

insert into @lookupTable
select 1, 'Description 1A', 1
union
select 2, 'Description 1B', 1
union
select 3, 'Description 2A', 2
union
select 1, 'Description 2B', 2

declare @valuesTable table
(
[fname] varchar(20),
[lname] varchar(20),
[deScription] varchar(20),
[year] char(4)
)

insert into @valuesTable
select 'joe', 'jones', 'Description 1A', '2009'
union
select 'jill', 'jones', 'Description 1A', '2009'
union
select 'janet', 'jones', 'Description 2A', '2009'
union
select 'john', 'jones', 'Description 2B', '2009'

SELECT
[lookup].lkpDescription,
count(*) as descCount
FROM
@lookupTable [lookup]
LEFT JOIN @valuesTable tbl
on rtrim(tbl.Description) = rtrim([lookup].lkpDescription)
where
tbl.[year] = 2009
group by
[lookup].lkpDescription
--begin sql for example

--begin output example
-- Here is the output that I am getting
Description 1A 2
Description 2A 1
Description 2B 1

-- Here is the output that I am trying to get
Description 1A 2
Description 2A 1
Description 2A 0
Description 2B 1
--end output example

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-09 : 12:01:38

Jones family got problems, I think it is giving you exactly what is in the database and what the query asks for.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-04-09 : 12:12:52

May be this is what you are looking for, but even this does not match your desired output because the data in the tables does not support your desired output.

SELECT 
   [lookup].lkpDescription, 
   count(tbl.description) as descCount
FROM 
   @lookupTable [lookup]
   LEFT JOIN @valuesTable tbl on 
      rtrim(tbl.Description) = rtrim([lookup].lkpDescription) 
      and tbl.[year] = 2009
group by
   [lookup].lkpDescription

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-10 : 02:09:03

Hi,
try this with same data
select distinct l.lkpDescription,(select count(*) from @valuesTable a where a.[deScription]=l.lkpDescription) as sss
from @lookupTable l
left outer join @valuesTable v
on v.[deScription]=l.lkpDescription

Kunal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-10 : 06:58:50

also see
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources