August 30, 2012 at 11:08 am
User_ID Phone Number AddBy_UserID
101945236380
211223344551
311223344561
411111111112
52222222221
62222222234
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.
August 30, 2012 at 11:16 am
Something like this?
DECLARE @TableTable(
User_ID int,
Phone_Numbervarchar(20),
AddBy_UserIDint)
INSERT @Table
SELECT 1, '0194523638', 0 UNION ALL
SELECT 2, '1122334455', 1 UNION ALL
SELECT 3, '1122334456', 1 UNION ALL
SELECT 4, '1111111111', 2 UNION ALL
SELECT 5, '222222222 ',1 UNION ALL
SELECT 6, '222222223 ',4;
WITH Dependencies AS(
SELECT user_id, AddBy_UserID
FROM @Table
WHERE User_id = 6
UNION ALL
SELECT t.User_ID, t.AddBy_UserID
FROM @Table t
JOIN Dependencies d ON t.User_ID = d.AddBy_UserID
)
SELECT User_ID
FROM Dependencies
I'm not sure if it's the best way, but it's one way to do it.
August 30, 2012 at 11:36 am
Great job man ! that's what i want....
that's damn difficult....
August 30, 2012 at 1:44 pm
It shouldn't be difficult and you must understand it before you use it.
Read about Recursive CTEs.
If you have a specific question, don't hesitate to ask it.
August 30, 2012 at 2:09 pm
Luis Cazares (8/30/2012)
It shouldn't be difficult and you must understand it before you use it.Read about Recursive CTEs.
If you have a specific question, don't hesitate to ask it.
thanks.understood~
August 31, 2012 at 4:00 am
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?
August 31, 2012 at 4:04 am
problem solved,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
August 31, 2012 at 7:23 am
Great, remember to remove the static values and replace them with variables.
Also, it's better if you list the columns you need instead of using the asterisk (*)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply