SQL LEFT JOIN not pulling intended data-CodePudding

I have the below tables:

CATEGORIES:
id | name          | category_group  | cate_type_id
1  | Entertainment | Entertainment   | 1
2  | Electricity   | Utilities       | 8
3  | Water         | Utilities       | 8
4  | Rent          | Living Exp      | 6
5  | credit card   | Finance         | 5

BUDGET-ITEMS:
id | budget_id | cat_id | category_group| budget_yr | budget_01 | budget_02 | ... | budget_12
1  | 1         | 1      | Entertainment | 2022      | 500       |           |
2  | 1         | 2      | Utilities     | 2022      | 1500      |           |
3  | 1         | 3      | Utilities     | 2022      |           |  250      |

I want to pull all items from Category table with mapping budget columns. Below is my JOIN.

SELECT c.id as base_id,c.name,c.category_type_id, c.category_group as base_group, b.* 
FROM category c 
LEFT JOIN budget_items b ON c.id = b.category_id 
WHERE c.category_type_id NOT IN (5) 
ORDER BY c.category_type_id, c.category_group ASC

I expect the below output:

id | budget_id | cat_id | catgroup      | budget_yr | budget_01 | budget_02 | ... | budget_12
1  | 1         | 1      | Entertainment | 2022      | 500       |           |
2  | 1         | 2      | Utilities     | 2022      | 1500      |           |
3  | 1         | 3      | Utilities     | 2022      |           |  250      |
4  | 1         | 4      | Living Exp    | 2022      |           |           |

However, I get like below (truncated base* columns here for space):

id | budget_id | cat_id | catgroup      | budget_yr | budget_01 | budget_02 | ... | budget_12
1  | 1         | 1      | Entertainment | 2022      | 500       |           |
2  | 1         | 2      | Utilities     | 2022      |           |  1500     |
3  | 1         | 3      | Utilities     | 2022      |           |           |
4  | 1         | 4      | Living Exp    | 2022      |           |           |

My query looks OK, not sure where it is going wrong. Does anyone see the issue?

Thanks in advance for your kind help.

Edit: I have truncated some columns for space here. the problem is the values are aligned to different budget columns. I get the columns correctly from the left table.

Edit: Thanks to everyone who pitched in to help, I finally figured the issue was with my data. The query was actually working fine. This community is amazing.

CodePudding user response：

Not sure what you're doing, but it seems to work as expected for me:

Schema (SQLite v3.30)

CREATE TABLE items (
  `id` INTEGER,
  `budget_id` INTEGER,
  `cat_id` INTEGER,
  `catgroup` VARCHAR(13),
  `budget_yr` INTEGER,
  `budget_01` INTEGER,
  `budget_02` INTEGER
);

INSERT INTO items
  (`id`, `budget_id`, `cat_id`, `catgroup`, `budget_yr`, `budget_01`, `budget_02`)
VALUES
  ('1', '1', '1', 'Entertainment', '2022', '500', null),
  ('2', '1', '2', 'Utilities', '2022', '1500', null),
  ('3', '1', '3', 'Utilities', '2022', null, '250');

CREATE TABLE cats (
  `id` INTEGER,
  `name` VARCHAR(13),
  `category_group` VARCHAR(13),
  `cate_type_id` INTEGER
);

INSERT INTO cats
  (`id`, `name`, `category_group`, `cate_type_id`)
VALUES
  ('1', 'Entertainment', 'Entertainment', '1'),
  ('2', 'Electricity', 'Utilities', '8'),
  ('3', 'Water', 'Utilities', '8'),
  ('4', 'Rent', 'Living Exp', '6'),
  ('5', 'credit card', 'Finance', '5');

Query

SELECT c.id
     , budget_id
     , cat_id
     , catgroup
     , budget_yr
     , budget_01
     , budget_02
FROM cats c
LEFT JOIN items i ON c.id = i.cat_id
WHERE c.cate_type_id <> 5;

id	budget_id	cat_id	catgroup	budget_yr	budget_01	budget_02
1	1	1	Entertainment	2022	500
2	1	2	Utilities	2022	1500
3	1	3	Utilities	2022		250
4

View on DB Fiddle