469,167 Members | 1,140 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Selecting from more than two tables

Hi there

After posting a little bit earlier about many to many tables, I was
wondering how I can get the total number of logins for each store, if I'm
storing information like so:

Stores:

+---------+-------------+
| StoreID | StoreName |
+---------+-------------+
| 6 | Barrie |
| 10000 | Toronto |
| 1 | Head Office |
+---------+-------------+
Users:

+--------+---------+-----------------+
| UserID | StoreID | Name |
+--------+---------+-----------------+
| 1 | 1 | Administrator |
| 2 | 6 | Chris Bosh |
| 3 | 6 | Reginald Dwight |
| 4 | 10000 | Bart Simpson |
+--------+---------+-----------------+

LoginTimes:
+--------+------------+
| UserID | LoginTime |
+--------+------------+
| 2 | 1130348594 |
| 2 | 1130349580 |
| 2 | 1130365537 |
| 2 | 1130528264 |
| 2 | 1130720304 |
| 2 | 1130791108 |
| 3 | 1130790764 |
| 3 | 1130791297 |
| 3 | 1130791322 |
| 4 | 1130794187 |
+--------+------------+

so, im looking for something like "Barrie: 9 total logins, Toronto: 1
total logins, Head Office: 0 total logins"

This is taking place on a 4.0.x server, so subqueries are not permitted.
I'm assuming this is possible to do in one select statement, but i start
to get lost when i make a query from more than two tables....

Thanks kindly.

Oct 31 '05 #1
1 1098
Good Man <he***@letsgo.com> wrote in
news:Xn************************@216.196.97.131:
so, im looking for something like "Barrie: 9 total logins, Toronto: 1
total logins, Head Office: 0 total logins"

I've almost done it... this is a total visit lookup per store:

Select Count(logintimes.LoginTime) AS totalvisits From stores, logintimes
Inner Join users ON logintimes.UserID = users.UserID AND users.StoreID =
stores.StoreID WHERE stores.StoreID='$vStoreID'

is that okay? the EXPLAIN looks good...


Nov 1 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Mark Hargreaves | last post: by
6 posts views Thread by aaj | last post: by
11 posts views Thread by Ron L | last post: by
9 posts views Thread by ice | last post: by
3 posts views Thread by =?Utf-8?B?anAybXNmdA==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.