Select Between Two Dates and from Two Tables in PHP and MySQL-CodePudding

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.

CodePudding user response：

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` 
WHERE `date` BETWEEN '$from_date' AND '$end_date' 
JOIN `regist_members`
    ON `member_transacting`.`compute_num` = `regist_members`.`m_compute_num` 
GOUP BY `member_transacting`.`compute_num`
ORDER BY `member_transacting`.`compute_num` DESC

CodePudding user response：

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