如何摆脱右连接中的空值

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:

Answer 1

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