problem with join - SQL Server Forums

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

Transact-SQL (2000)

problem with join

Author

Topic

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-09-30 : 04:43:20

I have following doubts on join condition

Table 1 primary key id,sub

Name id sub marks

xxx 61 maths 45

xxx 61 science 50

another table primary key id,language

id language write

61 english yes

61 Hindi no

Output:

Xxx 61 maths 45 english yes

Xxx 61 maths 45 Hindi no

Xxx 61 science 50 english yes

Xxx 61 science 50 hindi no

how to join these tables to get

every information in 2 rows will it possible

xxx 61 maths 45 English yes

xxx 61 science 50 hindi no

From:

N Salmon Raju

Métis Info sol

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-30 : 04:46:28

Somthing like this


Select a.Name,a.Id,a.Sub,a.Marks,b.language,b.write From 
Table1 a Cross Join Table2 b

Chirag

rammohan
Posting Yak Master

212 Posts

Posted - 2006-09-30 : 04:55:39

hi solamn how are you?

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-09-30 : 05:53:49

hi its not the answer i am expecting i want a solution, is there any way to minimise the row count with all the required feilds

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-30 : 08:04:47

quote:
Originally posted by rammohan

hi solamn how are you?

This is not a chat forum

quote:

hi its not the answer i am expecting i want a solution, is there any way to minimise the row count with all the required feilds

What do you mean by minimizing the Rowcount??
Try to post the some more details..

BTW, did you tried the query which i posted, i guess its display as per your expected output


Declare @table1 Table 
(
	Name varchar(10),
	id int,
	sub varchar(10),
	marks int
)

Declare @table2 Table 
(
	id int,
	language  varchar(10),
	write  varchar(10)
)

Insert @Table1 
Select 'xxx',61,'maths',45
Union All 
Select 'xxx',61,'science',50

Insert @Table2 
Select 61,'english','yes' 
Union All 
Select 61,'Hindi','no'



Select a.Name,a.Id,a.Sub,a.Marks,b.language,b.write From 
@Table1 a Cross Join @Table2 b 

--Output 
Name       Id          Sub        Marks       language   write      
---------- ----------- ---------- ----------- ---------- ---------- 
xxx        61          maths      45          english    yes
xxx        61          science    50          english    yes
xxx        61          maths      45          Hindi      no
xxx        61          science    50          Hindi      no

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources