So I have three records in my database, but when I run this query, it returns 9 results, three instances of each record. Why?
SELECT fileName, title, author, category FROM photos AS p, authors AS auth, categories AS cat, photo_associations AS pa WHERE pa.cat_id = cat.cat_id AND pa.author_id = auth.author_id ORDER BY p.photo_id;
Strange problems with a join
Moderator: General Moderators
- novice4eva
- Forum Contributor
- Posts: 327
- Joined: Thu Mar 29, 2007 3:48 am
- Location: Nepal
Re: Strange problems with a join
your join seems to be wrong, there is no association between the photos table with any other table in the WHERE clause. Can you give us the columns of the tables too so we could help you build the query. Also are you sure there just is one to one association between tables??
Re: Strange problems with a join
I actually figured it out right before I passed out last night. I had three tables all associated by a fourth table that kept all their ID's logged. I never called for the third ID from the photo's table. It works perfectly now.