left outer join query - 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 outer join query

Author

Topic

jameswoodmancy
Starting Member

13 Posts

Posted - 2007-11-21 : 05:16:15

I have a table that contains prices

tblPrices

customerId customername yearnumber monthnumber price
1 smith 2004 1 455
1 Smith 2004 2 321
1 Smith 2004 3 3455

etc...

for each month

then

1 smith 2005 1 4545
1 smith 2005 2 49

for each year up until 2007.

also i have about 20 customers

2 jones 2004 1 34345
2 jones 2004 1 354345

etc...

i have a customers table with about 25 customers in

customerid customername
1 smith
2 jones
3 blogs

etc...

basically i want to create a join that will join the two tables on customer id but giving the following results.

1 smith 2004 1 455
1 Smith 2004 2 321
1 Smith 2004 3 3455
etc..
2 jones 2004 1 34345
2 jones 2004 2 354345

and then blogs who isnt in the tblPrices i want to show null values for each year and each month number so

3 blogs 2004 1 null
3 blogs 2004 1 null

if i do a left outer join i get one value

3 blogs null null null

and then onto the next customer

4 woods 2004 1 333

anyone know how to do this join?

thanks for your help

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-21 : 06:23:26

select c.CustomerID, c.CustomerName, d.yearnumber, d.monthnumber, t.price
(select distinct CustomerID, CustomerName from tblPrices) c
cross join
(select distinct yearnumber, monthnumber from tblPrices) d
left join tblPrices t
on c.CustomerID = t.CustomerID
and d.yearnumber = t.yearnumber
and d.monthnumber = t.monthnumber

You should have a customer and date table so that you don't need to get the values from tblPrices.
tblPrices should just have the CustomerID, date and price.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jameswoodmancy
Starting Member

13 Posts

Posted - 2007-11-21 : 07:07:40

hi, thanks!

they seems to be something with the syntax though and i cant figure out what

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'd'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 07:23:06

[code]
select c.CustomerID, c.CustomerName, d.yearnumber, d.monthnumber, t.price
from
(
select distinct CustomerID, CustomerName from tblPrices
) c
cross join
(
select distinct yearnumber, monthnumber from tblPrices
) d
left join tblPrices t
on c.CustomerID = t.CustomerID
and d.yearnumber = t.yearnumber
and d.monthnumber = t.monthnumber
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources