How can i adjust this T-SQL query so that it returns one table rather than two? :)

Question

SELECT patron_name, producer.fed_number, tax_number, average_tb_test
  FROM producer 
 INNER JOIN producer_details ON producer.federal_number = producer_details.federal_number
 INNER JOIN statement ON producer.patron_number = statement.patron_number
 WHERE producer.patron_number = @PatronNo
 (SELECT MAX(statement.statement_number), MAX(period_ending) FROM statement)

Answer 1

The T-SQL UNION operator will combine two or more queries into one result set.

Answer 2

Use:

SELECT patron_name, 
       producer.fed_number, 
       tax_number, 
       average_tb_test,
       (SELECT MAX(s.statement_number) FROM STATEMENT s),
       (SELECT MAX(s.period_ending) FROM STATEMENT s),
   FROM PRODUCER
   JOIN producer_details ON producer.federal_number = producer_details.federal_number
   JOIN statement ON producer.patron_number = statement.patron_number
  WHERE producer.patron_number = @PatronNo

But you'll get the same maximum STATEMENT.statement_number and STATEMENT.period_ending value for every row. This will give you the max per patron_number value:

 SELECT patron_name, 
        producer.fed_number, 
        tax_number, 
        average_tb_test,
        x.max_statement_num,
        x.max_period
   FROM PRODUCER
   JOIN producer_details ON producer.federal_number = producer_details.federal_number
   JOIN STATEMENT ON producer.patron_number = statement.patron_number
   JOIN (SELECT s.patron_number,
                MAX(s.statement_number) AS max_statement_num,
                MAX(s.period_ending) AS max_period
           FROM STATEMENT s
       GROUP BY s.patron_number) x ON x.patron_number = producer.patron_number
  WHERE producer.patron_number = @PatronNo 

Answer 3

I would suggest either one of the approaches:

SELECT patron_name,  
       producer.fed_number,  
       tax_number,  
       average_tb_test, 
       MAX(statement.statement_number) as MaxStatementNumber,
       MAX(statement.period_ending) as MaxPeriodEnding
   FROM PRODUCER 
   JOIN producer_details ON producer.federal_number = producer_details.federal_number 
   JOIN statement ON producer.patron_number = statement.patron_number 
  WHERE producer.patron_number = @PatronNo 
  GROUP BY patron_name,  
       producer.fed_number,  
       tax_number,  
       average_tb_test

or

DECLARE @MaxStatementNumber INT, @MaxPeriodEnding INT
SELECT @MaxStatementNumber = MAX(statement.statement_number), @MaxPeriodEnding = MAX(period_ending) FROM statement

SELECT patron_name, producer.fed_number, tax_number, average_tb_test, @MaxStatementNumber AS maxStatementNumber, @MaxPeriodEnding AS MaxPeriodEnding 
  FROM producer  
 INNER JOIN producer_details ON producer.federal_number = producer_details.federal_number 
 INNER JOIN statement ON producer.patron_number = statement.patron_number 
 WHERE producer.patron_number = @PatronNo

It really depends on the results you're looking for. If you want MaxStatemetnNumber and MaxEndingPeriod per group of patron_name, etc, use the first one, otherwise use the second.