-1

I need to create 1 table based on matching the records of two tables, lets call them table p and table c, in SQL Server. The manual way to do this in Excel is to match the Order Column in table p with the Order Column in table c. Once those are matched, you take the corresponding Batch Column in table c and match it with the Batch Column in table p. Once those are matched you then take that corresponding Order Column in table p and match it with table c again and thats the final item we want to pull. Any ideas?

Conner McCoy
  • 43
  • 8
  • You mention the order column twice, is that correct? Could you share some SQL that you have already attempted to achieve this with? – Anthony Norwood Feb 08 '23 at 14:46
  • In sql you "match" these tables together using `JOINS`. Sounds like most likely you want an inner join here. – Sean Lange Feb 08 '23 at 14:49
  • Yes I mention the order column twice because in the manual process they do it twice. They math the order numbers together, find the corresponding batch number, match those two together, then take the order number again and match those together. The I tried joining table p and table c on order = order and batch = batch but the query runs forever. I tried inner join as well but that didn't work. – Conner McCoy Feb 08 '23 at 15:02
  • Table definitions and sample data at a bare minimum are needed to help here. – Sean Lange Feb 08 '23 at 15:03

1 Answers1

1

It's hard to say for sure given the lack of table definitions, but it sounds as though you need to do two joins, once over to table c, then back again to table p, which you would alias in order to pull the right column value. Perhaps this will help you get started.

SELECT p1.Order
FROM p
INNER JOIN C
ON p.Order = c.Order
INNER JOIN p as p1
ON p1.brance = c.branch
Robert Sievers
  • 1,277
  • 10
  • 15
  • Sorry for the lack of transparency, its data that has to do with work but one is a Productions Table and the other is a Consumption Table. The goal is to find the ingredients consumed & the current process is to find the Order# in the Prod table, which gives you the finished good, match that Order# w/Order# in Cons. Table, take the corresp. Batch# in the cons table, match it w/the Batch# in the Prod, then take the corresp Order# in Prod table again and match it with the Order# in the consump table – Conner McCoy Feb 08 '23 at 21:33
  • I believed your suggestion worked Robert! Thanks – Conner McCoy Feb 08 '23 at 21:56