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.
Author |
Topic |
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-11-30 : 10:34:49
|
I have this query (below) that I am trying to have only return one record for each ComponentID. But since there are sometimes several part#'s that for the same ComponentID that match my search value it is returning the ComponentID several times. ComponentID is my Primary Key for the Sparts table. Is there a way to have it only return the componentID once? I hope that makes sense.Thanks for your help!StacySELECT sparts.ComponentID, sparts.Component, sparts.Specification, sparts.GraphicFilename, sparts.Priority, sparts.QtyonHand, sparts.PointofReorder, sparts.PrintonWO, sparts.OrderedAmnt, sparts.Choosen, sparts.BinFROM sparts INNER JOIN spartven ON sparts.ComponentID = spartven.ComponentIDWHERE (spartven.PartNum LIKE '%' + @SearchValue + '%') |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-30 : 10:39:59
|
[code]SELECT *from(SELECT sparts.ComponentID, sparts.Component, sparts.Specification, sparts.GraphicFilename, sparts.Priority, sparts.QtyonHand, sparts.PointofReorder, sparts.PrintonWO, sparts.OrderedAmnt, sparts.Choosen, sparts.Bin,ROW_NUMBER() OVER (PARTITION BY sparts.ComponentID Order by sparts.ComponentID) as SeqFROM sparts INNER JOIN spartven ON sparts.ComponentID = spartven.ComponentIDWHERE (spartven.PartNum LIKE '%' + @SearchValue + '%'))PWHERE P.Seq = 1[/code] |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-11-30 : 11:16:57
|
Awesome! Thank you very much. It works perfectly.Stacy |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-11-30 : 11:33:23
|
[CODE]SELECT DISTINCT sparts.ComponentID, sparts.Component, sparts.Specification, sparts.GraphicFilename, sparts.Priority, sparts.QtyonHand, sparts.PointofReorder, sparts.PrintonWO, sparts.OrderedAmnt, sparts.Choosen, sparts.BinFROM sparts INNER JOIN spartven ON sparts.ComponentID = spartven.ComponentIDWHERE (spartven.PartNum LIKE '%' + @SearchValue + '%')[/CODE]=================================================Hear the sledges with the bells - silver bells! What a world of merriment their melody foretells! |
|
|
|
|
|
|
|