Question 63982 in PhpMyAdmin Question posted in
The official documentation can be found here.

Select Between Two Dates and from Two Tables in PHP and MySQL – Phpmyadmin

Please I need help with the right query for this query below…

SELECT 
    compute_num, 
    SUM(`shar_cr`) AS shar, 
    SUM(`thri_cr`) AS thri, 
    SUM(`vol_cr`) AS volun 
FROM `member_transacting` 
    WHERE date BETWEEN '$from_date' AND '$end_date' 
RIGHT JOIN regist_members ON member_transacting.compute_num=regist_members.m_compute_num 
ORDER BY date DESC

Basically, I want to select the sum of values in database table between two dates. And at the same time, I also want to fetch the same users information from the registered members table using their common identifier, which in this case is their computer numbers.

Expected Result

I want to be able to get the summation of values from member_transacting table plus also get grab the members name and phone number from the regist_members table.

I have tried out the above query on phpmyadmin but it didn’t work. Instead it threw error, that the query is wrong. Please help.

MY PROGRESS THUS FAR
The Code Below From Professor Abronsius’ Guide Works For Me

I just had to tweak it by adding GROUP BY and also requested specific field(full_name)from the regist_members table

    SELECT t.compute_num,
        m.full_name,     
        SUM( t.`shar_cr`) AS shar, 
        SUM( t.`thri_cr`) AS thri,
        SUM( t.`vol_cr`) AS volun
        FROM `member_trans` t
    JOIN `regist_members` m ON t.`compute_num`=m.`m_computer_no`
    WHERE t.`date` BETWEEN '$from_date' AND '$end_date' 
    GROUP BY t.`compute_num` 
    ORDER BY t.`date` DESC

Even though the above code already gives me about 90% of what I want, I can’t post it yet as answer because I still need a little help for it to be perfect.

THE HELP I NEED

Granted that the above code works well; it fetches the required results from both tables; but if I request more than one data from the regist_members table (e.g m.full_name, m.member_bank_name) at once it doesn’t work. It only works if I leave the code as it is above. It doesn’t allow me request for more data than what is in the above code. Please someone should guide.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Finally I was able to get my exact desired result from with Code Below...

               SELECT t.compute_num,
                        m.full_name,     
                        SUM( t.`shar_cr`) AS shar, 
                    SUM( t.`thri_cr`) AS thri,
                    SUM( t.`vol_cr`) AS volun,
                     m.member_bank AS bank_name,
                     m.member_account AS account_no
                    FROM `member_trans` t
                JOIN `regist_members` m ON t.`compute_num`=m.`m_computer_no`
                WHERE t.`date` BETWEEN '$from_date' AND '$end_date' GROUP BY t.`compute_num` ORDER BY t.`date` DESC

  2. 0

    I think you need GROUP BY here:

    SELECT 
    `member_transacting`.`compute_num`, 
    SUM(`shar_cr`) AS shar, 
    SUM(`thri_cr`) AS thri, 
    SUM(`vol_cr`)  AS volun 
FROM `member_transacting` 
JOIN `regist_members`
    ON `member_transacting`.`compute_num` = `regist_members`.`m_compute_num` 
WHERE `date` BETWEEN '$from_date' AND '$end_date' 
GROUP BY `member_transacting`.`compute_num`
ORDER BY `member_transacting`.`compute_num` DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search