April 18, 2011 at 12:01 pm
Hi,
I have two tables, which are different with unequal number of columns (4 vs 1 column).
And I want to create a view, which will take the 4 columns from the first table and 1 column from the other table.
I create it in a normal way:
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).
Please, if anyone has a solution, I appreciate it very much!
Thank you!
April 18, 2011 at 12:18 pm
the problem is not with the veiw but rahter the select. When you have more than one table involved you have to tell it how it relates to the other. There is alot more to it than that but for the simple terms you have to create a one to one relationship or a one to one relationship. In this case it sounds like you may have a many to many relationship or you have a one to many relationship when you expected a one to one relationship.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 18, 2011 at 12:21 pm
that's the problem, since the tables are unequal and with different columns, BUT with same number of rows.
So, there is no option just take values of each row from the 2 tables?
Do you have any concrete solution for this situation?
Thank you!
April 18, 2011 at 12:26 pm
there is not enough information to create a concrete solution. How does the data relate to one another? For eample do they both have the same ID field. knowing nothing about your data I can not say for sure other than to say that you can not join two compeltely unrelated tables in a veiw. If there is no relationship between the data what is the perpose of using them in the veiw together? It is that relationship in data that is the key.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 18, 2011 at 12:30 pm
is there any identity column in both table
select c1,c2,c3,(select top 1 c1 from table2) from table1
if you dont have any column which can be join then it must be an identity column to process next value as
I assume c1 is an identity column in both tables
select t1.c1,t1.c2,t1.c3,(select t2.c2 from table2 t2 where t2.c1 = t1.c1) from table1 t1
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 18, 2011 at 12:35 pm
asterman_3 (4/18/2011)
that's the problem, since the tables are unequal and with different columns, BUT with same number of rows.So, there is no option just take values of each row from the 2 tables?
Do you have any concrete solution for this situation?
Thank you!
Asterman,
As Dan mentioned, if the data has no relation, and you don't use an ON clause, you end up with a CROSS JOIN. You've seen the results of a cross join (every row in one table repeated for every single row in the other). If you can't relate the data, you need to generate a relation to cure your ills.
The following method is not recommended unless you absolutely know that there is no relation and you are doing some kind of trickery simply for personal display use only:
;with cte AS (select row_number() OVER (ORDER BY <somefield>) AS rn, columns1-4 FROM tbl1),
with cte2 AS ( SELECT row_number() OVER (order by <somefield>) AS rn, column1 FROM tbl2)
SELECT
c1.col1, c1.col2, c1.col3, c1.col4, c2.col1
FROM
cte AS c1 JOIN cte2 AS c2 ON c1.rn = c2.rn
Again, don't deploy this, it's just an example and a workaround while you determine what you actually should be linking the data between the tables with.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 18, 2011 at 12:38 pm
OK.
The situation is more complicated.
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
Is there any solution for this situation?
Thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply