Compared the column names and values of the result with those of table1 and table2,
I thought that the result might be like...
Code:
ID Name Course Score Code
3 asw BCT 33
3 CBA 200 33
5 BTA 300 33
6 NGT 50 33
If so, union all may be an answer.
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
TABLE1(ID , Name , course , Code) AS (
VALUES
(1 , 'Jo' , 'WBT' , '11')
, (2 , 'saha' , 'ICA' , '22')
, (3 , 'Asw' , 'BCT' , '33')
)
, TABLE2(ID , Score , Course , Code) AS (
VALUES
(2 , 100 , 'ABT' , '22')
, (5 , 300 , 'BTA' , '33')
, (3 , 200 , 'CBA' , '33')
, (6 , 50 , 'NGT' , '33')
)
(
SELECT id
, name
, course
, CAST(NULL AS INT) AS score
, code
FROM table1
WHERE code = 33
UNION ALL
SELECT id
, CAST(NULL AS VARCHAR(3) ) AS name
, course
, score
, code
FROM table2
WHERE code = 33
)
ORDER BY
id
, name
;
------------------------------------------------------------------------------
ID NAME COURSE SCORE CODE
----------- ---- ------ ----------- ----
3 Asw BCT - 33
3 - CBA 200 33
5 - BTA 300 33
6 - NGT 50 33
4 record(s) selected.
Generally speaking, an UNION ALL can be replaced by a FULL OUTER JOIN
(though, it may be not so efficient than union all),
like this...
Example 2:
Code:
SELECT COALESCE(t1.id , t2.id ) AS id
, t1.name
, COALESCE(t1.course , t2.course) AS course
, t2.score
, COALESCE(t1.code , t2.code ) AS code
FROM table1 t1
FULL OUTER JOIN
table2 t2
ON 0 <> 0
WHERE t1.code = 33
OR t2.code = 33
/* 33 IN (t1.code , t2.code) */
ORDER BY
t1.id
, t2.id
;
------------------------------------------------------------------------------
ID NAME COURSE SCORE CODE
----------- ---- ------ ----------- ----
3 Asw BCT - 33
3 - CBA 200 33
5 - BTA 300 33
6 - NGT 50 33
4 record(s) selected.
dBforums
>
Fetching Data from Different table
08-03-11, 07:32
Linear Mode
