How to get rid of null values in right join

Question

I have Three Table

categories

id
name

Sub_Categories

id
name
category_id

Sub_categories_three

id
name
sub_categories

Now I want to show all the data (name) from these table in a dropdown list, what would be the query to do so and how to set them on a view (nested loop) using foreach or mysqli_fetch_assoc().

I am using this query

$query="SELECT a.c_name, a.id, b.sc_name, b.id, c.sct_name, c.id 
FROM categories a 
right JOIN sub_categories b ON a.id=b.id 
right JOIN sub_categories_three c ON b.id=c.id";

But this query is returning null values also that fill unnecessary space in my dropdown tag.

Here is the image showing the null values that I don't want:

May be `nlv()` function with string aggregation operator `|` could help you. Any way could you provide the outlook of current and expected select result? — Alexey Usharovski, Jul 03 '19 at 13:46
Based on these results, you have records in your sct (subcat3) that don't link to a sc (Subcat) and records in sc that don't link to a c (category). Seems odd to me you'd want to use a right join here. Usually we want to see all categories, then the items in subcategories that relate, then a 3rd level... Did you mean to use a left join instead of right? — xQbert, Jul 03 '19 at 14:39
i use the left join but it only return 3 rows based on category(first table) — Ali Raza, Jul 03 '19 at 15:00

silenum · Answer 1 · 2019-07-04T05:52:46.477

So frist of all you do not need 3 tables. You can club them all together! This leads to following schema:

create table category
(
    id       int primary key,
    name     varchar(255) not null,
    superior int
);

So far, so good. Now we need some dummy data. For that execute following statement:

insert into category (id, name, superior)
values (1, 'Category 1', null),
       (2, 'Category 2', 1),
       (3, 'Category 3', 1),
       (4, 'Category 4', 1),
       (5, 'Category 5', 1),
       (6, 'Category 6', 2),
       (7, 'Category 7', 2),
       (8, 'Category 8', 2),
       (9, 'Category 9', 2),
       (10, 'Category 10', 3),
       (11, 'Category 11', 3),
       (12, 'Category 12', 3),
       (13, 'Category 13', 3),
       (14, 'Category 14', 4),
       (15, 'Category 15', 4),
       (16, 'Category 16', 7),
       (17, 'Category 17', 7),
       (18, 'Category 18', 8),
       (19, 'Category 19', 8),
       (20, 'Category 20', 8);

Now all you need to do is a recursive SQL statement. In this case I want Category 7 and all its subcategories:

with recursive subcategory as (
    select id, name, superior
    from category
    where id = 7
    union
    select c.id, c.name, c.superior
    from category c
             inner join subcategory s on s.id = c.superior
)
select *
from subcategory;

The result is (CSV-Format):

id, | name,        | superior
---------------------------
7,  | Category 7,  | 2
16, | Category 16, | 7
17, | Category 17, | 7

This is how I suppose to solve your problem.

This article might help you as well.

Cheers and stay tuned!

silenum

How to get rid of null values in right join

1 Answers1