Strange problems with a join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Theory?
Forum Contributor
Posts: 138
Joined: Wed Apr 11, 2007 10:43 am

Strange problems with a join

Post by Theory? »

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;
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Strange problems with a join

Post by novice4eva »

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??
Theory?
Forum Contributor
Posts: 138
Joined: Wed Apr 11, 2007 10:43 am

Re: Strange problems with a join

Post by Theory? »

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.
Post Reply