Assistance with SQL Query to break down bill of materials

I am try to breakdown a BOM into its lowest items. I have got 2 tables.

Table A:

ITEM FORMULA
--------------------
7024 BDD39
7024 BDD94


Table B

FORMULA RAW_ITEMS
---------------------------------------
BDD94 BioxDD 94 RMW0005
BDD94 BioxDD 94 RMP0007
BDD94 BioxDD 94 RMD0010
BDD94 BioxDD 94 RMH0009
BDD39 BioxDD 39 RMA0005
BDD39 BioxDD 39 RMW0006
BDD39 BioxDD 39 RMS0005


Is there any hint as to how to go about to replace the item in Table A with all the 3 items in Table B?

My expected output is:

Item
-----------------------------------------
7024 BDD39 BioxDD 39 RMA0005
7024 BDD39 BioxDD 39 RMW0006
7024 BDD39 BioxDD 39 RMS0005
7024 BDD94 BioxDD 94 RMH0007
7024 BDD94 BioxDD 94 RMA0007
7024 BDD94 BioxDD 94 RMW0010
7024 BDD94 BioxDD 94 RMS0009


Any help is appreciated.

3 Answers
3

By using CONCAT function and INNER JOIN, you may get the desired output :

SELECT CONCAT(a.Item, ' ', a.Formula) AS Item,
b.raw_items
FROM TableA a INNER JOIN TableB b
ON ( b.Formula = a.Formula )
WHERE a.Formula = 'BDD39';


or Use without assigning the vale BDD39 to Formula:

SELECT Item, raw_items
FROM
(
SELECT CONCAT(a.Item, ' ', a.Formula) AS Item,
b.raw_items,
dense_rank() over (order by a.Formula) dr
FROM TableA a INNER JOIN TableB b
ON ( b.Formula = a.Formula )
) q
WHERE q.dr = 1;


SQL Fiddle Demo

What you're asking for is simply an inner join, which is the basis of SQL:

SELECT
A.ITEM,
A.FORMULA,
B.RAW_ITEMS
FROM
TableA A
INNER JOIN
TableB B
ON A.Formula = B.Formula


If it all has to be in one column you just concatenate it up. Here's one way

SELECT
A.ITEM + ' ' +
A.FORMULA + ' ' +
B.RAW_ITEMS As Item
FROM
TableA A
INNER JOIN
TableB B
ON A.Formula = B.Formula


Here's another

SELECT CONCAT(
A.ITEM, ' ',
A.FORMULA,' ',
B.RAW_ITEMS) As Item
FROM
TableA A
INNER JOIN
TableB B
ON A.Formula = B.Formula


SELECT CONCAT(ITEM, " ", TA.FORMULA, " ", RAW_ITEMS) AS ITEM
FROM TABLEA TA, TABLEB TB
WHERE TA.FORMULA = TB.FORMULA


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.