SQL Server How to use the result from a SQL query as keyword to do another SQL query?

vpfxa7rd  于 2023-08-02  发布在  其他
关注(0)|答案(2)|浏览(133)

I like to create a table which can show parts ID, parts name and its most recent supplier's name. These information are separated in 3 tables - BOM table(Field Name=>drawingN, partsID, partsName), purchase_history table (partsID, supplierID, purchaseDate) and supplier_list table(supplierID, supplierName).

In BOM table, parts are categorize by drawing number, so I use following SQL to get parts ID that I want:

SELECT drawingN, partsID, partsName
FROM BOM
WHERE drawingN = 'I16253'

In purchase_history, because the same parts may purchased from different supplier over time, so I use following SQL to screen out the most recent one. I also LEFT JOIN supplier_list to make supplier name shown in the table. In addition, supplierID 2991 and 2992 means oders that had been canceled or modified, so I what to rule them out too.

SELECT TOP 1 purchase_history.partsID, supplier_list.supplierName
FROM purchase_history
LEFT JOIN supplier_list ON purchase_history.supplierID = supplier_list.supplierID
WHERE partsID = 'A1234' AND supplierID <> '2991' AND supplierID <> '2992'
ORDER BY purchaseDate DESC

From first SQL query, database returns:

drawingNpartsIDpartsName
I16253A1234Bolts
I16253B5678Spring

From second SQL query, database returns:

partsIDsupplierName
A1234ABC Company

Is it possible to combine first and second SQL to make database returns something like this:

drawingNpartsIDpartsNamesupplierName
I16253A1234BoltsABC Company
I16253B5678SpringDEF Company

Now I just keep chaning partsID = 'A1234' to partsID = 'B5678' in second SQL to get B5678's company name. I tried subquery, but it doesn't go right. Thanks for the help.

8ljdwjyq

8ljdwjyq1#

Seems like it should be:

DECLARE @drawing varchar(11) = 'I16253';

SELECT b.drawingN, b.partsID, b.partsName, sub.supplierName
FROM dbo.BOM AS b
OUTER APPLY
(
  SELECT TOP (1) s.supplierName
  FROM dbo.purchase_history AS ph
  INNER JOIN dbo.supplier_list AS s
  ON ph.supplierID = s.supplierID
  WHERE ph.partsID = b.partsID
  AND s.supplierID NOT IN ('2991','2992')
  ORDER BY ph.purchaseDate DESC
) AS sub
WHERE b.drawingN = @drawing;

I tried to work out what made the most sense based on the description, not the existing code, using this example on fiddle .

7rtdyuoh

7rtdyuoh2#

This should work (untested!):

SELECT drawingN, partsID, partsName, x.supplierName
FROM BOM
INNER JOIN (
    SELECT TOP 1 purchase_history.partsID, supplier_list.supplierName
    FROM purchase_history
    LEFT JOIN supplier_list ON purchase_history.supplierID = supplier_list.supplierID
    WHERE partsID = 'A1234' AND supplierID <> '2991' AND supplierID <> '2992'
    ORDER BY purchaseDate DESC
           ) x ON x.partsID = BOM.partsID
WHERE drawingN = 'I16253'
  • The x before the ON is an alias which defines how to refer to the results of this sub-query.
  • THEN ON-clause ( x.partsID = BOM.partsID ) tells how to glue the two results together,
  • x.supplierName is used to get the supplierName from the subquery, which we named x .

NOTE: As was (already noted) in the comments, above is just a simple glue-ing (is that a proper English word?) two queries to one.

Give me some time to get a proper SQL statement... 😉

相关问题