select all the related data. - 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 2008 Forums

Transact-SQL (2008)

select all the related data.

Author

Topic

anthor
Starting Member

14 Posts

Posted - 2012-08-30 : 13:03:20

User_ID Phone Number AddBy_UserID
1 0194523638 0
2 1122334455 1
3 1122334456 1
4 1111111111 2
5 222222222 1
6 222222223 4

let's see user_ID 6 data,
user ID 6 is add by user ID 4,
User ID 4 is add by user ID 2,
User ID 2 is add by user ID 1.

so,i want to select all the relationship user by USER ID 6,
mean i want to display the data who having the same chain.
what i want see the result is show the User ID 6,4,2,1,because they are having the relationship.

ATG
Starting Member

35 Posts

Posted - 2012-08-30 : 13:20:30

Not sure I understand what you're trying to do. The only significance I see about 6,4,2,1 is that the AddBy_UserID's are new for each of those numbers. Are you trying to group them?

If so, try a "GROUP BY" at the end of your statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 16:06:33

quote:
Originally posted by anthor

User_ID Phone Number AddBy_UserID
1 0194523638 0
2 1122334455 1
3 1122334456 1
4 1111111111 2
5 222222222 1
6 222222223 4

let's see user_ID 6 data,
user ID 6 is add by user ID 4,
User ID 4 is add by user ID 2,
User ID 2 is add by user ID 1.

so,i want to select all the relationship user by USER ID 6,
mean i want to display the data who having the same chain.
what i want see the result is show the User ID 6,4,2,1,because they are having the relationship.

you should be using recursively CTE for this



DECLARE @UserID int
SET @UserID = 6--change it to any value you like


;With CTE
AS
(
SELECT *
FROM Table
WHERE User_ID = @UserID

UNION ALL

SELECT t.*
FROM CTE c
INNER JOIN Table t 
ON t.User_ID = c.AddBy_UserID
)

SELECT *
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anthor
Starting Member

14 Posts

Posted - 2012-08-30 : 16:09:39

problem solved, using the CTE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 16:11:37

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anthor
Starting Member

14 Posts

Posted - 2012-08-31 : 05:58:46

USE [DB_Flexi]
GO
/****** Object: StoredProcedure [dbo].[UpdateUserAmount] Script Date: 08/30/2012 14:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[UpdateUserAmount]
@Topup_Amount decimal(18,2),
@AccNo int
--@Balance INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

WITH Dependencies AS(SELECT * FROM Tbl_Account WHERE AccNo = 8 UNION ALL SELECT t.* FROM Tbl_Account t JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)
SELECT Balance,Balance,0,0,'',GETDATE(),2 FROM Dependencies

-- Insert statements for procedure here
INSERT INTO Tbl_Transaction(Before_Amount,After_Amount,Amount,Transaction_AccNo,Remark,Transaction_Date,TransType_ID)
SELECT Balance,Balance+@Topup_Amount,@Topup_Amount,@AccNo,'',GETDATE(),2 FROM Dependencies

UPDATE Tbl_Account Set Balance=Balance-@Topup_Amount WHERE AccNo=@AccNo
END

but,how to insert the data like this?

anthor
Starting Member

14 Posts

Posted - 2012-08-31 : 06:03:49

problem solve,should be like this
-- Insert statements for procedure here
WITH Dependencies AS(SELECT * FROM Tbl_Account WHERE AccNo = 8 UNION ALL SELECT t.* FROM Tbl_Account t JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)
INSERT INTO Tbl_Transaction(Before_Amount,After_Amount,Amount,Transaction_AccNo,Remark,Transaction_Date,TransType_ID)
SELECT Balance,Balance+@Topup_Amount,@Topup_Amount,@AccNo,'',GETDATE(),2 FROM Dependencies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources