0

There are 2 MySQL tables: 'posts' and 'category'. Unfortunately there is no taxonomy table.

Categories:

id: integer
name: string

Posts:

id: integer
name: string
body: text
tag: string
category: string

So, in category there is:

 id    name
 1     Books
 2     Animals

And Post:

 id    name     body      tag      category
 1     My Post  Hi post   my-post  1
 2     Post 2   2nd Post  post-2   1;2

This is te SQL query:

SELECT * FROM posts WHERE category = '1'

Only returns post id 1

SELECT * FROM posts WHERE category = '2'

Returns nothing

How can I get both posts with just one SQL query?

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
quakeglen
  • 165
  • 3
  • 7
  • This is a really bad way to store categories. You can use the `LIKE` keyword to try and accomplish what you want, but you will quickly find there are a lot of flaws with that approach. For example, if I said `WHERE category LIKE '%1%', I will get any category with a 1: 1, 11, 12, 13..., 21.. and so on. It is very hard to distinguish a single 1 by the way you've designed htis. – AdamMc331 Mar 27 '15 at 16:12
  • I know, It was like that when I saw it for the first time. I have tried `LIKE`, but it returns category 1, category 11, category 111 and so. – quakeglen Mar 27 '15 at 16:18

1 Answers1

2

Personally, I would steer well clear of that structure and create a new table PostCategoryto house your associated categories for each post so:

postID | categoryID
1      | 1
2      | 1
2      | 2

Then use a distinct select and an inner join in your sql:

select distinct post.* from post 
inner join postcategory on post.id = postcategory.postID 
where postcategory.categoryID = 2;

As @McAdam331 quite rightly said, using a string to store lookup values you're going to be querying is B.A.D for performance and in general for db design

scgough
  • 5,099
  • 3
  • 30
  • 48
  • This is perfect. I'm really glad someone took the time to demonstrate what the new table would look like. For reference of others, see [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – AdamMc331 Mar 27 '15 at 16:17
  • Thanks guys, it seems a much better solution – quakeglen Mar 27 '15 at 16:22