Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    15

    Unanswered: select all the relationship

    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.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with CTE as
    (
        select * from MyTable 
        where User_ID = 6
        
        union all
        
        select t.*
        from MyTable as t
        JOIN CTE as c on c.AddBy_UserID = t.User_ID
    )
    
    select * from CTE
    Hope this helps.

  3. #3
    Join Date
    Aug 2012
    Posts
    15
    Quote Originally Posted by imex View Post
    Try:

    Code:
    with CTE as
    (
        select * from MyTable 
        where User_ID = 6
        
        union all
        
        select t.*
        from MyTable as t
        JOIN CTE as c on c.AddBy_UserID = t.User_ID
    )
    
    select * from CTE
    Hope this helps.
    thanks dude,problem solved with CTE

  4. #4
    Join Date
    Aug 2012
    Posts
    15
    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,TransTyp e_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?

  5. #5
    Join Date
    Aug 2012
    Posts
    15
    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,TransTyp e_ID)
    SELECT Balance,Balance+@Topup_Amount,@Topup_Amount,@AccNo ,'',GETDATE(),2 FROM Dependencies

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •