Need some SQL help

I have been thinking about how to do this queery all day and just can't think of how it should be structured....

I have a table, lets call it table A. Which has a list of users with a unique id. So:

ID_____________________NAME
1**********************Bob
2**********************Joe

I have a second table, Lets call table B which contains things that the users have done with record of the user's ID in it.

Record_________________Action_______________User
1**********************Eat******************2
2**********************Sleep****************1
3**********************Sleep****************2
4**********************Shite*****************2

What i need to do is find out how many actions where taken by each user. So say I want to display the result (us MS Access) as:

User__________________Actions
Bob*******************1
Joe*******************3

I've done this sort of queery before (about 2 years ago), and all the google searches I do on inner joins and outer joins aren't jogging my memory on how i did the queery..

Any thoughts?

Something like:

select a.Username, count(b.record)
from tblA a
inner join tblB b on a.ID=b.User
Group by a.Username

Oh and mind the language.

If you want those with no action to show up on the report make it an outer join instead of an inner join.


select a.Username, count(b.record)
from tblA a
outer join tblB b on a.ID=b.User
Group by a.Username


If you do not want them to show up then the inner join is what you want.

Sorry, Joe only does 3 things in life.... And what goes in, must come out!

Thanks alot for you help guys... I knew it was a simple query.. Doesn't work in M$ Access, but thats because access kinda sux for SQL queries.. At least I have a starting point of what i need to do.

Thanks again!

Ok, here's another one for you guys. Probably a noob question but here goes...

I have a table of ships and waypoints. For instance:

ShipID________________WayPointID
1********************34
1********************46
1********************77
2********************88
2********************82
2********************64
3********************77
3********************54
3********************21

In one "turn" I just want to select the first waypoint for each ship. So I only retrieve the following rows:
1********************34
2********************88
3********************77

Is there a simple way to do it?

I have other tables where the ship ids are unique, so I was trying to figure out a way using DISTINCT and JOIN, but I don't know if that's possible.

select shipid,min(waypointid)
from AA
group by shipid

If you want all the information from those rows then.

select *
from aa a1 (A1 is an alias for the table)
where aa.waypointid = (select min(a2.waypointid)
from aa a2 (a2 is another alias)
where a2.shipid = a1.shipid)

The aliases are so you have unique names for the table in each section.

> Doesn't work in M$ Access, but thats because access kinda sux for SQL queries..

Access has slightly different sql syntax to worry about, but the statements above should work with slight tweaking to make it access specific. (been years since I had to worry about access sql though)

Siread you really need an order column too

So you'd have ShipID, WaypointID, iOrder.

Then you'd just

Select Top 1 ShipID, WaypointID From Waypoints Where ShipID = @Variable Order By iOrder

Of course if you have the iOrder column it actually makes life simpler still

Select ShipID, WaypointID from Waypoints Where ShipID = @Variable And iOrder = 1

Ah thanks. I don't think SQLite supports the TOP command but the Order column makes sense.