CODEWITHSUNDEEP
sqlsql-server
Trang Le
Trang Le

Reputation: 87

How to combine these 2 SQL query results?

1st Query: 

Select O.TrainDate, O.RailHead, O.Position as Pos, 
    O.Iso, C.CustomerName + ' (' + O.Customer + ')' as Customer, 
    P.ProductName + ' ('+O.Product +')' as Product, 
    O.Target, O.Docket, o.Gross, o.Tare ,o.Net, o.ConNote, o.Description 
from IsoOrders O, Customer C, Product P
where o.Customer = c.Customer 
    and p.Product = o.Product
    and o.Traindate >= '12-14-2016'
    and o.Iso = '040'

2nd Query:

select top 1 isodeldocket, product from trans where container = '040' 
order by despatchdatetime desc

The last query's result was to be added as the last 2 columns of the 1st query.

Upvotes: 1

Views: 125

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

Solving the problem in your query

You can do it like this:

select 
  O.TrainDate, O.RailHead, O.Position as Pos, O.Iso, 
  C.CustomerName + ' (' + O.Customer + ')' as Customer, 
  P.ProductName + ' ('+ O.Product +')' as Product, 
  O.Target, O.Docket, O.Gross, O.Tare, O.Net, O.ConNote, O.Description, 
  -- Added these columns
  T.isodeldocket, 
  T.product
from 
  IsoOrders O, 
  Customer C, 
  Product P, 
  -- Added this derived table
  (select top 1 isodeldocket, product 
   from trans 
   where container = '040' 
   order by despatchdatetime desc) T
where O.Customer = C.Customer and P.Product = O.Product
and O.Traindate >= '12-14-2016'
and O.Iso = '040'

Improving the query by using ANSI JOIN syntax

While you're refactoring this query, why not move to ANSI JOIN, which greatly simplifies readability and clearly shows the intent / difference between (INNER) JOIN and CROSS JOIN:

select 
  O.TrainDate, O.RailHead, O.Position as Pos, O.Iso, 
  C.CustomerName + ' (' + O.Customer + ')' as Customer, 
  P.ProductName + ' ('+ O.Product +')' as Product, 
  O.Target, O.Docket, O.Gross, O.Tare, O.Net, O.ConNote, O.Description, 
  T.isodeldocket, 
  T.product
from IsoOrders O
join Customer C on O.Customer = C.Customer 
join Product P on P.Product = O.Product
-- CROSS JOIN more explicitly reveals your intention than a comma-separated table list
cross join (
  select top 1 isodeldocket, product 
  from trans 
  where container = '040' 
  order by despatchdatetime desc
) T
where O.Traindate >= '12-14-2016'
and O.Iso = '040'

Correlating the "outer" O.Iso with the "inner" trans.container value

From your comments, I take that you want to take this one step further and avoid duplicating the '040' "parameter". This can be done in SQL Server using APPLY:

select 
  O.TrainDate, O.RailHead, O.Position as Pos, O.Iso, 
  C.CustomerName + ' (' + O.Customer + ')' as Customer, 
  P.ProductName + ' ('+ O.Product +')' as Product, 
  O.Target, O.Docket, O.Gross, O.Tare, O.Net, O.ConNote, O.Description, 
  T.isodeldocket, 
  T.product
from IsoOrders O
join Customer C on O.Customer = C.Customer 
join Product P on P.Product = O.Product
-- Use CROSS APPLY rather than CROSS JOIN
cross apply (
  select top 1 isodeldocket, product 
  from trans 
  where container = O.Iso -- Now, you can access "outer" columns
  order by despatchdatetime desc
) T
where O.Traindate >= '12-14-2016'
and O.Iso = '040'

Upvotes: 2

Related Questions