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.
| Author |
Topic |
|
celetier
Starting Member
1 Post |
 Posted - 2009-04-21 : 10:37:29
|
| Hi!I'm begginer in this... but I have to build a query that mix all data from a single table.I have a lot of data of a lot of items by date..I wrote the next:select D1.noSerial, D1.toner as '%date20', D2.toner as '%date24', D3.toner as '%date28'FROM discovery D1 full Outer Join discovery D2 oN D1.noSerial = D2.noSerial and D1.date<D2.datefull Outer Join discovery D3 oN D1.noSerial = D3.noSerial and D1.date<D3.datewhere D1.date='2009-03-20'and D2.date='2009-03-24'and D3.date='2009-03-28'But when I run the query I only get the serials that are in the three days... but I want that appear all the serials even they only are in one day (of course that in the another day I suposse that will appear as null).Please help me...Thanks a lot. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-21 : 10:47:52
|
| The where clause is turning this into an inner joinNot sure what you want but maybeselect coalesce(D1.noSerial,D2.noSerial,D3.noSerial), D1.toner as '%date20', D2.toner as '%date24', D3.toner as '%date28'FROM discovery D1 full Outer Join discovery D2 oN D1.noSerial = D2.noSerial and D1.date<D2.datefull Outer Join discovery D3 oN D1.noSerial = D3.noSerial and D1.date<D3.datewhere (D1.date='2009-03-20' or d1.Date is null)and (D2.date='2009-03-24' or d2.Date is null)and (D3.date='2009-03-28' or d3.Date is null)Note the coalesce.Suspect this still isn't what you want - try explaining it in words.This won't give you a row if it fulfills the join but doesn't have the correct date.==========================================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. |
 |
|
|
|
|
|
full outer join