CODEWITHSUNDEEP
mysql
966p
966p

Reputation: 575

MySQL left join empty result

I have two tables: terems and logs1015.
Need to add data from logs1015 to terems based on similar 'hash' row.

This query works fine if 'SUM(insum)' or 'SUM(outsum) is larger than 0.
But if logs1015 doesn't contain data with such 'hash' then query result is empty.

What the mistake? Thanks

SELECT terems.*, 
    SUM(insum) as firstsum , 
    SUM(outsum) as secondsum 
    FROM terems 
    LEFT JOIN logs1015 USING(hash) 
    WHERE owner='1' 
        AND (type='stat') 
        AND (time BETWEEN 1445904000 AND 1445990400) 
    GROUP BY name 
    LIMIT 1000

Tables structure 

*terems*: id(int),hash(varchar),name(varchar),owner(int)  
*logs1015*: id(int),hash(varchar),type(varchar),insum(int),outsum(int),time(varchar)

Upvotes: 0

Views: 2098

Answers (2)

966p
966p

Reputation: 575

Working solution:

"SELECT t.*, 
        SUM(l.insum) as firstsum , 
        SUM(l.outsum) as secondsum 
        FROM terems t
        LEFT JOIN logs1015 l ON 
            t.hash = l.hash
            AND (l.type='stat') 
            AND (l.time BETWEEN $fromstamp AND $tostamp)
        WHERE t.owner='$userid'    
        GROUP BY t.name 
        LIMIT 1000";

Thanks a lot!

Upvotes: 0

winkbrace
winkbrace

Reputation: 2711

When (left) outer joining, you must put the where clauses on the outer table in the join condition, otherwise you say that it must exist after joining. And then you have implicitly turned it into an inner join.

Also use aliases on your tables so you can easily spot these bugs.

Example:

SELECT t.name, 
    SUM(l.insum) as firstsum , 
    SUM(l.outsum) as secondsum 
FROM terems t
LEFT JOIN logs1015 l ON t.hash = l.hash
                     AND (l.type='stat') 
WHERE t.owner='1' 
    AND (t.time BETWEEN 1445904000 AND 1445990400) 
GROUP BY t.name 
LIMIT 1000

Upvotes: 2

Related Questions