February 7, 2014 at 10:27 am
Posted - 02/07/2014 : 12:10:45 Show Profile Email Poster Edit Topic Reply with Quote Delete Topic Nuke Spam!
I have to get the min price value V.Price for each P.PartID but the PartID can have for than one price value.
Below is what I have so far Also I am getting other FRID besides 0 as well.
Please help
Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID
From Parts As P
Right Join Vend2Part As V
On P.PartID = V.PartID And P.FRID = V.FRID
Left Join companies As C
On C.COID = V.COID And C.FRID = V.FRID
Left Join db_manuf As M
On P.manufacturer = M.ManID
Where P.FRID = 0 And P.cost > (V.price * 1.02) or P.cost < (V.price * 0.98) And V.price = (Select Min(Price) From Vend2Part)
Order By P.FRID
February 7, 2014 at 1:11 pm
As it stands, you are using the minimum price from Vend2Part for ANY partid, not just the one that you're interested in for any given row.
maybe replace
(Select Min(Price) From Vend2Part)
with
(Select Min(Price) From Vend2Part vsub where vsub.PartID = P.PartID)
Sort of a wild guess, since I don't have a table to test against, and the 'vsub' alias might not be needed (or for that matter even work LOL)
February 7, 2014 at 1:27 pm
You also some parenthesis in your where clause that are very likely incorrect.
See if this might be more of what you are looking for.
Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID
From Parts As P
Right Join Vend2Part As V On P.PartID = V.PartID And P.FRID = V.FRID
Left Join companies As C On C.COID = V.COID And C.FRID = V.FRID
Left Join db_manuf As M On P.manufacturer = M.ManID
Where P.FRID = 0
And (P.cost > (V.price * 1.02) or P.cost < (V.price * 0.98)) --you were missing parenthesis here
And V.price = (Select Min(Price) From Vend2Part vsub where vsub.PartID = P.PartID)
Order By P.FRID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2014 at 1:31 pm
Sean Lange (2/7/2014)
You also some parenthesis in your where clause that are very likely incorrect.See if this might be more of what you are looking for.
Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID
From Parts As P
Right Join Vend2Part As V On P.PartID = V.PartID And P.FRID = V.FRID
Left Join companies As C On C.COID = V.COID And C.FRID = V.FRID
Left Join db_manuf As M On P.manufacturer = M.ManID
Where P.FRID = 0
And (P.cost > (V.price * 1.02) or P.cost < (V.price * 0.98)) --you were missing parenthesis here
And V.price = (Select Min(Price) From Vend2Part vsub where vsub.PartID = P.PartID)
Order By P.FRID
What was I doing wrong when I was getting FRID other than 0... Was it just the parenthesis is all.
Thanks
February 7, 2014 at 1:42 pm
Dieselbf2 (2/7/2014)
Sean Lange (2/7/2014)
You also some parenthesis in your where clause that are very likely incorrect.See if this might be more of what you are looking for.
Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID
From Parts As P
Right Join Vend2Part As V On P.PartID = V.PartID And P.FRID = V.FRID
Left Join companies As C On C.COID = V.COID And C.FRID = V.FRID
Left Join db_manuf As M On P.manufacturer = M.ManID
Where P.FRID = 0
And (P.cost > (V.price * 1.02) or P.cost < (V.price * 0.98)) --you were missing parenthesis here
And V.price = (Select Min(Price) From Vend2Part vsub where vsub.PartID = P.PartID)
Order By P.FRID
What was I doing wrong when I was getting FRID other than 0... Was it just the parenthesis is all.
Thanks
I am not 100% certain that what I posted is correct. I suspect it was a combination of the correct parenthesis and the proper subquery.
The issue with the parenthesis is that you want 3 conditions to be true:
1) p.FRID = 0
2) P.cost > (V.price * 1.02) OR P.cost < (V.price * 0.98)
3) V.price = (Select Min(Price) From Vend2Part vsub where vsub.PartID = P.PartID)
When you leave out the parenthesis it becomes 1 of 2 conditions:
1) p.FRID = 0 AND P.cost > (V.price * 1.02)
OR
2) P.cost < (V.price * 0.98) AND V.price = (Select Min(Price) From Vend2Part vsub where vsub.PartID = P.PartID)
Remember the order of precedence with logical operators.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply