CODEWITHSUNDEEP
mysqlsqlleft-join
Дмитрий Яковлев
Дмитрий Яковлев

Reputation: 3

Left Join return not all row

I have two tables: baseresulttables and historyviewdemands.

I want to get the first n entries from baseresulttables, and if there are values for them in historyviewdemands.

But the query works like an inner join.

What am I doing wrong?

baseresulttables

ID    TypeResult
792     1
9397    1
9398    1
9399    1
9400    1

historyviewdemands

ID    BaseResutlTableId
7720    9397
7721    9398
7722    9399
7723    9400
7724    9401

Query:

SELECT 
    bs.ID, hv.Id
FROM
    baseresulttables bs
        LEFT JOIN
    historyviewdemands hv ON (bs.ID = hv.BaseResutlTableId)
WHERE
    bs.`TypeResult` = 1
LIMIT 20 OFFSET 0;

reuslt

bs.ID   hv.Id
9397    7720
9398    7721
9399    7722
9400    7723
9401    7724

Upvotes: 0

Views: 56

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

Using LIMIT without ORDER BY is fairly meaningless, because you aren't telling MySQL which order to use for choosing a certain number of records. Add an ORDER BY clause to query for best results.

SELECT bs.ID, hv.Id 
FROM baseresulttables bs 
LEFT JOIN historyviewdemands hv
    ON bs.ID = hv.BaseResutlTableId
WHERE
    bs.TypeResult = 1
ORDER BY <some column>
LIMIT 20 OFFSET 0;

Upvotes: 1

Related Questions