CODEWITHSUNDEEP
phpmysql
Waqas Ahmad
Waqas Ahmad

Reputation: 416

I have done self join in php but it gives wrong result

Here is the DB Structure

-------------------------------------------------------
| id | cat_title |  parent-cat_id | status            |  
-------------------------------------------------------
| 1  | Main      |  null          | enabled           |
| 2  | Child     |  1             | enabled           |
-------------------------------------------------------

I have written this query for Self Joining 

SELECT a.id as ID, a.cat_title as Title, s.cat_title as parent_category
FROM store_categories a
LEFT JOIN store_categories s ON s.parent_cat_id =a.id

But it gives me wrong result like that. because Main didn't have a parent_category and Child have a parent_category of Main. and in result, it shows Main has a parent_category and Child has not.

-----------------------------
| ID | Title |  parent-cat_id|  
-----------------------------
| 1  | Main      |  Child    |
| 2  | Child     |  null     |
-----------------------------

Upvotes: 0

Views: 33

Answers (1)

Nigel Ren
Nigel Ren

Reputation: 57121

The order in which you are linking the two versions of the table is the wrong way round, you just need to change the ON clause...

SELECT a.id as ID, a.cat_title as Title, s.cat_title as parent_category 
    FROM store_categories a 
    LEFT JOIN store_categories s 
       ON a.parent_cat_id =s.id

Upvotes: 2

Related Questions