Agfdhyk

I have been improving my MYSQL skill over the past month but have hit the point where im unsure of what to do get this over the line.

select id, fname,lname,status, email, country_name, login_count, 

        (select count(ID) from applications inner join players on users.id = players.user_id inner join applications on players.id = applications.user_id where players.id = applications.player_id),  (select count(ID) from messages where users.id = messages.user_id) AS '# of Messages', (select count(id) from participants where users.id = participants.user_id) AS '# of Threads', (SELECT created_at FROM messages where users.id = messages.user_id ORDER BY id DESC LIMIT 1 ) AS 'Last Message Date', updated_at

from users

where type = 'player'

#And (country_name = 'australia' or country_name = 'new zealand' or country_name = 'South Africa')

and status = 'active'

and (select count(ID) from messages where users.id = messages.user_id) = 0

and (select count(id) from participants where users.id = participants.user_id) != 0

and updated_at > '2018-10-01'

order by updated_at desc

I am trying to get the number of applications that a user of type player has made. Im also keen to get the number of messages and threads they have been involved in.

I can run the query fine if i remove

(select count(ID) from applications inner join players on users.id = players.user_id inner join applications on players.id = applications.user_id where players.id = applications.player_id),

but as soon as i try to get a count of the applications made it returns this

Not unique table/alias: 'applications'

Any help would be most appreciated.

(please be kind, I'm self-teaching myself SQL)

Your issue is when you run the subquery here:

select count(ID)

from applications

inner join players on users.id = players.user_id

inner join applications on players.id = applications.user_id

where players.id = applications.player_id

Any time that you reference a table more than once in a given query, you need to give that table reference a unique alias. This query touches the applications table twice, so MySQL doesn't know which one you're referring to in a given situation (and in general, you should be aliasing your tables anyway, since it makes EXPLAIN statements easier to follow for debugging, and makes the query more readable).

You can add aliases using the as syntax:

select

    count(mainApps.ID)

from

    applications as mainApps

inner join

    players as appPlayers

    on  appPlayers.id = mainApps.playerId

inner join

    applications as joinedApps

    on  appPlayers.id = joinedApps.user_id

where

    users.id = appPlayers.user_id