Completely Stumped/Brain Teaser

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Completely Stumped/Brain Teaser

Author

Topic

mad_fox
Starting Member

3 Posts

Posted - 2010-02-04 : 15:10:07

I've been trying to figure this problem out for a VERY long time now and I haven't made any progress at all! ANY help would be greatly appreciated.

I have a group of things (lets say lego pieces) to select from and I have a another group of things (lets say lego robots) that can be built from the pieces provided.

Not every piece must be used from the lego pieces provided to build a robot, but the pieces provided must contain every piece necessary to build a lego robot robot.

What I am trying to do is run a query that returns a list of lego robots that can be built, given a list of available lego pieces.

How can something like this be accomplished?

Thanks!

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 02:30:21

Lets say you have tables Robot (name of the Robot etc.), BillOfMaterials (all the pieces, and qty, required to build the Robot), and Inventory (all the pieces you own, and the qty of each that you have)


SELECT R.RobotName,
       COUNT(BOM.LegoPiece_ID) AS DistinctPieces,
       SUM(BOM.QtyNeeded) AS TotalPieces
FROM Robot AS R
    JOIN BillOfMaterials AS BOM
        ON BOM.Robot_ID = R.Robot_ID
    JOIN Inventory AS I
        ON I.LegoPiece_ID = BOM.LegoPiece_ID
       AND I.QtyOwned >= BOM.QtyNeeded
GROUP BY R.RobotName

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-05 : 08:38:26

Hm, I would prefer building cars...as far as I know a car consist of 4 wheels, a chassy, 6 "windows" and 3 mirrors (2 on each outer side, 1 inside). At least to me this sounds more like the requirements (don't have a solution though):

--> Problem: how many cars can you build with the given ObjectSpecification and the amount of parts in stock?

DECLARE @ObjectType TABLE (
  ObjTypeID int NOT NULL PRIMARY KEY CLUSTERED,
  ObjTypeName varchar(200)
  )
DECLARE @PartsInStock TABLE (
  PartID int NOT NULL PRIMARY KEY CLUSTERED,
  PartName varchar(200) NOT NULL,
  Available int NOT NULL
  )
DECLARE @ObjectSpecifications TABLE (
  ObjTypeID int,
  PartID int,
  Quantity int
  )
INSERT INTO @ObjectType SELECT 1, 'car'

INSERT INTO @PartsInStock 
SELECT 1, 'Window', 32 UNION ALL
SELECT 2, 'Chassy', 3 UNION ALL
SELECT 3, 'Wheel', 55 UNION ALL
SELECT 4, 'Mirror', 7

INSERT INTO @ObjectSpecifications 
SELECT 1, 1, 6 UNION ALL
SELECT 1, 2, 1 UNION ALL
SELECT 1, 3, 4 UNION ALL
SELECT 1, 4, 3

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-05 : 08:51:12

Suggestion of expected output:

--> Expected output
SELECT 'Car' AS ObjectType, 1 AS ObjectNumber, 6 AS Window, 1 AS Chassy, 4 AS 'Wheel', 3 AS 'Mirror' UNION ALL
SELECT 'Car' AS ObjectType, 2 AS ObjectNumber, 6 AS Window, 1 AS Chassy, 4 AS 'Wheel', 3 AS 'Mirror'

(for early readers I changed the mirrors in stock to 7)

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-02-05 : 12:00:21

I, for one, welcome our new Lego Robot Overlords.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 12:09:39

I'd quite like a new car ...

mad_fox
Starting Member

3 Posts

Posted - 2010-02-08 : 14:47:40

quote:
Originally posted by Kristen

Lets say you have tables Robot (name of the Robot etc.), BillOfMaterials (all the pieces, and qty, required to build the Robot), and Inventory (all the pieces you own, and the qty of each that you have)

SELECT R.RobotName,
COUNT(BOM.LegoPiece_ID) AS DistinctPieces,
SUM(BOM.QtyNeeded) AS TotalPieces
FROM Robot AS R
JOIN BillOfMaterials AS BOM
ON BOM.Robot_ID = R.Robot_ID
JOIN Inventory AS I
ON I.LegoPiece_ID = BOM.LegoPiece_ID
AND I.QtyOwned >= BOM.QtyNeeded
GROUP BY R.RobotName

Thank you very much for your reply Kristen. I'm trying to understand how you're BillOfMaterials table is set up. Would there only be 3 columns - LegoPiece_ID, QtyNeeded and Robot_ID, with the LegoPiece_ID and Robot_ID as the composite key? Or did you have something different in mind.

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 08:52:38

BillOfMaterials is intended to just list the components (and Qty) that are needed to build the product.

e.g. a chair needs a seat, back and 4 legs.

http://en.wikipedia.org/wiki/Bill_of_materials

mad_fox
Starting Member

3 Posts

Posted - 2010-02-11 : 13:51:38

quote:
Originally posted by Kristen

BillOfMaterials is intended to just list the components (and Qty) that are needed to build the product.

e.g. a chair needs a seat, back and 4 legs.

http://en.wikipedia.org/wiki/Bill_of_materials

Thanks, my question is more of a design/organizational question now. I'm trying to visualize what the billOfMaterials table would look like. What I said above is how I visualized what you said, but I'm not so sure now, judging from you're last post.

Would you mind going into a little more detail? I understand that BOM is a list of whats needed, but I don't understand how BOM can hold lists of whats needed for each robot.

Say BOM had a chair and a table in it, what would that look like?

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 13:57:01

The BOM would be for a "set" - comprising a specific Chair and a specific Table.

(This example needs a BOM that is capably of handling sub-assemblies)

The Chair and the Table would each have their own BOM records, so the Chair needs Seat, Back and 4 legs; Table needs Table-top and 4 [probably different!] legs.

Another example might be a Stool and a Chair. Maybe the BOM for the Chairs is "Stool plus Back" and the Stool is "Seat and 4 legs"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources