0

I'm using MySQL 5.5 Command Line Client.

What I want:

is this

What I tried last:

SELECT e.event_id,e.title,e.description,e.event_date,e.image, sum(d.amount) as total,count(d.event_id) as total_donors FROM event e,donation d where d.event_id = e.event_id group by e.event_id;

This joins the table but I want the null value as well, How can I modify this to get the last row in the desired results table(in the image attached)?

Also, what is this type of join called?

Thanks.

JustCurious
  • 344
  • 5
  • 15
  • 2
    'what is this type of join called?' - outdated or a comma join. please refer https://dev.mysql.com/doc/refman/8.0/en/join.html for how mysql treats them and this https://stackoverflow.com/questions/20138355/whats-the-difference-between-comma-separated-joins-and-join-on-syntax-in-mysql – P.Salmon Oct 21 '20 at 07:34
  • just do `LEFT JOIN` – FanoFN Oct 21 '20 at 07:39
  • The command line client version isn't relevant. Check the server version with `select version();` – ysth Oct 21 '20 at 08:00

1 Answers1

1

Your query just need to convert using LEFT JOIN like this:

SELECT e.event_id,e.title,e.description,e.event_date,e.image, 
       /*1*/
       sum(ifnull(d.amount,0)) as total,
       count(d.event_id) as total_donors 
FROM event e 
/*2*/
LEFT JOIN donation d 
/*3*/
ON d.event_id = e.event_id 
group by e.event_id;

Observe the following markings in the query above:

/*1*/ add ifnull in the sum operation to return 0 instead of null. This will also prevent null result if one of the value in sum is null.

/*2*/ change comma join to LEFT JOIN particularly because you want all rows from the left table are shown despite having no match in the table on the right.

/*3*/ change where to ON.

Here is a fiddle demo: https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=723cc34b7b875111701d9134b443f39b

FanoFN
  • 6,815
  • 2
  • 13
  • 33