Noob. Help w/ query! - SQL Server Forums

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

Noob. Help w/ query!

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-10 : 15:32:29

Hello,

I have this query I am working on:

SELECT PRODTABLE.PRODID, PRODTABLE.ITEMID, PRODTABLE.QTYSCHED, PRODBOM.ITEMID AS BOMID, PRODBOM.QTYBOMCALC,INVENTTABLE.ITEMBUYERGROUPID, PRODJOURNALTABLE.POSTEDDATE

FROM PRODTABLE RIGHT JOIN PRODBOM ON PRODTABLE.PRODID = PRODBOM.PRODID AND PRODTABLE.DATAAREAID = PRODBOM.DATAAREAID LEFT JOIN
INVENTTABLE ON PRODTABLE.ITEMID = INVENTTABLE.ITEMID AND PRODTABLE.DATAAREAID = INVENTTABLE.DATAAREAID LEFT JOIN
PRODJOURNALTABLE ON PRODTABLE.PRODID = PRODJOURNALTABLE.PRODID AND
PRODTABLE.DATAAREAID = PRODJOURNALTABLE.DATAAREAID

WHERE (PRODTABLE.PRODSTATUS = '4') AND (PRODTABLE.DATAAREAID = 'xxx') AND (PRODTABLE.BACKORDERSTATUS = '2') AND
(PRODTABLE.STUPDATE > @StartDate) AND (PRODTABLE.STUPDATE < @EndDate)

Instead of selecting inventtable.itembuyergroupid, I want to reference the prodbom.itemid and select the inventtable.itembuyergroupid from that result. (These tables can join on itemid)

Basically I want to take the itemid from the prodbom table, join it with the inventtable and take the itembuyergroupid from there based on the prodbom itemid.

ProdBom Table

ProdID itemid
1 400
2 410
3 420

InventTable

ItemId itemBuyerGroupId
400 4E
410 4F
420 4Z

Any ideas as to how do this?

Thank you in advance for the help.

Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 21:05:47

[code]SELECT pb.ProdID,i.itemBuyerGroupId
FROM ProdBom pb
JOIN Invent i
ON i.ItemId=pb.itemid[/code]

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-11 : 09:21:33

Thank you Visakh for your reply.

This is not what I am looking for however. I want to grab the prodbom.itemid from my original query and lookup inventtable.itembuyergroupid based off of the results of prodbom.itemid. I think I need to nest another select statement somehow.

Your query will work but how to nest it into this one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 09:38:13

so do you mean this?


SELECT *
FROM(SELECT pb.ProdID,i.itemBuyerGroupId
FROM ProdBom pb
JOIN Invent i
ON i.ItemId=pb.itemid)q1
JOIN
(
SELECT PRODTABLE.PRODID, PRODTABLE.ITEMID, PRODTABLE.QTYSCHED, PRODBOM.ITEMID AS BOMID, PRODBOM.QTYBOMCALC,INVENTTABLE.ITEMBUYERGROUPID, PRODJOURNALTABLE.POSTEDDATE

FROM PRODTABLE RIGHT JOIN PRODBOM ON PRODTABLE.PRODID = PRODBOM.PRODID AND PRODTABLE.DATAAREAID = PRODBOM.DATAAREAID LEFT JOIN
INVENTTABLE ON PRODTABLE.ITEMID = INVENTTABLE.ITEMID AND PRODTABLE.DATAAREAID = INVENTTABLE.DATAAREAID LEFT JOIN
PRODJOURNALTABLE ON PRODTABLE.PRODID = PRODJOURNALTABLE.PRODID AND 
PRODTABLE.DATAAREAID = PRODJOURNALTABLE.DATAAREAID

WHERE (PRODTABLE.PRODSTATUS = '4') AND (PRODTABLE.DATAAREAID = 'xxx') AND (PRODTABLE.BACKORDERSTATUS = '2') AND 
(PRODTABLE.STUPDATE > @StartDate) AND (PRODTABLE.STUPDATE < @EndDate)
)q2
ON q2.PRODID=q1.PRODID

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2008-12-12 : 10:00:08

Finally got it! Thanks for the help everyone...

Final query:
select tempview1.prodid, tempview1.itemid, tempview1.qtysched, tempview1.BOMID, tempview1.qtybomcalc, inventtable.itembuyergroupid, tempview1.posteddate
from
(SELECT PRODTABLE.PRODID as prodid, PRODTABLE.ITEMID as itemid, PRODTABLE.QTYSCHED as qtysched, PRODBOM.ITEMID AS BOMID, PRODBOM.QTYBOMCALC as qtybomcalc,
INVENTTABLE.ITEMBUYERGROUPID as itembuyergroupid, PRODJOURNALTABLE.POSTEDDATE as posteddate, prodtable.dataareaid as dataareaid

FROM PRODTABLE RIGHT JOIN
PRODBOM ON PRODTABLE.PRODID = PRODBOM.PRODID AND PRODTABLE.DATAAREAID = PRODBOM.DATAAREAID LEFT JOIN
INVENTTABLE ON PRODTABLE.ITEMID = INVENTTABLE.ITEMID AND PRODTABLE.DATAAREAID = INVENTTABLE.DATAAREAID LEFT JOIN
PRODJOURNALTABLE ON PRODTABLE.PRODID = PRODJOURNALTABLE.PRODID AND
PRODTABLE.DATAAREAID = PRODJOURNALTABLE.DATAAREAID

WHERE (PRODTABLE.PRODSTATUS = '4') AND (PRODTABLE.DATAAREAID = 'xxx') AND (PRODTABLE.BACKORDERSTATUS = '2') AND
(PRODTABLE.STUPDATE > @StartDate) AND (PRODTABLE.STUPDATE < @EndDate) ) as tempview1

inner join inventtable with (nolock) on inventtable.itemid = tempview1.bomid and inventtable.dataareaid = tempview1.dataareaid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 10:02:02

quote:
Originally posted by SQLSoaker

Finally got it! Thanks for the help everyone...

Final query:
select tempview1.prodid, tempview1.itemid, tempview1.qtysched, tempview1.BOMID, tempview1.qtybomcalc, inventtable.itembuyergroupid, tempview1.posteddate
from
(SELECT PRODTABLE.PRODID as prodid, PRODTABLE.ITEMID as itemid, PRODTABLE.QTYSCHED as qtysched, PRODBOM.ITEMID AS BOMID, PRODBOM.QTYBOMCALC as qtybomcalc,
INVENTTABLE.ITEMBUYERGROUPID as itembuyergroupid, PRODJOURNALTABLE.POSTEDDATE as posteddate, prodtable.dataareaid as dataareaid

FROM PRODTABLE RIGHT JOIN
PRODBOM ON PRODTABLE.PRODID = PRODBOM.PRODID AND PRODTABLE.DATAAREAID = PRODBOM.DATAAREAID LEFT JOIN
INVENTTABLE ON PRODTABLE.ITEMID = INVENTTABLE.ITEMID AND PRODTABLE.DATAAREAID = INVENTTABLE.DATAAREAID LEFT JOIN
PRODJOURNALTABLE ON PRODTABLE.PRODID = PRODJOURNALTABLE.PRODID AND
PRODTABLE.DATAAREAID = PRODJOURNALTABLE.DATAAREAID

WHERE (PRODTABLE.PRODSTATUS = '4') AND (PRODTABLE.DATAAREAID = 'xxx') AND (PRODTABLE.BACKORDERSTATUS = '2') AND
(PRODTABLE.STUPDATE > @StartDate) AND (PRODTABLE.STUPDATE < @EndDate) ) as tempview1

inner join inventtable with (nolock) on inventtable.itemid = tempview1.bomid and inventtable.dataareaid = tempview1.dataareaid