SQL read multiple tables and self join-CodePudding

Been trying to learn SQL and am stuck on a problem I want to understand:

Given the following tables:

TABLE - Customer.movie_id
ID  MOVIE_ID
-------------
x   Spiderman     
y   Batman   
z   Avengers  

TABLE - Customer.game_id
ID  GAME_ID
-------------
A   COD     
C   HALO   
D   BATTLEFEILD
B   MINECRAFT  

TABLE - Customer.type_id
ID  TYPE_ID
-------------
ii   AGE     
jj   GENDER   
kk   INCOME  

TABLE - Customer.Info
ID  MOVIE_ID  GAME_ID   TYPE_ID   DATA
--------------------------------------------
1     x          A        ii       20
2     x          A        jj       F
3     x          C        kk       1000
4     y          C        ii       40
5     y          D        jj       M
6     y          C        kk       5000
7     z          B        ii       60
8     z          B        jj       F
9     z          C        kk       10000

Produce an output that will show rows of AGE only if MOVIE_ID and GAME_ID match the same values on the GENDER type rows.

TABLE - Customer.Info

ID  MOVIE_ID  GAME_ID   TYPE_ID   DATA
--------------------------------------------
1     x          A        ii       20
7     z          B        ii       60

I have been able to do queries individually in python and process it there, but I don't have any idea of how to combine all of this into one query.

Can anyone help?

CodePudding user response：

This is probably the shortest. If any other rows exists that has the same ids and gender then return it:

select i1.*
from Customer.Info i1 inner join Customer.Info i2
    on      i2.MOVIE_ID = i1.MOVIE_ID and i2.GAME_ID = i3.GAME_ID
        and i1.TYPE_ID = 'ii' and i2.TYPE_ID = 'jj'
where and exists (
    select 1 from Customer.Info i3
    where i3.ID <> i2.ID
        and i3.MOVIE_ID = i2.MOVIE_ID and i3.GAME_ID = i2.GAME_ID
        and i3.TYPE_ID = 'jj' and i3.DATA = i2.DATA
);

I don't know if you intend for groups of more than two to all match together without variation.

CodePudding user response：

This data model is not normalized properly and everything is named poorly, but aside from that, you probably want some form of EXISTS clause, such as:

select
    i.*
from
    customer.info i
    join
    customer.type_id ti on ti.id = i.type_id
where
    ti.type_id = 'AGE'
    and
    exists(
        select 1
        from customer.info i2
        join customer.type_id ti2 on ti2.id = i2.type_id
        where i2.movie_id = i.movie_id
        and i2.game_id = i.game_id
        and ti2.type_id = 'GENDER'
    )
/

Or, if EXISTS is not available, you can do it joining two subqueries:

select 
    age_info.*
from
    (
        select i.*
        from customer.info i
        join customer.type_id ti on ti.id = i.type_id
        where ti.type_id = 'AGE'
    ) age_info
    join
    (
        select distinct i.movie_id, i.game_id
        from customer.info i
        join customer.type_id ti on ti.id = i.type_id
        where ti.type_id = 'GENDER'
    ) gender_info
    on gender_info.movie_id = age_info.movie_id
    and gender_info.game_id = age_info.game_id
/