SQL Server 2012 - Generate query from one table to another involving 3 (three) tables at once

1

Hello, Community!

I need help with the logic of a query I have to do in SQL Server 2012 .

Well, I have three tables: [1 '] operacao_apf ; [2] operacao_contrato ; and, [3] operacao_detalhe .

What I need to do: in [1 '], I need to get the name of any operation, and, by name, I have to go to [2] and select all contracts related to this operation. After selecting all the contracts in [2] related to the operation searched in 1 , I need to go to [3] and select all the details related to each transaction related to the transaction in 1 .

Layout:

Note that tables are always associated with 1:N from left to right. An operation can have multiple contracts and each contract can have several details.

What I want to do is generate a report with this, but I need to understand the SQL Server 2012 logic to get started.

    
asked by anonymous 04.09.2016 / 05:53

1 answer

1

User or INNER JOIN .

Specifies all matching pairs of rows returned. Discards the non-matching rows from both tables. When no type of join is specified, this is the default.

FULL [OUTER] Specifies that a row in the left or right table that does not meet the join condition is included in the result set, and the corresponding output columns in the other table are set to NULL. This occurs in addition to all lines usually returned by INNER JOIN.

LEFT [OUTER] Specifies that all rows in the left table that do not meet the join condition are included in the result set, and the output columns in the other table are set to NULL in addition to all rows returned by the inner join. RIGHT [OUTER] Specifies that all rows in the right table that do not satisfy the join condition are included in the result set, and the output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join. >

Specifies that the SQL Server query optimizer uses a join hint, or an execution algorithm, for each query specified in the FROM clause of the query. For more information, see Join Tips (Transact-SQL). JOIN Indicates that the specified join operation must occur between specified table sources or views.

ON Specifies the criteria on which the join is based. Criteria can specify any predicate, although columns and comparison operators are often used, for example:

SELECT *  
FROM operacao_apf AS o   
JOIN operacao_contrato AS c  
ON o.ProductID = c.ProductID -- altere com os ids derelação  
JOIN operacao_detalhe AS d  
ON c.ProductID = d.ProductID -- altere com os ids derelação  
    
04.09.2016 / 17:24