473,231 Members | 1,889 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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 1153
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mark Hargreaves | last post by:
Please help!!! I have a problem with a query that I cannot seem to resolve!! First things first, I am using MySQL version 4.0.17, which means I cannot use subselects. Here is my problem: ...
4
by: Lucius | last post by:
Hello everyone, I have a query problem. I'll put it like this. There is a 'publishers' table, and there is a 'titles' table. Publishers publish titles (of course). Now I want to make a query (in...
4
by: Sami | last post by:
I hope someone will tell me how to do this without having to do any VB as I know nothing in that area. I am a rank beginner in using Access. I have created a database consisting of student...
6
by: aaj | last post by:
Hi all I use a data adapter to read numerous tables in to a dataset. The dataset holds tables which in turn holds full details of the records i.e. keys, extra colums etc.. In some cases I...
1
by: sneha123 | last post by:
There will be some 20 questions and for each question there will be 4 choices.what i want to do is to select multiple answers by clicking the checkbox. i m using asp.net,vb.net pls help me we...
11
by: Ron L | last post by:
I have a data table that lists a series of items in my database. In my user form, I want the user to be able to filter by a number of criteria (e.g. location, contract, date modified, etc). Other...
48
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
9
by: ice | last post by:
Hello, I have a couple of tables. The client tables and the contacted tables. I am not sure how to start on this, what I need is a way to query all my clients then show any client that the...
3
by: =?Utf-8?B?anAybXNmdA==?= | last post by:
In Visual Studio 2005, I have my DataSet filled with several static tables (they might get changed weekly, but that's about it). With one of these tables in the DataSet, is it possible to select...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.