CODEWITHSUNDEEP
phpmysqlleft-join
user1372430
user1372430

Reputation:

Mysql left join returns more than one row

I have 3 tables and some fields' names are the same. Here is the first table named semp:

enter image description here

The second one's name semp_k:

enter image description here

And the third is semp_y:

enter image description here

You see, the main table is the first and the others are related it. The first table has got 3 row. So when I fetch it, it must return 3 row. But when I fetch the first table, it multiples returned rows with sum of second and third table. Here is my code:

SELECT s.*, k.*, y.* FROM semp AS s LEFT JOIN semp_k AS k ON s.no = k.semp_no LEFT JOIN semp_y AS y ON s.no = y.semp_no WHERE s.durum = 1 ORDER BY s.bas_t DESC

Upvotes: 0

Views: 86

Answers (2)

ydlgr
ydlgr

Reputation: 57

You need to use group by. Your query should be like this ; 

SELECT
    s.*, k.*, y.*
FROM
    semp AS s
LEFT JOIN semp_k AS k ON s. NO = k.semp_no
LEFT JOIN semp_y AS y ON s. NO = y.semp_no
WHERE
    s.durum = 1
GROUP BY s.no
ORDER BY
    s.bas_t DESC

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

use MySQL group by

group by s.no

or try this :-

SELECT
s.*, k.*, y.*
FROM
semp AS s
LEFT JOIN semp_k AS k ON s. NO = k.semp_no
LEFT JOIN semp_y AS y ON s. NO = y.semp_no
WHERE
s.durum = 1
GROUP BY s.no
ORDER BY
s.bas_t DESC

Upvotes: 1

Related Questions