CODEWITHSUNDEEP
mysqljoinleft-join
Dustin Scott Garza
Dustin Scott Garza

Reputation: 130

LEFT JOIN pulling only one result

MY PROBLEM

I'm joining 2 tables...nav & subnav

Only one result shows from subnav when it should be 2 or more results.

You can View my Problem HERE : http://aquiestoy.mx/demo/tempone/indexnew.php ( the top gray accordian menu )

MY CODE

SELECT nav.name AS name, 
nav.href, 
nav.nav_id, 
subnav.name AS subname, 
subnav.nav_id AS parent 

FROM nav LEFT OUTER JOIN subnav
ON nav.nav_id = subnav.nav_id 

GROUP BY nav.name
ORDER BY nav.position, subnav.position

MY TABLES

Table Name "NAV"
+-------------+-------------+---------+----------+-----------+
| nav_id      | name        | href    | position | client_id |
+-------------+-------------+---------+----------+-----------+
| 10          | Home        | home    | 11       | 56        |
| 11          | Products    | about   | 11       | 56        |
| 12          | Promotions  | about   | 11       | 56        |
| 13          | two         | about   | 11       | 56        |
+-------------+-------------+---------+----------+-----------+

Table Name SUBNAV 
+-------------+--------+---------+----------+--------+-----------+
| subnav_id   | name   | href    | position | nav_id | client_id |
+-------------+-------------+---------+----------+---+-----------+
| 1           |        | page    | 1        | 11     |    56     |
| 2           | Page1  | page    | 2        | 11     |    56     |
| 3           | Page2  | page    | 3        | 11     |    56     |
| 4           | Page3  | page    | 4        | 11     |    56     |
+-------------+--------+---------+----------+--------+-----------+

As you can see it is pulling

Upvotes: 0

Views: 30

Answers (1)

segFault
segFault

Reputation: 4054

Your GROUP BY nav.name is likely the culprit here. It is grouping all results on that field, thus all child/sub nav items with the same main nav name will be merged into one result with the data from the last record in that row.

You should maybe group by another field or don't group at all if possible.

This would require you to re-write your navigation rendering loop to handle the new data structure as you will have every sub-nav item now.

The other option is splitting the queries up into sub-queries for each sub-nav item.

Something like: 

SELECT 
   nav.name, 
   nav.href, 
   nav.nav_id
FROM nav
ORDER BY nav.position

Then loop through each result from that and perform: 

SELECT 
   subnav.name,
   subnav.nav_id AS parent 
FROM subnav
WHERE subnav.nav_id = 11 // Or the current parent nav_id
ORDER BY subnav.position

Upvotes: 1

Related Questions