adding some rows to a select

Hi folks,

I've a sql query problem I was wondering if you all had a quick and
dirty solution for. I've a query:

Select code, value from table_a where date in
(2004) and a_code in ('1000','2000') and b_code in ('01000','02000')

This returns a table that looks like:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750

What I'd like to see is:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750
2000 01000 $0
2000 02000 $0

Any suggestions on how to rewrite my query so the results show A_CODE
2000 with a VALUE of 0 or null?

Thank much in advance!

MarcMarc (brownjenkn@.aol.com) writes:
> I've a sql query problem I was wondering if you all had a quick and
> dirty solution for. I've a query:
> Select code, value from table_a where date in
> (2004) and a_code in ('1000','2000') and b_code in ('01000','02000')
> This returns a table that looks like:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> What I'd like to see is:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> 2000 01000 $0
> 2000 02000 $0
> Any suggestions on how to rewrite my query so the results show A_CODE
> 2000 with a VALUE of 0 or null?

CREATE TABLE a_code (a_code char(4) NOT NULL
CREATE TABLE b_code (b_code char(5) NOT NULL

go
INSERT a_code (a_code) VALUES ('1000')
INSERT a_code (a_code) VALUES ('2000')
INSERT b_code (b_code) VALUES ('01000')
INSERT b_code (b_code) VALUES ('02000')
go
SELECT a.a_code, b.b_code, coalesce(t.value, 0)
FROM (a_code a
CROSS JOIN b_code b)
LEFT JOIN table_a t ON a.a_code = t.a_code
AND b.b_code = t.b_code
ABD t.date = '2004'

Here I am handling a_code and b_code in the same way, so you will
get output for missing b_codes as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp