Do we really need to have "join" every time selecting from multiple tables

Howdy.

I got stuck again on Complex Queries with VIEW 4/10.
I was not sure if everytime when we want to pull columns from the other table, we always need to use join.
Here is my code:

DQ answer key using Inner JOIN.

image

I don’t understand why I misuse the sum() in this case. Thank you for clarification.

I saw from this link https://javarevisited.blogspot.com/2013/08/difference-between-where-vs-having-clause-SQL-databse-group-by-comparision.html
that we do not always have to join two tables if we need to pull multiple columns.

The SQL statement uses GROUP by customer id.

Since GROUP is used, then SQL statement needs HAVING is like a WHERE clause to filter customer spending greater than 90.

SUM will sums all invoice total for this unique customer id.

Therefore, the SUM(i.total) > 90 will only contains customers having more than 90 dollars for all the invoices.

1 Like

When using an aggregate function (sum / count / … ), you also have to make use of the GROUP BY clause.

Next to that, when you want to filter on the result of an aggregate , you cannot do that in the WHERE clause, but you have to do that in the HAVING clause.

1 Like

It depends on how you design your database schema. Since each table represent a type of data, then, most of time, you need to perform some kind of join to answer question or queries.

1 Like

@alvinctk Problem solved!!! Thank you so much for your quick response!!

Here is my code and it works!!! :crazy_face:

@yoyotooie, You are welcome.

We have a solved feature that allows you the ability to mark something as the “correct” answer, which helps future students with the same question quickly find the solution they’re looking for.

Here’s an article on how to mark posts as solved that you can use to help remind students to mark them as solved - I don’t want to do this for you until I know that solution/explanation works.

Best,
Alvin.

1 Like

It helps to understand what exactly the difference between HAVING and WHERE is.

‘WHERE’ clause is used to filter individual rows, before grouping/aggregation has taken place.

‘HAVING’ is used to filter groups, after grouping/aggregation has taken place.

WHERE SUM(i.total) > 90 doesn’t work because you’re applying something that’s used to filter individual rows on an aggregated group.

1 Like

@alvinctk Thank you. got it.

1 Like

@blueberrypudding85 Thank you. It really clarifies my confusion. :smile:

1 Like