Using multiple and interdepended CROSS-APPLY across multiple tables

Question

How can I use either CROSS APPLY (or INNER JOIN) to get data from one table based on the values in other tables?

I.e. I have the following tables:

Table Descriptions:

ProdID	Description	TrackNum
361	Test 1	499
388	Test 2	003
004		5599
238	Test 3	499
361	Test 10	555
004	Test 40	555

Table Products:

ProdID	ProductName	Price
361	P1	5.00
388	P2	5.00
004	P3	12.00
238	P4	6.00
515	P5	7.00
636	P6	7.00
775	P7	7.00
858	P8	8.00

Table Invoices:

ProdID	TrackNum	InvoiceID
361	499	718
388	199	718
004	499	718
238	499	718
361	555	333
004	555	444
361	111	444
388	222	333
616	116	565
717	116	565
361	003	221
388	003	221
004	5599	728

What I need my query to do is to:

1. Go into Invoices table first, and get only records that matches specified InvoiceID and TrackNum;
2. Then go into Products table and get only rows that have matches on ProdID between the data I pulled out in Step #1 and the data existis in the Products table.
3. Then finally get all columns from the Descriptions table, but only for the rows which I got in the Step #2 and which matches on ProdID.

What I need to have at the end is something like this (if I get more columns that is fine, but I do not want to get more rows):

ProdID	Description	TrackNum
361	Test 1	499
004		5599
238	Test 3	499

I have following query (and I have tried using INNER JOIN and CROSS APPLY) - but it returns me more rows than I need:

SELECT * FROM [Descriptions] AS [DES] 
CROSS APPLY
(
    select * from [Invoices] AS [INV] where [INV].[TrackNum] = '499' AND [INV].[InvoiceID] = '718'
) [INV]
CROSS APPLY 
    (
        select * from [Products] AS [GP] 
        WHERE [GP].[ProdID] = [INV].[ProdID]
    ) [GP2]
WHERE 
[DES].[ProdID] = [GP2].[ProdID]
order by [DES].[ProdID] asc

Answer 1

Based on what you describe you want the following, start with your Invoices table and a where clause to get the right rows, then join on Products and Descriptions.

I'm also guessing that you want to match the Description on TrackNum? Since it appears you have a unique Description per ProdId/TrackNum combination.

select [INV].[ProdID], [DES].[Description], [INV].[TrackNum]
from [Invoices] as [INV]
inner join [Products] as [GP] on [GP].[ProdID] = [INV].[ProdID]
inner join [Descriptions] on [DES].[ProdID] = [GP].[ProdID] and [DES].[TrackNum] = [INV].[TrackNum]
where [INV].[TrackNum] = '499' AND [INV].[InvoiceID] = '718'
order by [DES].[ProdID] asc;

Note: You normally only use a 'CROSS APPLY' for queries where you want to run/evaluate something per row in your main table.

Answer 2

SELECT
  *
FROM
  invoices   AS i
LEFT JOIN
  descriptions   AS d
    ON  d.prodid = i.prodid
    AND d.tracknum = i.tracknum -- you don't have this, but I think it's required.
LEFT JOIN
  products   AS p
    ON  p.prodid = i.prodid
WHERE
      i.invoiceid = 718
  AND i.tracknum = 499
ORDER BY
  i.prodid

One thing that concerns me is that both the invoices and descriptions have a column named tracknum, but your query and expected data indicate that you don't want to include that in the join? That's very confusing and either a poor column name, or a mistake in your query and example results.

Answer 3

In this case the Inner Join is sufficient. You don't need to use Cross Apply