SQL Database Query Help

[NathanBWF]

Trying to pull data from two tables in our inventory database. I need to pull the item number, the components that are used to make that item, and the costs of each of the individual components. Using the following query, I get all of the information I need except I can't figure out how to pull the costs of each individual component instead of the cost of the item itself:

SELECT bom50100.itemnmbr, bom50100.itemdesc, bom50100.cmptitnm, iv00101.currcost
FROM bom50100
LEFT OUTER JOIN
iv00101
ON bom50100.itemnmbr = iv00101.itemnmbr

Anyone know how I would get it to pull the individual components costs in the iv00101 table rather than the cost of the item?

 

[Mark R]

What are the fields in the 2 tables, and what do they mean - I'm struggling to decode your field names.

What table stores the prices for the individual components?

 

[Markbnj]

Yeah, I was struggling with this too. It may be that a left outer is not appropriate here, or it may be something else, but it's very hard to tell from your sample code.

If you're very far along it's probably too late to do much about it, but I strongly suggest avoiding names like this in the future. There's no reason for them.

 

[NathanBWF]

Originally posted by: Mark R
What are the fields in the 2 tables, and what do they mean - I'm struggling to decode your field names.

What table stores the prices for the individual components?

Sorry...should have been a bit more clear...

itemnmbr = This is the unique identifier for the item.
itemdesc = Description of the item.
cmptitnm = Similar to itemnmbr as you can have an item that is made up of multiple items. Ex: Item number 195205 is made up of items 300254, 330301, 510203 and 510215.
iv00101.currcost = The cost of the item/components. This is where I'm getting stuck as I need to pull the cost of each individual item that goes into making the 'main' item. So for the example above, I need to pull the costs of items 300254, 330301, 510203 and 510215 individually, not the overall cost of item 195205 which is what my query is currently doing.

Unfortunately 'currcost' isn't in the 'bom50100' table, which is why I need to use a JOIN statement.

Thanks...

EDIT: Here is a screenshot of what my query gives me:

Link

As you can see, the currcost is the same as it's pulling the currcost for item 195205, and not for each individual item that makes up item 195205.

 

[snapper316]

you're missing a table or two in your list...

Tell us the entire list of tables you have with their fields and someone could help you write a query. And the field names are awful but at least with the Table/Field list we could help you better.

Such as: The name of the table that holds your components and which components make up what item.

 

[NathanBWF]

Gah...well I figured it out. Changed:

ON bom50100.itemnmbr = iv00101.itemnmbr

To:

ON bom50100.cmptitnm = iv00101.itemnmbr

And I now get the costs of the individual columns.

 

[MmmSkyscraper]

The table and field names are horrible.

 

[troytime]

Originally posted by: MmmSkyscraper
The table and field names are horrible.

 

[NathanBWF]

Originally posted by: troytime

Originally posted by: MmmSkyscraper
The table and field names are horrible.

You can thank Microsoft Great Plains for that.

 

[MmmSkyscraper]

Originally posted by: NathanBWF

Originally posted by: troytime

Originally posted by: MmmSkyscraper
The table and field names are horrible.

You can thank Microsoft Great Plains for that.

Thanks Microsoft Great Plains, you suck :thumbsup:

 

[Markbnj]

Originally posted by: MmmSkyscraper

Originally posted by: NathanBWF

Originally posted by: troytime

Originally posted by: MmmSkyscraper
The table and field names are horrible.

You can thank Microsoft Great Plains for that.

Thanks Microsoft Great Plains, you suck :thumbsup:

Haha.

"Hey, we're building a framework here! We need framework-sounding table names! What's a good acronym?"

"I like BOM50100."

"Where the hell did that come from?"

"It was my bot's name in AC1"

"Cool! Let's make all our tables BOM50100.something!"

"And vowels, don't forget to get rid of vowels."

"Yeah, no vowels. So it's BOM50100.smthng."

 

[Patt]

I can get in on the Microsoft Great Plains hating :thumbsup: <-- that's not a thumb :|

I have to support that piece of garbage at work.