CODEWITHSUNDEEP
mysqlsqlcompare
Astha
Astha

Reputation: 1734

mysql SQL query to compare integer and string

I have a problem with comparing a string to integer columns of different tables in database.

Have a database with two tables as categories and content. Now each content text can belong to more than one category which is listed in category_ids column of content table separated by comma. I know it is not normalized. But it is already there.

categories table

id | name | timestamp
 1   aaa    something
 2   bbb    something
 3   ccc    something
 4   ddd    something
 5   eee    something

content table

id | category_ids | content | timestamp
 1     1              xxx     something
 2     1,2,3,4        yyy     something
 3     1,2            zzz     something
 4     1,2,3          www     something

Now i have to show list of categories with the count of content text in each categories. Means number of content text using that category in it. Note (category eee is not there in any content text so showing 0 count in result set.)

aaa(4)
bbb(3)
ccc(2)
ddd(1)
eee(0)

i tried alot of ways to compare id of categories table to category_ids of content table. But one is integer and other one is string.

one way i tried is cast function but failed

SELECT C.id , C.Name, COUNT( * ) AS ContentCount FROM categories AS C LEFT JOIN content  AS Q ON (CAST(C.id AS CHAR) = (Q.category_ids))  GROUP BY C.Name

Please dont tell me ways to change the database. If any ideas that how can i write a query which will give me this kind of result set will be highly appreciated.

Upvotes: 0

Views: 2054

Answers (2)

diagonalbatman
diagonalbatman

Reputation: 17982

You need no normalise your quotes table. Ideally you shoud have a quote and a quote-category table. Each quote is then only stored once in your quote table, the quote category table has columns like quotid,catid so if a quote had entries in cat a,b,c you would have three entries on the quote category table. You can then use joins and group buys to answer your question

Upvotes: 1

Johan
Johan

Reputation: 76537

CSV in a database is the worst idea ever.
It will kill any chance of using an index. It's not normalized. It's slow.
It will keep on biting you again and again.

SELECT C.id , C.Name, COUNT( * ) AS QuotesCount 
FROM categories AS C 
LEFT JOIN quotes  AS Q ON (FIND_IN_SET(c.id,Q.category_ids))
GROUP BY C.Name

See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Upvotes: 2

Related Questions