mysqli left join duplicate row

Question

table com

id | com       | id_status  
1    testing 1   1  
2    testing 2   1  
3    testing 3   2  
4    testing 4   2

table update

id_status | update  
1           update 1  
2           update 2  
3           update 3  


SELECT `update`.`update`, `com`.`com` as comen
FROM `update` 
    LEFT JOIN `com` ON `update`.`id_status`=`com`.`id_status`

it results :

update 1  
testing 1  
update 1  
testing 2  
update 2  
testing 3  
update 2  
testing 4

the update table results duplicate

i've done using group by update.id_status, it results :

update 1  
testing 1  
testing 2  
update 3

the table com only resut 1 row

EDIT--
i got syntax from this MySQL LEFT JOIN display duplicate rows

$first = true;  
while($row = $query->fetch_object()){  
if($first){  
    echo $row->update;  
    $first = false;  
    echo "<br>";  
}  
echo $row->comen;  
echo "<br>";  
}  

update 1  
testing 1  
testing 2  
testing 3  
testing 4

it fetch only 1 table row

i want the results looks like this :

update 1  
testing 1  
testing 2  
update 2  
testing 3  
testing 4  
update 3  
...

--

how the right syntax work or maybe the query?

http://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram - this will help you understand what joins do. Further to this https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ <--- a better diagram with data. — Trent, Mar 02 '17 at 01:40
i've seen it before... in fact i've take the pic to learn it. it used left join.. and i've read this post http://stackoverflow.com/questions/23301264/mysql-left-join-display-duplicate-rows if $first = true it's only fetch 1 table row — faddi, Mar 02 '17 at 01:47
That question relates to PHP, not SQL. The fact is, you have two fields in your select - the SQL query is going to return every combination that it has that matches your selection criteria (where clause). — Trent, Mar 02 '17 at 01:55
so i've edit the question. you might want to explain it with the code, i kinda confused with text. — faddi, Mar 02 '17 at 02:10
@faddi can you format your result so it is easy to understand. — Rakesh Kumar, Mar 02 '17 at 09:00

score 0 · Answer 1 · answered Mar 02 '17 at 07:43

0

Use INNER JOIN instead of left join

SELECT `update`.`update`, `com`.`com` as comen
FROM `update` INNER JOIN `com` ON `update`.`id_status`=`com`.`id_status`;

or can use DISTINCT keyword to avoid duplicate results

SELECT DISTINCT `com`.`id_status`, `update`.`update`, `com`.`com` as comen
FROM `update` LEFT JOIN `com` ON `update`.`id_status`=`com`.`id_status`;

answered Mar 02 '17 at 07:43

Sateesh

1,327
9
12

i've also tried DISTINCT and it doesn't work wonder why? here's the pic different queries name and tables but same result http://s61.photobucket.com/user/codename_faddi/media/try_zpsms10e3bf.png.html?filters[user]=146246261&filters[recent]=1&sort=1&o=0 – faddi Mar 02 '17 at 08:14

Mithu CN · Answer 2 · 2017-03-02T08:58:09.650

0

According to your query , the result should have ok. Let have a look that the table com contain two value those id_status=1.

Now if you leftjoin com table with update table then the query will give 2 row corresponding id_status=1 because com table contain two row which id_status is 1.

if you want to get only any one row accroding to id_status then you have to use group by update.id-status or group by com.id-status

 SELECT `up`.`up`, `com`.`com` as comen FROM `up` LEFT JOIN `com` ON `up`.`id_status`=`com`.`id_status` group by up.id_status

I used up instead of update because update is a keyword. Table:

Result:

edited Mar 02 '17 at 08:58

answered Mar 02 '17 at 08:07

Mithu CN

605
5
11

yes, i've done it using group by only fetch 1 row of table com but not all... i'm kinda frustating with such noob question – faddi Mar 02 '17 at 08:23
Could you tell again what do u want actually ? – Mithu CN Mar 02 '17 at 08:30
i want to fetch all everything loop each 1 row table update with each all table com. some say loop a query is an expensive way – faddi Mar 02 '17 at 08:42
Unfortunately, it is ok from my side. I don't know what problem is going on in your ? I executed the query in my mysql server . See the update query and result which i executed . – Mithu CN Mar 02 '17 at 08:53
here's my code http://i61.photobucket.com/albums/h48/codename_faddi/try2_zpszw8zhikd.png?t=1488360365 . need to fetch all com in each update – faddi Mar 02 '17 at 09:27

score 0 · Accepted Answer · answered Mar 02 '17 at 09:54

Try This, I think this will resolve:-

SELECT `update`.`update`, group_concat(`com`.`com`) as comen
FROM `update` 
    LEFT JOIN `com` ON `update`.`id_status`=`com`.`id_status` 
Group by `update`.`update`

and use your php as like:-

while($row = $query->fetch_object()){  
    echo $row->update;  
    $comen = explode(",", $row->comen);
    foreach($comen as $values){
     echo "<br>";  
     echo $values;
    }
    echo "<br>";  
}

mysqli left join duplicate row

3 Answers3