Join master and detail tables as one table
I have a table of main tables and detail tables as shown below
Master table
Id code Name
-------------------
1 00 qqq
Detailed table
Id code Name
-------------------
1 01 xyz
1 02 pqr
1 03 abc
1 04 aaa
now i need the result as below
Result
Id code Name
-----------------
1 00 qqq
1 01 xyz
1 02 pqr
1 03 abc
1 04 aaa
I like to avoid UNION
Thank you in advance:)
source to share
Well, I don't know why you don't want to use Union.
But you can use FULL OUTER JOIN if you don't want to use union. something like that -
SELECT COALESCE(Master1.id, Detail.id) AS id
,COALESCE(Master1.code, Detail.code) AS code
,COALESCE(Master1.NAME, Detail.NAME) AS NAME
FROM MASTER1
FULL OUTER JOIN DETAIL ON Master1.code = Detail.code
Output
Id code Name
-----------------
1 00 qqq
1 01 xyz
1 02 pqr
1 03 abc
Check SQL script
Here
source to share
If the table is not large enough, you can create a temp table and insert data from master and detail with
Insert into temp_table select * from detail;
insert into temp_table select * from master;
or if you want you can also use an underrated query to join the table data
insert into main selection * from details;
source to share
There are many ways to achieve this ... you can try creating a temporary table and inserting all the data into it, and then choosing a different kind of material.
Below is a different approach using CTE, let us know if it works for you.
CREATE TABLE #Master
( ID INT, Code VARCHAR(50), Name VARCHAR(50))
CREATE TABLE #Details
( ID INT, Code VARCHAR(50), Name varchar(50))
INSERT INTO #Master values(1,'00', 'qqq')
INSERT INTO #Details values(1,'01', 'xyz')
INSERT INTO #Details values(1,'02', 'pqr')
INSERT INTO #Details values(1,'03', 'abc')
INSERT INTO #Details values(1,'04', 'aaa')
; WITH CTE_RESULT AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CODE, NAME ORDER BY CODE, NAME) ROW_NO, ID, CODE, NAME
FROM #Details D
)
SELECT A.ID, A.CODE, A.NAME FROM CTE_RESULT A
INNER JOIN #Master M ON M.ID=A.ID WHERE ROW_NO=1
source to share