Need help to create a View/Stored Procedure

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

General SQL Server Forums

New to SQL Server Programming

Need help to create a View/Stored Procedure

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2008-06-24 : 05:20:05

Experts

I am trying to create a view or Stored Procedure between different table

Table1 consist of the follwing Fields:

Ref_No: String hold the reference number, Unique
Details: String

Table2:
MasterRefNum : String, not Unique
SubscriberRefNum : String, not Unique

What I am trying to do is that when the user enter a refernece number the system should return back
1- the details where Ref_No = the required refernece number
2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers

Any advice?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 05:23:52

[code]
CREATE PROC YourProc
AS
SELECT t1.*,t3.*
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.MasterRefNum=t1.Ref_No
INNER JOIN Table1 t3
ON t3.Ref_No=t2.SubscriberRefNum
WHERE t1.Ref_No=@RefNo
GO[/code]
Where @RefNo is passed on value of ref no by user
Also replace * by actual columns in select

mmalaka
Starting Member

33 Posts

Posted - 2008-06-24 : 05:36:36

quote:
Originally posted by visakh16

CREATE PROC YourProc
AS
SELECT t1.*,t3.*
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.MasterRefNum=t1.Ref_No
INNER JOIN Table1 t3
ON t3.Ref_No=t2.SubscriberRefNum
WHERE t1.Ref_No=@RefNo
GO

Where @RefNo is passed on value of ref no by user
Also replace * by actual columns in select

Thanks for ur reply, I tried this code and I got the following:
Suppose we found one record with Ref_No = 10 and a matching record in table 2 with SubscriberRefNum = 20 i got a result of 2 records but rpeating the Ref_No = 10 details twice, so I am trying to say I got 2 repeated records where Ref_No = 10; I should get 2 Records Where Ref_No=10 and 20

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 05:42:24

quote:
Originally posted by mmalaka

quote:
Originally posted by visakh16

CREATE PROC YourProc
AS
SELECT t1.*,t3.*
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.MasterRefNum=t1.Ref_No
INNER JOIN Table1 t3
ON t3.Ref_No=t2.SubscriberRefNum
WHERE t1.Ref_No=@RefNo
GO

Where @RefNo is passed on value of ref no by user
Also replace * by actual columns in select

Thanks for ur reply, I tried this code and I got the following:
Suppose we found one record with Ref_No = 10 and a matching record in table 2 with SubscriberRefNum = 20 i got a result of 2 records but rpeating the Ref_No = 10 details twice, so I am trying to say I got 2 repeated records where Ref_No = 10; I should get 2 Records Where Ref_No=10 and 20

didnt get what you're asking. can you illustrate it with sample data?

mmalaka
Starting Member

33 Posts

Posted - 2008-06-24 : 05:49:09

quote:

didnt get what you're asking. can you illustrate it with sample data?

I mean the user will select a Ref_No = 10
The system found on Table1 one record with Ref_No=10 and Details = "Test1"
in Table2 the system found one record with MasterRefNum = 10 and SubscriberRefNum = 20
now the system should look into table 1 to get the record with Ref_No=20 and so the final Result should be 2 records one for Ref_No=10 and the second for Ref_No=20

But
your code is returning 2 duplicated records for Ref_No=10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources