Performing a sub query using values from the main query in postgres

Question

I am trying to match up data between two tables by using the same values from the main query in the sub-query. I am using 3 tables for this:

World - The area of the world that the player has been in Player - The actual player Objects destroyed - Just a stats table used to keep track of the player's progress in the world

What I am trying to do is to create a query that displays the play and the world they were in and get a count of all the objects they have destoryed in that world. My query is currently like this:

select
    world_id,
    world_name,
    player_id
    count(select * from objects_destoryed where player_id = <insert player ID>) as Stats
from
    Worlds
    join Players using (player_id)
where
    player_id = <insert player ID>

What I am asking is if there is a better way rather then including the player ID twice to get the overall statistics. Also is there a way that I can expand this to run on multiple players that were in that world? Such as using a player_id in statement.

Thanks for any advice

Alan Gilbert · Accepted Answer · 2011-10-13 19:54:44Z

1

I have not used postgresql, but in other flavors of SQL, something like this would work:

select
    world_id,
    world_name,
    player_id
    count(*) as Stats
from
    Worlds
    join Players using (player_id)
    join objects_destroyed using (player_id)
where
    player_id = <insert player ID>
group by world_id, world_name, player_id

The goal is for the select statement to return one row for each object destroyed by the player in the world, and then allow the group by clause to collapse the rows with the same world and player into a single row, with the number of rows consolidated into "Stats."

In order to make this return results for several players, you would just change your where clause so that it keys off of the world, rather than the player. Each player with a presence in that world would have his own row.

edited Oct 13, 2011 at 19:54

answered Oct 13, 2011 at 19:49

Alan Gilbert

3652 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Seb Over a year ago

Guess I was just over complicating my problem. Thank you for your help.

Collectives™ on Stack Overflow

Performing a sub query using values from the main query in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related