sqlserver query - 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)

sqlserver query

Author

Topic

sqlquery123
Starting Member

4 Posts

Posted - 2006-01-06 : 23:46:12

I am new to sqlserver and also started writing queries.Please help me in getting the output
i have two tables A,B

A:
reg_cd char 2
sid bigint 8

PKY:reg_cd,sid

insert into a values('R3',101)
insert into a values('R5',101)

B:
Sid bigint 8
clt_cd char 2
dur int 4

Pky:sid,clt_cd,dur

insert into a values(101,'01',3)
insert into a values(101,'04',3)

I WANT THE OUTPUT TO BE in two records given below

reg_cd sid dur cltd sid
---------------------------
101 01 3 R3 101
101 04 3 R5 101

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-07 : 00:13:22

[code]select A.reg_cd,
A.sid,
B.dur,
B.clt_cd,
B.Sid
from A
inner join B on A.sid = B.sid[/code]
Now, promise not to post here again until you have read the section on SELECT statements and JOINs in Books Online. The time you spend will be well invested, and afterwards we will be happy to help you with anything you don't understand.

sqlquery123
Starting Member

4 Posts

Posted - 2006-01-07 : 00:30:59

Thanks for your inputs.
Query results four records

101 01 3 R3 101
101 04 3 R3 101
101 01 3 R5 101
101 04 3 R5 101

But i want the output in two records.. given below

101 01 3 R3 101
101 04 3 R5 101

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 06:36:59

Why does

('R3',101) go with (101,'01',3)

and

('R5',101) go with (101,'04',3)

and not, say, the other way round or as per the results from blindman's query?

Kristen

sqlquery123
Starting Member

4 Posts

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

i want the records to be clubbed into two records instead of four records.

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 10:13:56

I understand that, but what defines that

('R3',101) goes with (101,'01',3) and
('R5',101) goes with (101,'04',3)

rather than, say,

('R3',101) goes with (101,'04',3) and
('R5',101) goes with (101,'01',3)

??

Kristen

sqlquery123
Starting Member

4 Posts

Posted - 2006-01-07 : 19:36:36

Yes

('R3',101) goes with (101,'04',3) and
('R5',101) goes with (101,'01',3)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-07 : 22:18:19

Ok. But WHY?
General principle: if you can't describe what you want to do in English, you can describe it in SQL either.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-07 : 23:49:18

Don't quite understand what is the relationship between the table A & B but this will give you want you want

select	B.sid,
	B.clt_cd,
	B.dur,
	A.reg_cd,
	A.sid
from	A
	inner join B 
	on 	A.sid 		= B.sid
	and	A.reg_cd	= case B.clt_cd
					when '04' then 'R3'
					when '01' then 'R5'
					end

-----------------
[KH]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-08 : 12:11:37

Ha! I like it. "When logic fails you, try hard-coding."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-08 : 12:46:38

Maybe this (2005 only):

SELECT A.reg_cd, A.sid, B.dur, C.clt_cd AS cltcd, B.sid
FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY reg_cd, sid) AS rn FROM A ) AS A
FULL OUTER JOIN ( SELECT *, ROW_NUMBER() OVER (ORDER BY sid, clt_cd, dur) AS rn FROM B ) AS B
ON A.rn = B.rn

Kristen
Test

22859 Posts

Posted - 2006-01-09 : 03:21:51

Arnold, you know you've got your ORDER BYs wrong and sqlquery123 wants them ordered by the order they are in the database ... [/thud]

Kristen

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-01-09 : 15:48:14

SQLQuery what everyone is saying is that you've output your "goal" but there is nothing in your "relationships" to explain why that data should be output. In other words what is the relationship between "CLT_CD" and "REG_CD"? Does a CLT_CD of "01" correlate to a value of "R3" in REG_CD somehow? Or did you just pull your desired output of thin air?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources