0

Table scenario

TABLE1

TRAN_NO | SR_NO |MAT_NAME
---------------------------
001     |   1   |Material 1
001     |   2   |Material 2

TABLE2

TRAN_NO | SR_NO | TEMP
-------------------------
 001    |   1   | 10
 001    |   2   | 20
 001    |   3   | 30
 001    |   4   | 40

I want output like this

TRAN_NO | SR_NO |MAT_NAME    | TRAN_NO | SR_NO | TEMP
  001   |   1   | Material 1 |   001   |   1   |  10
  001   |   2   | Material 2 |   001   |   2   |  20
 NULL   | NULL  | NULL       |   001   |   3   |  30
 NULL   | NULL  | NULL       |   001   |   4   |  40

I tried all types of join including LEFT, RIGHT, CROSS but all of them either give matching rows or Cartesian product of them.

Vikram
  • 1
  • 1
  • 1
    share here your queries you tried – Rajen Raiyarela Jan 16 '19 at 06:05
  • 1
    Please try it yourself. Read this https://stackoverflow.com/a/406333/5618563 – Kurt Miller Jan 16 '19 at 06:06
  • 1
    Also; pay attention to the values in SR_NO in Table 2, you can never have an output like that with those values – Kurt Miller Jan 16 '19 at 06:08
  • Possible duplicate of [LEFT JOIN vs. LEFT OUTER JOIN in SQL Server](https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server) – Kurt Miller Jan 16 '19 at 06:08
  • A `CROSS JOIN` would give you all combinations 2 x 4 = 8 rows. Not what you want. Don' use `RIGHT JOIN`, when you want to outer join a table. Right joins do the same thing as left joins, but are harder to read, because you must read the joins backwards. You want to outer join table1 to table2, so use a `LEFT JOIN`. Show what you tried. Where does your attempt fail? And where does SR_NO 4 come from? A typo? – Thorsten Kettner Jan 16 '19 at 06:19
  • Yes it was a type mistake.. – Vikram Jan 16 '19 at 06:28
  • SELECT * FROM TABLE1 RIGHT OUTER JOIN TABLE2 ON TABLE1.SR_NO = TABLE2.SR_NO This query worked..! – Vikram Jan 16 '19 at 06:32
  • There is a TRAN_NO in both tables. Should it be used to join the tables as shown in krunal modi's answer? You should always provide sample data that includes edge cases. In your sample we see the same TRAN_NO / SR_NO pairs in both tables. What if SR_NO 2 had TRAN_NO 123 in table1? How would that change the results? – Thorsten Kettner Jan 16 '19 at 06:40
  • And as mentioned: Don't use `RIGHT [OUTER] JOIN`. Make it a Habit to use `LEFT [OUTER] JOIN` instead. (That is simply `table2 LEFT OUTER JOIN table1 ON …` in your case.) – Thorsten Kettner Jan 16 '19 at 06:46

3 Answers3

0

try this.

select *
from table1 a
right join table2 b on a.sr_no = b.sr_no;
Nick
  • 117
  • 1
  • 9
0

select a., b. from Table_2 b left join Table_1 a on a.TRAN_NO = b.TRAN_NO and a.sr_no = b.sr_no

krunal modi
  • 135
  • 10
0

use left join like below

 select t1.*,t2.* from TABLE2 t2 left join TABLE1 t1 
 on t2.TRAN_NO=t1.TRAN_NO and t2.SR_NO=t1.SR_NO
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63