how can i get this kind of output

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

SQL Server 2000 Forums

SQL Server Development (2000)

how can i get this kind of output

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-07-27 : 02:11:15

Hai,
i have two tables. both the table i have some id column to relate with .
but in one table i have 3 records for on id ..in another table i have 5 records for the same id..now i need to get all the 8 records..
but sometime the records in both the table might be got the exactly same value...so i can i solve this. for better understanding i have simulated the problem here. I dont want to use union...is there any way to solved this using join only. help pls

DECLARE @Teacherinfo TABLE
(
TEACHER_ID INT,
STUDENTNAME VARCHAR(10)
)
INSERT INTO @TEACHERINFO
SELECT 1,'STU1' UNION ALL
SELECT 1,'STU2' UNION ALL
SELECT 1,'STU2' UNION ALL
SELECT 2, 'STU3'

DECLARE @STUDENTinfo TABLE
(
STUDID INT,
TEACHERID INT,
STUDENTNAME VARCHAR(10)
)

INSERT INTO @STUDENTinfo
SELECT 1,1,'JOHN' UNION ALL
SELECT 2,1,'STU2' UNION ALL
SELECT 3,1,'JOSE' UNION ALL
SELECT 4,1, 'STU3'

TECHERID SNAME
1 STU1
1 STU2
1 JOHN
1 STU3
1 JOSE

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-27 : 02:28:10

DECLARE @Teacherinfo TABLE
(
TEACHER_ID INT,
STUDENTNAME VARCHAR(10)
)
INSERT INTO @TEACHERINFO
SELECT 1,'STU1' UNION ALL
SELECT 1,'STU2' UNION ALL
SELECT 1,'STU2' UNION ALL
SELECT 2, 'STU3'

DECLARE @STUDENTinfo TABLE
(
STUDID INT,
TEACHERID INT,
STUDENTNAME VARCHAR(10)
)

INSERT INTO @STUDENTinfo
SELECT 1,1,'JOHN' UNION ALL
SELECT 2,1,'STU2' UNION ALL
SELECT 3,1,'JOSE' UNION ALL
SELECT 4,1, 'STU3'

TECHERID SNAME
1 STU1
1 STU2
1 JOHN
1 STU3
1 JOSE

please check n confirm the o/p

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-07-27 : 02:34:29

YEs, I have corrected it..Sorry for the mistake

DECLARE @Teacherinfo TABLE
(
TEACHER_ID INT,
STUDENTNAME VARCHAR(10)
)
INSERT INTO @TEACHERINFO
SELECT 1,'STU1' UNION ALL
SELECT 1,'STU2' UNION ALL
SELECT 1,'STU2' UNION ALL
SELECT 1, 'STU3'

DECLARE @STUDENTinfo TABLE
(
STUDID INT,
TEACHERID INT,
STUDENTNAME VARCHAR(10)
)

INSERT INTO @STUDENTinfo
SELECT 1,1,'JOHN' UNION ALL
SELECT 2,1,'STU2' UNION ALL
SELECT 3,1,'JOSE' UNION ALL
SELECT 4,1, 'STU3'

TECHERID SNAME
1 STU1
1 STU2
1 JOHN
1 STU3
1 JOSE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 08:31:04

Why dont you use Union?

Madhivanan

Failing to plan is Planning to fail

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-07-27 : 22:28:52

Actully as i have many thousands of records in the table, "union" takes few seconds to retrieve the data.
But time being i have introduced Table variable to reduce this retrival time. it is quite helpful. but if i get better idea than this, that would be better. Actully im doing the Consolidated Report of my Application . So im facing quite lot of performance issue.

quote:
Originally posted by madhivanan

Why dont you use Union?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-27 : 23:47:10

try this


select	distinct coalesce(t.TEACHER_ID, s.TEACHERID), coalesce(s.STUDENTNAME, t.STUDENTNAME)
from	@TEACHERINFO t full join @STUDENTinfo s
	on	t.TEACHER_ID	= s.TEACHERID
	and	t.STUDENTNAME	= s.STUDENTNAME

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-28 : 01:28:05

khtan, can u please explain what COALESCE does? what is its functionality? i wants to know more about this function.

thanks in advance,

Mahesh

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-28 : 07:10:51

Books On Line has a full definition of COALESCE and examples of how to use it.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-28 : 09:06:36

quote:
COALESCE
Returns the first nonnull expression among its arguments.

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression

Is an expression of any type.

n

Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Return Types
Returns the same value as expression.

Remarks
If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to this CASE function:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-29 : 01:21:43

Thanks KHTan,

suppose i have A, B, C, D, E, F expressions. Out of these
B & D are non null and remaining are nulls.

If i use COALESCE(A, B, C, D, E, F), will it return only ->B or -> B & D.(I know, u have mentioned first non null expr.) . Now If I want to return all the non null exprs, among the exprs, which functions should i use?

thanks in advance,

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-29 : 05:33:58

Why dont you check like this?

Select id from
(
Select 'A' as id, A as Expression
union all
Select 'B', B
union all
Select 'C', C
union all
Select 'D', D
) T
where Expression is not null

Madhivanan

Failing to plan is Planning to fail

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-29 : 09:07:46

yes Madhi,

its nice one. but i wanted to know is there anything like COALESCE which returns every non null exprs among the exprs, as COALESCE return only first non null exprs among the expr.

Thanks for updation,

Mahesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-29 : 09:16:07

COALESCE is a scalar function. It only returns one value. If you required more than one value to return, you can create your own Inline function using the Madhivanan's method.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-29 : 09:23:59

thanks both of u

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources