-5

Table1 

Date        v1  
05/01/2010  26          
05/02/2010  31   
05/03/2010  50

Table2 

Date        v2 v3  
05/01/2010  42 jkl  
05/02/2010  28 mno  
05/03/2010  12 pqr  
05/04/2010  13 stv  
05/06/2010  48 8965

How can I join the above two tables so that my result look similar to below

Date        v1   v2   v3  
05/01/2010  26   42   jkl   
05/02/2010  31   28   mno  
05/03/2010  50   12   pqr  
05/04/2010   0   13   stv  
05/06/2010   0   48   8965
marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
rakesh
  • 31
  • 4
  • 6
    This is a QA site, why would we send the A to your email. Check here if it does not get closed. – Dustin Laine Oct 15 '10 at 20:24
  • What **version** of SQL Server are you using?? Also: what **datatypes** are those columns?? – marc_s Oct 15 '10 at 21:02
  • 1
    tip: dates make really bad primary and foreign keys, it is never a good idea to join tables with dates. If you follow this tip, it will save you much frustration in the future. – KM. Oct 15 '10 at 21:07

1 Answers1

2

You use the JOIN keyword:

SELECT table2.Date, COALESCE(v1, 0) AS v1, v2, v3
FROM Table2
LEFT JOIN Table1
ON table1.Date = table2.Date

There are different types of join for example:

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

If you don't specify which type of join you want, by default you get an INNER join. It seems here that you want a LEFT JOIN or a FULL OUTER JOIN although it isn't clear which from your question.

See this question for an explanation of the different types of joins:

Community
  • 1
  • 1
Mark Byers
  • 767,688
  • 176
  • 1,542
  • 1,434
  • i tried all the possible joins for this type of output. but i am not able to get the output similar to the one above – rakesh Oct 15 '10 at 20:29
  • I think you'll need a to change v1 to ISNULL(v1,0) v1 – Conrad Frix Oct 15 '10 at 20:31
  • eventhough if i use ISNULL or coalesce the value for v1 is showing as 0 – rakesh Oct 15 '10 at 20:40
  • 1
    @rakesh - COALESCE and ISNULL will both work here. You show dates as the join columns. Are these actually dates with times? If they are, then the columns must be identical, including the time values. – bobs Oct 15 '10 at 20:42
  • 1
    I suggest that you submit a new question, since this one has been closed. Include the data you provided in this question and notice how it is formatted (there's a button in the question editor that formats code). Also, include the query you have now. And, explain that you are not getting the desired results. Also, take a look at the FAQ (see button above) before sending the question. Following the guidelines will help you get more responses. Good luck. – bobs Oct 15 '10 at 20:56
  • 2
    ouch, the OP's introduction to database joins and you throw a `RIGHT JOIN` at them! the OP will be ruined for life ;-o – KM. Oct 15 '10 at 21:01
  • @KM: Changed to LEFT JOIN... is that better? – Mark Byers Oct 15 '10 at 21:02
  • 1
    @Mark Byers, I'm sorry but once the genie is out of the bottle, there isn't any going back, the OP is tainted, ruined for life! ;-) their retinas will have the RIGHT JOIN code burned in them forever.... hee heee heee (+1 for the left join!) – KM. Oct 15 '10 at 21:04