mysql - Mysql Query Gives Duplicate Rows

Question

SELECT 
        tc.expense AS Expense
        ,tc.`tour_sub_code`
        ,tc.login_id
        ,tc.currency
        ,tc.amount
        ,tc.date
        ,tc.from_date
        ,tc.to_date
        ,tc1.NoOfDays
        ,tc1.NoOfDays*tc.amount totalAmount

        FROM tc_wallet tc ,

       (SELECT 
        DATEDIFF(
          STR_TO_DATE(to_date, '%Y-%m-%d'),
          STR_TO_DATE(From_date, '%Y-%m-%d')
        ) NoOfDays 
      FROM
        tc_wallet 
      WHERE expense = 'DAILY ALLOWANCE' 
        AND login_id = 'sunny.thangaraj') AS tc1 

        WHERE tc.login_id = 'sunny.thangaraj'
        AND tc.expense = 'DAILY ALLOWANCE'       

In the above query i am selecting data from same table twice
1) Individual column data
2) DateDiff between columns of same table and using it to calculate total amount

but it is giving me output twice ,

OUTPUT

DAILY ALLOWANCE TCEB161017  sunny.thangaraj USD 50  12-02-2018  2018-12-08  2018-12-10  2   100
DAILY ALLOWANCE TCEB161017  sunny.thangaraj USD 50  12-02-2018  2018-12-08  2018-12-10  2   100
DAILY ALLOWANCE TCEB010915  sunny.thangaraj USD 2   17-01-2014  2018-12-08  2018-12-10  2   4
DAILY ALLOWANCE TCEB010915  sunny.thangaraj USD 2   17-01-2014  2018-12-08  2018-12-10  2   4

Like TOURSUBCODE TCEB010915,TCEB161017 comes 2 times which is same and is not desired.
Can anyone tell me what wrong am i doing here ?
Is it because i am doing Select From A,(Select From A)

标签： mysqlsqldatabase

Answer 1

您可以使用DISTINCT

SELECT  DISTINCT
        tc.expense AS Expense
        ,tc.`tour_sub_code`
        ,tc.login_id
        ,tc.currency
        ,tc.amount
        ,tc.date
        ,tc.from_date
        ,tc.to_date
        ,tc1.NoOfDays
        ,tc1.NoOfDays*tc.amount totalAmount

        FROM tc_wallet tc ,

       (SELECT 
        DATEDIFF(
          STR_TO_DATE(to_date, '%Y-%m-%d'),
          STR_TO_DATE(From_date, '%Y-%m-%d')
        ) NoOfDays 
      FROM
        tc_wallet 
      WHERE expense = 'DAILY ALLOWANCE' 
        AND login_id = 'sunny.thangaraj') AS tc1 

        WHERE tc.login_id = 'sunny.thangaraj'
        AND tc.expense = 'DAILY ALLOWANCE'       

http://www.mysqltutorial.org/mysql-distinct.aspx