Connect two tables to MySQL



  • There are two types of tables.

    Table #1 - client

    id  status (varchar)  login (varchar)   password (char)
    

    1 enabled qwerty wrbujhwprijp;ji
    2 enabled wjkb w;rbjowrobi
    3 disabled vorochenko wpribjhbrkw;jbn

    Table #2 - balance

    id  id_client  date(date)        amount(decimal(8,2))

    1 1 2015-04-04 100.00
    2 1 2015-04-04 -40.00
    3 2 2015-04-04 -50.00
    4 3 2015-04-04 80.00
    5 2 2015-04-04 120.00
    6 1 2015-04-04 -45.00

    It requires:
    Each client needs to receive the sum of his balance, the date of operation, the status of the client is not important.
    The client may not have any records of the transactions in general, but he must be present in the report.
    Example of field report

    idclient (int)   amount (decimal)
    1 15.00
    2 70.00
    3 80.00
    4 Null

    I made this request:

    SELECT idclient (int), SUM(amount) FROM balance GROUP BY idclient (int)

    but it doesn't work, because it doesn't count to four.



  • Use it. http://dev.mysql.com/doc/refman/5.7/en/join.html ♪ Specifically, in your case, you need to add all the records from the table. clientwhich do not even have balance♪ It's enough to add to your request. RIGHT JOIN

    SELECT
        c.`id` AS `id_client`,
        SUM(b.`amount`) AS `sum`
    FROM `balance` AS b
    RIGHT JOIN `client` AS c 
        ON b.id_client = c.id
    GROUP BY c.`id`;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2