How to do Nested Queries in SQL

Question

I'm trying to learn SQL, and while I'm slowly learning how to query data, I'm stuck on querying the results of a query. Take this example

I want an SQL statement to do 2 things. Suppose I have 2 tables like the one below (table 1 borrowed from another example on stack overflow)

Table 1:

ID  game   point  time
1    x      5     7:00
1    z      4     11:00
2    y      6     9:00
3    x      2     2:00
3    y      5     4:00
3    z      8     6:00
4    k      0     8:00    

Table 2:

id  tv chan
1    cab  
2    trop  
3    start   
4    cab  

The first thing I want to do is combine certain columns from these tables. I know I can select these columns and do an inner join on ID

However the second thing I want to do is drop all the rows with point value 0, and then have only rows with distinct game name with the lowest point value. So I want the final table to look like this

id  game   point   tv chan    
1    z      4       cab
2    y      5       trop
3    x      2       start

Thanks

Answer 1

You could try something like this:

SELECT t1.ID, 
       t1.game, 
       t1.point, 
       t2.tv_chan 
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.id = t1.id 
INNER JOIN (SELECT t11.game, MIN(t11.point) AS min_point  
            FROM Table1 AS t11 
            WHERE t11.point != 0 
            GROUP BY t11.game
           ) AS t3 ON t3.game = t1.game
                      AND t3.min_point = t1.point 
WHERE t1.point != 0