You already have a foreign key, that is stores.store_by references users.user_id. You might not have declared it (which is OK) but if that is the key you want that is fine. I suspect you are seeing an inadvertent Cartesian product. The way you have written this query you will get one row from the stores table for each row in the users table where a store_by = user_id - and because you said "LEFT JOIN" you will get one row from the stores table even if there are no matching rows in the users table. So you say you get 3 x the rows you're expecting; are there 3 users that match that store_by? Good Luck, Frank On Aug 9, 2011, at 11:31 AM, php-db-digest-help@xxxxxxxxxxxxx wrote: > From: Chris Stinemetz <chrisstinemetz@xxxxxxxxx> > Subject: foreign key > Date: August 9, 2011 11:31:51 AM PDT > To: php-db@xxxxxxxxxxxxx > > > Okay. I am pretty new to mysql so this may seem like a ridiculous > question to some people. > > I am trying to use a LEFT JOIN query, but the results I am finding > unusual. For every record occurrence there is for the query, the > results are duplicated by that amount. > > So if there are 3 records from the query results, then the output is 3 > times what I expect.. if that makes sense. > > From what I have researched so far. I believe I may need to add a > foreign key to build the relations between the two tables. > > Based on the query can any tell me the correct way of adding the > foreign key if ,in fact, that is what I need? > > I can provide table structures and information if necessary. > > The query is: > > $posts_sql = "SELECT > store_subject, > store_comments, > store_date, > store_tptest, > store_by, > users.user_id, > users.user_name, > users.first_name, > users.last_name > FROM > stores > LEFT JOIN > users > ON > stores.store_by = users.user_id > WHERE > stores.store_subject = '" . mysql_real_escape_string($_GET['id'])."' > ORDER BY > stores.store_date DESC "; > > The query dump is: > > SELECT store_subject, store_comments, store_date, store_tptest, > store_by, users.user_id, users.user_name, users.first_name, > users.last_name FROM stores LEFT JOIN users ON stores.store_by = > users.user_id WHERE stores.store_subject = 'Noland Park Plaza 3509 S. > Noland Rd' ORDER BY stores.store_date DESC >
