Why INNER JOIN dont not work correct?

Question

I have one SQL query with INNER JOINS. I need to get all offers from table offers.

Table offers is empty now. But the following query returns one row with NULL field.

Why is it returned? How to fix that? I need to return 0 rows if table is empty.

Query:

 select *, SUM(offers.price * announcement_product.amount) AS total, announcements.user_id AS creator_ann, announcements.id AS ann_id, announcements.delivery AS deliveryAnn, announcements.payment AS paymentAnn, SUM(announcement_product.amount) AS amount, announcement_product.name as name_product from `offers` inner join `announcements` on `announcements`.`id` = `offers`.`announcement_id` inner join `announcement_product` on `offers`.`announcement_product_id` = `announcement_product`.`id` inner join `countries` on `countries`.`id` = `announcements`.`country` where `offers`.`user_id` = 1 and `offers`.`status` = 1 and `offers`.`deleted_at` is null

O. Jones · Accepted Answer · 2017-04-16 20:25:11Z

You're using the aggregate function SUM(), but you don't have any GROUP BY clause.

When you do that you are instructing MySQL to add up all the row values in the column you mention in SUM(). It will do that even if there are no rows to add up.

For best results you should study up on the GROUP BY function and how to use it with SUM(). It's hard to guess what you want from your query.

LLL · Accepted Answer · 2017-04-16 20:25:26Z

I'm not sure, but I don't think

select *, ..

when there's multiple tables in the query is valid.

Try

select offers.*,..

Arin Vartazarian · Accepted Answer · 2017-04-16 20:45:20Z

This how Your select structure should be : Select Id, Sku, Sum(Onhand), Sum(price) From mytable Where mytable Onhand > 0 Group by Id,Sku If you are going to use aggregate function such as Max,Sum,Min,.... you need to use group by for other table fields that your using in the select part.

Collectives™ on Stack Overflow

Why INNER JOIN dont not work correct?

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related