Need a logic
-
i have posted my Table structure below
DECLARE @Mtbl AS TABLE (id INT,Rid INT, DATA VARCHAR(10))
DECLARE @Ctbl AS TABLE (id INT,Rid INT,Tdata VARCHAR(10))
INSERT INTO @Mtbl
VALUES
(1,1,'test'),(1,2,'test'),(1,3,'test'),
(2,1,'test'),(2,2,'test'),(2,3,'test'),
(3,1,'test'),(3,2,'test'),(3,3,'test'),(3,4,'test'),
(4,1,'test'),(4,2,'test'),(4,3,'test'),(4,4,'test')
INSERT INTO @Ctbl
VALUES
(1,1,'Data1'),(1,2,'Data2'),
(3,1,'Data3'),(3,4,'Data4')
/****************Result would be like this **************************/
DECLARE @Rtbl AS TABLE (id INT,Rid INT, DATA VARCHAR(10),Result BIT)
INSERT INTO @Rtbl
VALUES
(1,1,'Data1',1),(1,2,'Data2',1),(1,3,'test',0),
(3,1,'Data3',1),(3,2,'test',0),(3,3,'test',0),(3,4,'Data4',1)
any idea
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (5/17/2014)
i have posted my Table structure belowDECLARE @Mtbl AS TABLE (id INT,Rid INT, DATA VARCHAR(10))
DECLARE @Ctbl AS TABLE (id INT,Rid INT,Tdata VARCHAR(10))
INSERT INTO @Mtbl
VALUES
(1,1,'test'),(1,2,'test'),(1,3,'test'),
(2,1,'test'),(2,2,'test'),(2,3,'test'),
(3,1,'test'),(3,2,'test'),(3,3,'test'),(3,4,'test'),
(4,1,'test'),(4,2,'test'),(4,3,'test'),(4,4,'test')
INSERT INTO @Ctbl
VALUES
(1,1,'Data1'),(1,2,'Data2'),
(3,1,'Data3'),(3,4,'Data4')
/****************Result would be like this **************************/
DECLARE @Rtbl AS TABLE (id INT,Rid INT, DATA VARCHAR(10),Result BIT)
INSERT INTO @Rtbl
VALUES
(1,1,'Data1',1),(1,2,'Data2',1),(1,3,'test',0),
(3,1,'Data3',1),(3,2,'test',0),(3,3,'test',0),(3,4,'Data4',1)
any idea
Okay, good, except you don't bother to explain how we are supposed to get from sample data to expected results. I realize we could guess at the logic, but it would be nice if you explained it so we are sure to be solving the problem at hand. As simple as it appears to be I have to ask if this is some kind of homework problem for a class.
-
Well, didn't take that long.
DECLARE @Mtbl AS TABLE (id INT,Rid INT, DATA VARCHAR(10))
DECLARE @Ctbl AS TABLE (id INT,Rid INT,Tdata VARCHAR(10))
INSERT INTO @Mtbl
VALUES
(1,1,'test'),(1,2,'test'),(1,3,'test'),
(2,1,'test'),(2,2,'test'),(2,3,'test'),
(3,1,'test'),(3,2,'test'),(3,3,'test'),(3,4,'test'),
(4,1,'test'),(4,2,'test'),(4,3,'test'),(4,4,'test');
INSERT INTO @Ctbl
VALUES
(1,1,'Data1'),(1,2,'Data2'),
(3,1,'Data3'),(3,4,'Data4');
/****************Result would be like this **************************/
DECLARE @Rtbl AS TABLE (id INT,Rid INT, DATA VARCHAR(10),Result BIT)
INSERT INTO @Rtbl
VALUES
(1,1,'Data1',1),(1,2,'Data2',1),(1,3,'test',0),
(3,1,'Data3',1),(3,2,'test',0),(3,3,'test',0),(3,4,'Data4',1);
select * from @Mtbl;
select * from @Ctbl;
select * from @Rtbl;
select
m.id,
m.Rid,
isnull(c.Tdata,m.DATA) as DATA,
case when c.Tdata is not null then 1 else 0 end Result
from
@Mtbl m
left outer join @Ctbl c
on (m.id = c.id and m.Rid = c.Rid)
where
exists(select 1 from @Ctbl c1 where m.id = c1.id);
I would still like to know the logic behind this particular activity.
-
I am out of my system once I get I will explain the rest of the thing and check the result
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
sorry for the delay, quite busy
we are creating data driven dynamic reports, for that we need to store the field name and table names, when ever a user added a table to the report we just add the corresponding report fields form the field table, using a separate screen the user might just create a alias for any fields in any table that he wish to view in the report, now the requirement is there are some default fields to displayed in the reports, if the user changed the alias of any fields must also displayed with the alias he created,
Thanks for the help
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply