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

vpfxa7rd 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(134)

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:

drawingN	partsID	partsName
I16253	A1234	Bolts
I16253	B5678	Spring

From second SQL query, database returns:

partsID	supplierName
A1234	ABC Company

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

drawingN	partsID	partsName	supplierName
I16253	A1234	Bolts	ABC Company
I16253	B5678	Spring	DEF 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.

sql-server

来源：https://stackoverflow.com/questions/76744394/how-to-use-the-result-from-a-sql-query-as-keyword-to-do-another-sql-query

2条答案

按热度按时间

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 .

赞(0）回复(0）举报 2023-08-02

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... 😉

赞(0）回复(0）举报 2023-08-02

我来回答

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

2条答案

相关问题

热门标签

最新问答