CODEWITHSUNDEEP
sqlpostgresql
user38543
user38543

Reputation: 59

How do I output a table that an id have different gender in their game

I am not sure how to write the title of this question so the title might sound really confusing so please look at here. let suppose we have these two tables.

The first table is id, name, gender. This is the id of their game like login where name and gender is their name and gender in real life.

Where in the second table, name, and gender refer to in-game gender and name refer to in-game name as well.

id | name | gender      id |    name     | Gender    
---+------+----        ----+-------------+-------- 
1  |   A  |  F          1  |     a       |   F
2  |   B  |  M    and   1  |     b       |   F
3  |   C  |  M          2  |     c       |   F
4  |   D  |  M          3  |     d       |   M
                        3  |     e       |   M
                        3  |     f       |   F
                        4  |     g       |   M
                        4  |     h       |   M

We want to select the id, name, and gender(in the first table), and the number of characters that an id had but that doesn't have the same gender as real-life. This might sound really confusing so here is what should be the output is

id | name  | gender| #Character
---+-------+-------+-----------
2     B        M         1
3     C        M         3

id1: The reason it doesn't output id 1 is that id = 1 is F and she created 2 characters in the game, but both of the characters are F so she didn't 'switch' her gender so we do not print the row.

id2: We select id 2 because id = 2 is M in real-life, but he in-game character is F so we select this row.

id3: He is M in real life and one of his characters is F so we select this row

id4: He is M and none of his character is F so we don't need to print it on the screen.

Hopefully, you get what I'm trying to do here.

select id, name, gender,
(select count(*) from table_2 as t2 where t2.id = t1.id group by id) as #Character
from table_1 as t1
order by login;

Above query will print every id, name and gender and number of characters an id had, but this is not what I wanted. What should I change my code so that it works as what I Intended to do?

Upvotes: 1

Views: 369

Answers (3)

Abelisto
Abelisto

Reputation: 15624

Actually it is simple.

We want to select the id, name, and gender(in the first table), and ... characters that an id had but that doesn't have the same gender as real-life.

select
    *
from
    t1
where
    exists (select 1 from t2 where t1.id = t2.id and t1.gender <> t2.gender)

We want to select the id, name, and gender(in the first table), and the number of characters ...

select
    *,
    (select count(*) from t2 where t1.id = t2.id) as "#Character"
from
    t1
where
    exists (select 1 from t2 where t1.id = t2.id and t1.gender <> t2.gender)

Upvotes: 1

lareb
lareb

Reputation: 92

Try this:

select id, 
(select count(*) from table_2 as t2 where t2.id = t1.id group by id having count(*) >1) as #Character
from table_1 as t1
order by login;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Now that I understand the problem:

select t1.*, count(*)
from table_1 t1 join
     table_2 t2
     on t2.login = t1.login
group by t1.id
having count(*) filter (where t2.gender <> t1.gender) > 0;

In other words, you can filter in the having clause.

I think id and login could be confused -- your sample query does not match the sample data. But you should get the idea.

Upvotes: 0

Related Questions