Shalvika's Query, Sql Server

Shalvika's Query

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

CREATE TABLE TABLE1(reportName varchar(20), fieldName varchar(20));
CREATE TABLE TABLE2(reportName varchar(20), fieldName varchar(20));

INSERT INTO TABLE1 values('r1', 'f1');
INSERT INTO TABLE1 values('r1', 'f2');
INSERT INTO TABLE1 values('r1', 'f3');

INSERT INTO TABLE1 values('r9', 'f91');
INSERT INTO TABLE1 values('r9', 'f92');
INSERT INTO TABLE1 values('r9', 'f93');
--INSERT INTO TABLE1 values('q1', 'f4');

INSERT INTO TABLE2 values('r1', 'f1');
INSERT INTO TABLE2 values('r1', 'f2');
INSERT INTO TABLE2 values('r1', 'fc');

INSERT INTO TABLE2 values('r9', 'f91');
INSERT INTO TABLE2 values('r9', 'f92');
INSERT INTO TABLE2 values('r9', 'f9c');
--INSERT INTO TABLE2 values('q1', 'fd');
--INSERT INTO TABLE2 values('q1', 'fe');

--SELECT * FROM TABLE1;
--SELECT * FROM TABLE2;

--Find total field Names.
--For each table, print reportName and list all fields. Print Null if not present

/*SELECT t2.reportName,t1.fieldName
FROM TABLE1 t1 
RIGHT OUTER JOIN 
(SELECT reportName,fieldName FROM TABLE1
 WHERE reportName='r1'
 UNION 
 SELECT reportName,fieldName from TABLE2
  WHERE reportName='r1') t2
ON t1.fieldName=t2.fieldName

UNION ALL

SELECT t2.reportName,t1.fieldName
FROM TABLE2 t1 
RIGHT OUTER JOIN 
(SELECT reportName,fieldName FROM TABLE1
 WHERE reportName='r1'
 UNION 
 SELECT reportName,fieldName from TABLE2
  WHERE reportName='r1') t2
ON t1.fieldName=t2.fieldName */

SELECT t2.reportName,t1.fieldName
FROM TABLE1 t1 
RIGHT OUTER JOIN 
(SELECT reportName,fieldName FROM TABLE1
 
 UNION 
 SELECT reportName,fieldName from TABLE2
  ) t2
ON t1.fieldName=t2.fieldName

UNION ALL

SELECT t2.reportName,t1.fieldName
FROM TABLE2 t1 
RIGHT OUTER JOIN 
(SELECT reportName,fieldName FROM TABLE1
 
 UNION 
 SELECT reportName,fieldName from TABLE2
  ) t2
ON t1.fieldName=t2.fieldName


run \| edit \| history \| help	0