December 9, 2009 at 10:41 am
I know this may be simple but I just cant get the desired results.
I have a table tbl(A) with a list of all trailer types. And I have another table tbl(B) with all the trailers on order.
I need to show all the trailers from tbl(A) and a count of trailers from tbl(B)
So:
tbl(A)
Model
a
b
c
d
e
f
g
h
i
j
tbl(B)
Model
a
a
b
g
i
i
i
i
How can I get the results to show all tbl(A) with counts from tbl(B)
Desired results from above tables:
Model Count
a 2
b 1
c 0
d 0
e 0
f 0
g 1
h 0
i 4
j 0
Thank you for you help.
December 9, 2009 at 10:50 am
try an inner join
select tbla.model, count(tblb.model)
from tbla inner join tblb on tbla.model = tblb.model
or something similar , post your exact tables for more precise code
December 9, 2009 at 10:57 am
Actually, you want a LEFT OUTER JOIN.
create table dbo.TblA (
Model char(1)
);
create table dbo.TblB (
Model char(1)
);
insert into dbo.TblA
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f' union all
select 'g' union all
select 'h' union all
select 'i' union all
select 'j';
insert into dbo.TblB
select 'a' union all
select 'a' union all
select 'b' union all
select 'g' union all
select 'i' union all
select 'i' union all
select 'i' union all
select 'i';
select
a.Model,
count(b.Model)
from
dbo.TblA a
left outer join dbo.TblB b
on a.Model = b.Model
group by
a.Model
order by
a.Model;
December 9, 2009 at 12:55 pm
Awesome... Thank you very much... More arsenal for growing bag of SQL knowledge.
December 9, 2009 at 1:01 pm
Here is a little more knowledge that will be beneficial here on SSC. Read the first article I reference below in my signature block regarding asking for assistance, then look at how I posted my response. Much of what I provided you should provide, it makes it easier for those of us trying to help and you get tested code in return.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply