You must Sign In to post a response.
  • Category: SQL Server

    Return nothing when 1 table is empty between 2 table.

    SELECT A.AccID, ISNULL(A.Camt, 0) AS Camt, ISNULL(B.Damt, 0) AS Damt
    FROM

    (SELECT SUM(J.Amount) AS Camt, J.AccID
    FROM dbo.Journal AS J
    INNER JOIN dbo.Voucher AS V ON J.Vid = V.Vid
    WHERE (J.DRCR = 'D' and AccID = 1200181)
    GROUP BY J.AccID) AS A

    full JOIN

    (SELECT SUM(J.Amount) AS Damt, J.AccID
    FROM dbo.Journal AS J INNER JOIN
    dbo.Voucher AS V ON J.Vid = V.Vid
    WHERE (J.DRCR = 'C' and AccID = 1200181)
    GROUP BY J.AccID) AS B ON A.AccID = B.AccID

    this return nothing when table A or table B is Empty.

    i want to return if any table have data. is it possible?
  • #763088
    Hai Rashed Bin Hares,
    The result is correct as per the expectations. If you use inner join, the result will display only when the join is matching. If there is no match in the join condition, there will be no results.
    If you want to get the results, when one table is not having the records, you need to use Left Outer join or right outer join as per the requirement.
    So you need to change your script accordingly.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #763089
    brother why you don't look that i have used full join between table a and b.

  • #763098
    use UNION instead of full join

  • #763100
    Yes, its possible use joins (left/ right) to accomplish your task. Google it for more details of left/ right joins.
    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #763297
    Hello,

    Expected behavior of the above query is correct.Since, the above query acting as an inner join so, if even 1 table out 2 table is empty, it doesn't return anything or return no records.

    If you still want to display the data then, please use OUTER JOIN (i.e. Left or Right Outer Join).

    Thanks


Sign In to post your comments