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
 Select Query Returning One Record Per PK

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!
Stacy
SELECT        sparts.ComponentID, sparts.Component, sparts.Specification, sparts.GraphicFilename, sparts.Priority, sparts.QtyonHand, sparts.PointofReorder, 
sparts.PrintonWO, sparts.OrderedAmnt, sparts.Choosen, sparts.Bin
FROM sparts INNER JOIN
spartven ON sparts.ComponentID = spartven.ComponentID
WHERE (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 Seq
FROM sparts INNER JOIN
spartven ON sparts.ComponentID = spartven.ComponentID
WHERE (spartven.PartNum LIKE '%' + @SearchValue + '%')
)P
WHERE P.Seq = 1[/code]
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-11-30 : 11:16:57
Awesome!
Thank you very much. It works perfectly.
Stacy
Go to Top of Page

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.Bin
FROM sparts INNER JOIN
spartven ON sparts.ComponentID = spartven.ComponentID
WHERE (spartven.PartNum LIKE '%' + @SearchValue + '%')[/CODE]


=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!
Go to Top of Page
   

- Advertisement -