Three table join

luiddog

I have three tables. Users, Events and States. The User table has the user's state, the event table has the user's events and states table is just the 50 US states. What I want is to list the number of events per a state, while listing all the states. So:

ALABAMA
ALASKA
ARIZONA (1)
ARKANSAS
CALIFORNIA (22)

I got this so far:

SELECT *, COUNT(Users.State) FROM Events, Users
WHERE Events.DateEnd >= NOW() 
AND Events.uid = Users.UID
GROUP BY Users.State

But I can't get it to have all the states with the number of events within the state. And again, the events table doesn't have the state. The events table is linked to the user table via a user id and the user table has the state.

Lars_Berg

SELECT  states.statename, COUNT(*) as events
FROM states left join Users on states.sid = users.sid
 left join events on Events.DateEnd >= current_date
 AND Events.uid = Users.UID
GROUP BY states.statename

change column names as apropriate.

luiddog

Can't get it to work. All I get is:

State | eventNum

Alabama | 50

This is what I'm using, based on the previous reply:

SELECT  States.State, COUNT(*) as eventsNum
FROM States left join Users on States.State = Users.State
 left join Events on Events.DateEnd >= current_date
 AND Events.uid = Users.UID
GROUP BY  Users.State

luiddog

Still having problems. I've tried everything.

laserlight

What is your relevant database schema?

luiddog

What do you mean?

The tables are as follows:

States
-StateID
-State
-Abbr

Users
-UID
-State

Events
-EventID
-uid
-EventName
-DateStart

Events are linked to users by the 'UID'.
-DateEnd

laserlight

What do you mean?

Either show the CREATE TABLE statements, or at the very least, what you have shown. You should state the primary keys and foreign keys for more clarity, of course.

Users are linked to states by state name rather than id? I might suggest the SQL query below, which essentially is another way of writing what was suggested:

SELECT States.State, COUNT(States.State) AS eventsNum
    FROM States, Users, Events
    WHERE States.State=Users.State
        AND Users.UID=Events.uid
        AND Events.DateEnd >= NOW()
    GROUP BY States.State

luiddog

Thanks for your help but the previous code just results in the number of events per a state with events, but I'm trying to list all 50 states with the amount of events. Not just the states with events. i.e.

Alaska
Alabama (1)
Arizona
California (24)
Connecticut (2)
...

laserlight

Thanks for your help but the previous code just results in the number of events per a state with events, but I'm trying to list all 50 states with the amount of events. Not just the states with events.

Ah, I see what you mean. I was confusing a left join with inner join. In that case, perhaps the only problem was that you used the wrong join condition, so a little re-arrangement might work:

SELECT States.State, COUNT(States.State) AS eventsNum
    FROM States LEFT JOIN Users ON States.State=Users.State
        LEFT JOIN Events ON Users.UID=Events.uid
    WHERE Events.DateEnd >= NOW()
    GROUP BY States.State

luiddog

Thanks again, but it still just gives the states that have events. I'm trying to list all states, even if they have no events. But thanks again, I've been trying everything.