CODEWITHSUNDEEP
mysqlsqldatabase
twan
twan

Reputation: 2659

Why is my result empty when using this query

I am getting products and a category that the product belongs too to display on a page. Also I get some data belonging to the product that is in a different table.

My query looks like this:

SELECT cnt.id as content_id, cnt.title as content_title, cnt.featured, cnt.alias as content_alias, cnt.catid, cnt.images, cnt.state, cnt.introtext, cat.id as cat_id, cat.title as cat_title, cat.alias as cat_alias,
MAX(case when f.field_id = 4 then f.value end) as prijs,
MAX(case when f.field_id = 5 then f.value end) as prijsoud
FROM snm_fields_values f
JOIN snm_content cnt
ON cnt.id = f.item_id
JOIN snm_categories cat
ON cnt.catid = cat.id
WHERE cnt.catid = '17'
AND cnt.state = 1
GROUP BY f.item_id

My problem is, when a categorie doesn't have any articles under it, all results are empty. So on a category page where there are no products belonging to that category, it also doesn't show the category title.

Only when a category has a product (snm_content) under it, then it displays all data.

Above query returns the following:

enter image description here

There are no products under catid 17.

When I change it to 16 (which does have products) this is my result:

enter image description here

I would like to get all data that is there, so when a category doesn't have products, I still need the category title.

Why is everything empty when a product doesn't exist?

Upvotes: 1

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I think you want outer joins. I don't fully understand the GROUP BY, but this may do what you want:

SELECT cnt.id as content_id, cnt.title as content_title, cnt.featured, cnt.alias as content_alias, cnt.catid, cnt.images, cnt.state, cnt.introtext, cat.id as cat_id, cat.title as cat_title, cat.alias as cat_alias,
       MAX(case when f.field_id = 4 then f.value end) as prijs,
       MAX(case when f.field_id = 5 then f.value end) as prijsoud
FROM snm_categories cat LEFT JOIN
     snm_content cnt
     ON cnt.catid = cat.id AND cnt.state = 1 LEFT JOIN
     snm_fields_values f 
     ON cnt.id = f.item_id 
WHERE cat.id = 17
GROUP BY cnt.id, cnt.title, cnt.featured, cnt.alias, cnt.catid, cnt.images, cnt.state, cnt.introtext, cat.id, cat.title, cat.alias

Upvotes: 1

Related Questions