How can I join all the record obtained by a select on a table with the records of another table?

Question

I am pretty new in SQL and I have the following problem trying to implement the following query on a MySql database.

So I have this situation:

Into my database have a table named TID023_INTERVENTO where each line of this table represent a project (for me a project is something that can receive a grant).

And it have the following structure:

Field            Type            Null   Key    Default    Extra

PRG_INT          int(11)         NO     PRI               auto_increment
COD_INT          varchar(15)     NO     UNI     
DES_INT          text            YES            
COD_CUP          varchar(15)     YES    UNI     
FLG_CUP_PRO      tinyint(1)      YES            
...................................................................
...................................................................
...................................................................

So by this query:

select * from TID023_INTERVENTO where COD_TIP_STA_INT = 2 && COD_TIP_BAN = 2;

I obtain a list of projects (a project is a record of the TID023_INTERVENTO table) that have 2 specific values for the COD_TIP_STA_INT and COD_TIP_BAN fields.

Ok, this query works fine and I obtain my list of projects.

My problem is that now I have to modify the previous query to join the information stored into another table named TID031_PIANO.

The situation is the following: each record retrieved by the previous query is linked 1:1 to a record into the TID031_PIANO table that have the following structure:

Field             Type          Null    Key
PRG_PIA           int(11)       NO      PRI
DES_DEN_SCU       varchar(255)  NO  
DES_PIA           text          YES 
PRG_INT           int(11)       YES     MUL     
..............................................................
..............................................................
..............................................................

So, as you can see, into this second table (TID031_PIANO) there is the PRG_INT field that represent the primary key of the TID023_INTERVENTO table.

I know that I can first retrieve the list of the records of the TID031_PIANO table by my original simple query, this one:

select * from TID023_INTERVENTO where COD_TIP_STA_INT = 2 && COD_TIP_BAN = 2;

and then iterate on each record to obtain the primary key and so use this primary key to perform a second simple select query on the TID031_PIANO table specifying the condition on the PRG_INT field of this table.

But I want perform it into a single query performina join or something like this.

What is the best way to achieve this task?

Answer 1

So, you are looking for join syntax?

select * from TID023_INTERVENTO s
INNER JOIN TID031_PIANO t 
ON(s.PRG_INT = t.PRG_INT )
where s.COD_TIP_STA_INT = 2 and t.COD_TIP_BAN = 2;