May 6, 2011 at 7:37 am
HI,
table -1
vijay BE ECE
table-2
vijay ECE a
vijay CSE b
vijay MEC c
now i want a query to obtain the below result
vijay BE ECE a
vijay BE CSE b
vijay BE MEC c
OR
vijay BE ECE a
vijay BE CSE null
vijay BE MEC null
May 6, 2011 at 7:49 am
Not sure why you'd want to do that, but you can achieve it with a CROSS JOIN.
John
May 6, 2011 at 8:42 am
Those tables are awful. . . what is the purpose of this?
--Test data, two simple tables since you didn't supply DDL
DECLARE @table1 AS TABLE (col1 CHAR(5), col2 CHAR(2), col3 CHAR(3))
INSERT INTO @table1
SELECT 'vijay', 'BE', 'ECE'
DECLARE @table2 AS TABLE (col1 CHAR(5), col2 CHAR(3), col3 CHAR(1))
INSERT INTO @table2
SELECT 'vijay', 'ECE', 'a'
UNION ALL SELECT 'vijay', 'CSE', 'b'
UNION ALL SELECT 'vijay', 'MEC', 'c'
Few examples that produce the results you want: -
--As John suggested, Cross Join for the first result set
SELECT a.col1, a.col2, b.col2, b.col3 FROM @table1 a
CROSS JOIN @table2 b
SELECT b.col1, a.col2, b.col2, b.col3 FROM @table1 a
LEFT OUTER JOIN @table2 b ON a.col3 = b.col2
UNION ALL
SELECT b.col1, a.col2, b.col2, NULL FROM @table1 a
LEFT OUTER JOIN @table2 b ON a.col3 <> b.col2
SELECT a.col1, a.col2, c.col2, CASE WHEN b.col2 IS NULL
THEN NULL
ELSE a.col3 END AS col3
FROM @table2 a
LEFT OUTER JOIN @table1 b ON a.col2 = b.col3
CROSS JOIN @table1 c
SELECT a.col1, a.col2, c.col2, NULLIF(ISNULL(NULLIF(ISNULL(b.col3,c.col2),b.col3),a.col3),c.col2) AS col3
FROM @table2 a
LEFT OUTER JOIN @table1 b ON a.col2 = b.col3
CROSS JOIN @table1 c
Without more information and DDL, I'm not sure you'll get much else as an answer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply