IT Marketing IT Marketing - 3 years ago 160
MySQL Question

SQL Left Join multiplying the values or hide all the values in php/mysql

I have 3 Table, Students, academic_work and orders.



Students Table is Main Table which stores the data of students. we have 2 fields in it.



1- student_id

2- student_email


2nd Table we have academic_work, in which we store all the academic_work records


1- id


2- student_email

3- title

4- price

3rd Table we have order, in which we store all the orders records



1- order_id


2- student_email

3- payer_email

4- item_id

5- item_name

6- payment_amount



Now i want to show the all records on front-office in table like that.


Student Email

Upload (Total Uploads)

Purchase (Total Purchase)

Sale (Total Sale)

Total Upload (Sum of Total Uploads)

Total Purchase (Sum of Total Purchase)

Total Sale (Sum of Total Sale)



My SQL Query

SELECT s.*, s.student_id,
s.student_email, s.price,
COUNT(u.student_email) AS 'uploadCount',
COUNT(b.payer_email) AS 'buyCount',
COUNT(p.student_email) AS 'purchaseCount',
SUM(ut.price) AS 'uploadTotal'
SUM(bt.payment_amount) AS 'buyTotal'
SUM(pt.payment_amount) AS 'purchaseTotal'
FROM students s
LEFT JOIN academic_work u
ON u.student_email = s.student_email
LEFT JOIN orders b
ON b.student_email = s.student_email
LEFT JOIN orders p
ON p.payer_email = s.student_email
LEFT JOIN academic_work ut
ON ut.student_email = s.student_email
LEFT JOIN orders bt
ON bt.student_email = s.student_email
LEFT JOIN orders pt
ON pt.student_email = s.student_email
GROUP BY s.student_id
ORDER BY s.student_id DESC

Answer Source

You have not included comma after uploadtotal, buytotal

Problem:

SELECT s.*, s.student_id, 
           s.student_email, s.price,
           COUNT(u.student_email) AS 'uploadCount',    
           COUNT(b.payer_email) AS 'buyCount',
           COUNT(p.student_email) AS 'purchaseCount',
           SUM(ut.price) AS 'uploadTotal'
           SUM(bt.payment_amount) AS 'buyTotal'
           SUM(pt.payment_amount) AS 'purchaseTotal'

Correct it to as follows

SELECT s.*, s.student_id, 
           s.student_email, s.price,
           COUNT(u.student_email) AS 'uploadCount',    
           COUNT(b.payer_email) AS 'buyCount',
           COUNT(p.student_email) AS 'purchaseCount',
           SUM(ut.price) AS 'uploadTotal',
           SUM(bt.payment_amount) AS 'buyTotal',
           SUM(pt.payment_amount) AS 'purchaseTotal'


$query = "Your query";

mysqli_query($db_conn, $query) or die(mysqli_error($db_conn));

Check with mysqli_error whats the error your getting

Try just using the following query

SELECT u.student_email, COUNT(u.student_email) AS 'uploadCount', SUM(u.price) AS 'uploadTotal' FROM student s LEFT JOIN academic_work u ON u.student_email = s.student_email GROUP BY u.student_email

Hope this will help. If it works for you then will update the query

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download