On Tue, Aug 9, 2011 at 2:55 PM, Frank Flynn <frank@xxxxxxxxxx> wrote:
>
> 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?
No there is only 1 user that match the sotre_by. If the query is not written
correctly do you mind trying to show me how to correctly right it?
When I dump the query and run it in console I get the results I want. Not
sure what I am doing wrong.
mysql> SELECT stores.store_subject, stores.store_comments,
stores.store_date, stores.store_tptest, stores.store_by, user
s.user_id, users.user_name, users.first_name, users.last_name FROM stores
LEFT JOIN users ON stores.store_by = users.use
r_id WHERE stores.store_subject = 'Bella Roe 4980 Roe Blvd' ORDER BY
stores.store_date DESC ;
+-------------------------+----------------+---------------------+-----------------+----------+---------+---------------
-+------------+-----------+
| store_subject | store_comments | store_date |
store_tptest | store_by | user_id | user_name
| first_name | last_name |
+-------------------------+----------------+---------------------+-----------------+----------+---------+---------------
-+------------+-----------+
| Bella Roe 4980 Roe Blvd | test | 2011-08-09 14:08:05 |
600kbps-3.8mbps | 1 | 1 | chrisstinemetz
| Chris | Stinemetz |
| Bella Roe 4980 Roe Blvd | test1 | 2011-08-09 14:07:49 | 0-250kbps
| 1 | 1 | chrisstinemetz
| Chris | Stinemetz |
+-------------------------+----------------+---------------------+-----------------+----------+---------+---------------
-+------------+-----------+
2 rows in set (0.00 sec)
