SQL: getting duplicate records with this query

I have an app that has posts, tags, and a tag_ref link table. I want to query for posts that have a specific tag association.

The database structure is as follows:

posts

id

body

city_id

tags_ref

row_id

table

tag_id

tags

id

safe_tag

tag

So tags_ref is a link table between posts and tags.

After adding some tags to posts I'm now getting duplicate records in my queries.

Even with this query:

SELECT P.* FROM posts as P WHERE P.city_id = 2

Even though I'm not joining tables here and only looking for posts where the city_id field is set to 2 I'm still getting duplicate records only for records that have tags associated with them.

I've also tried:

DISTINCT (P.id), P.* FROM posts as P WHERE P.city_id = 2

I know there are only 7 rows in my posts table so I know duplicate records don't actually exist as I'm counting 7 unique posts.

Can anyone help me figure out 开发者_如何转开发where I've gone wrong?

Database is MySQL

Thanks,

Billy

---

DISTINCT (P.id), P.* FROM posts as P WHERE P.city_id = 2

Your DISTINCT here does nothing when some values in other fields (in P.*) differ. Try to query the whole table posts without a filter to see what you've really got there.