1

How can I get the number of advertisements when linking tables with left join in Mysql?

SQL-FIDDLE-TABLE

if I use count (*) and group by, I get a result like

SQL-FIDDLE-TABLE

count
2
2
2

My goal is to get the number of ads with Find_in_set

SQL-FIDDLE-TABLE

result:

count
2

Result count: 2, but there are 1 classifieds in that category. How can I get the number of ads for a category?

Kenan Şimşek Birusk Kuresofa
  • 63
  • 1
  • 8
  • I solved the problem. Thanks anyway. select count(*) from ( select ads_category.id as category_id, ads_category.title, ads.id, ads.categories, ads.ads_title from ads_category left join ads on FIND_IN_SET(ads_category.id,ads.categories) where ads.id IS NOT NULL group by ads.id ) as table_1 where FIND_IN_SET(1,table_1.categories) – Kenan Şimşek Birusk Kuresofa Apr 08 '18 at 15:07

1 Answers1

1

FIND_IN_SET() doesn't return the number of elements in a list, it just returns the position of a specific value, if it is found.

One expression you can use to calculate the length of a list is:

LENGTH(ads.categories)-LENGTH(REPLACE(ads.categories, ',', ''))+1

The difference in the length of the string and the string with commas removed, plus on, is the number of elements.

But you will find storing data as a comma-separated list when you really want to treat the elements individually is going to cause more problems.

It'll be much easier if you represent this many-to-many relationship between ads and categories by creating a third table to store each pair of ads.id and ads_category.id.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828