Create SQL Views from 2 different unequal tables!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create SQL Views from 2 different unequal tables!

Author

Topic

asterix5
Starting Member

6 Posts

Posted - 2011-04-19 : 02:52:10

I am working with sql audit logs which I need to extract specific data (specific columns) and save them in text files.
Since from the SQL Server Audit Logs the "ID row" can not be determined from the logs (ex. UPDATE .. where lastname="test" - from this I can not determine the ID row) , I am extracting the required data by combining audit logs with CHANGE DATA CAPTURE (which track the ID of each action logged).

So, I have in one side the table from the audit logs, and on the other side the table from the CHANGE DATA CAPTURE table.

The goal is to capture 4 columns from the first table, and 1 column from the other one.

They have same number of rows, but different columns (logs vs CHANGE DATA CAPTURE table).

For the moment I created simple view from these 2 tables, as the code below:

create view vw_ViewLogs
as
select
Column1, Column2, Column3, Column4, Column1
FROM
db..TBL1, db..TBL2

But the problem is that the view creates much more rows, it multiplies rows of each table (ex. 4*4=16 rows instead of 4 rows) - Cross Join problem.

Is there any solution when there are 2 tables with different column values?

Thank you!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-19 : 06:06:17

Try this -


SELECT A.Column1, A.Column2, A.Column3, A.Column4, B.Column1 FROM 
(
SELECT	Column1, Column2, Column3, Column4, ROW_NUMBER() OVER ( ORDER BY (SELECT 1) ) RowNo 
FROM	Tbl1
) A
INNER JOIN 
(
SELECT	Column1, ROW_NUMBER() OVER ( ORDER BY (SELECT 1) ) RowNo 
FROM	Tbl2
) B 
ON A.RowNo = B.RowNo

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources