Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – sql query to get Result Table from Table A and Table B

Please check tables below and help to solve.
Table A

a_id name dept a_date amount
123 Clark Sales 2024-01-01 5000
124 Clark Sales 2024-01-02 6000
125 John Sales 2024-01-03 3500
126 John Sales 2024-01-04 5500

Table B

b_id name dept b_date amount status type
2001 Clark Sales 2024-02-05 1000 Paid Loan
2002 Clark Sales 2024-02-06 1000 Unpaid Loan
2003 Clark Sales 2024-02-08 5000 Paid Loan
2004 Clark Sales 2024-02-09 1000 Unpaid Adv
2005 John Sales 2024-02-11 1000 Unpaid Loan

Result Table for Name "Clark"

Date Amount
2024-01-01 +5000
2024-01-02 +6000
2024-02-05 -1000
2024-02-08 -5000
Total +5000
  • Row 01 and 02 in Result Table -> name = Clark from Table A
  • Row 03 and 04 in Result Table -> status = ‘Paid’ AND type = ‘Loan’ AND name = ‘Clark’ from Table B

I have tried coalesce and many other solutions here but I am unable to achieve this.

Thanks

I have tried coalesce, inner join, CASE and many other solutions here but I am unable to achieve this.

Tags:

2

Answers


  1. 0

    According to the criteria described in your question, I suggest a UNION.

    SELECT a_date as `Date`
      ,amount
  FROM table_a
 WHERE name = 'Clark'
UNION
SELECT b_date as `Date`
      ,amount
  FROM table_b
 WHERE name = 'Clark'
   AND status = 'Paid'
   AND type = 'Loan'

    Note that DATE is a reserved word in MySQL, hence in the above code it is enclosed in backticks.

    Login or Signup to reply.
  2. 0

    Something like this:

    SELECT 
    a_date as `Date`,
    amount
FROM table_a
WHERE name = 'Clark'
UNION
SELECT 
   b_date as `Date`,
   amount
FROM table_b
WHERE name = 'Clark' AND status = 'Paid' AND type = 'Loan'

    About the negative values, on your description you have only positive values. If you are storing positive values and want to use PAID UNPAID to make this as credit or debit, you can use the IF function

    IF(status == 'Unpaid', amount*-1, amount)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search