Return Unique Part Number Values to Web Form

Question

Return Unique Part Number Values to Web Form

2Tall

SSChampion

Points: 14644
More actions
April 30, 2008 at 12:03 am

#380246

Hi. I have to display data to end users. I created 2 tables in SQL Database, Part & PartNarrative. The data is from an old flat file database. Part contains PartNumber as does PartNarrative.
A user enters a part number in a web form then hits search. The GridvView then runs SQL query and displays data relative to that part. However the Part Number etc is repeated for each record returned? I want it to return unique values for PartNumber / PartDescription etc...... Basically each part has a number of NarrativeLines, Narratives and NarrativePrintFlags. So Part 12345 might have 25 NarrativeLines + 25 Narratives (basically a marker and note against a particular part, each marker and narrative is unique to the PartNumber). When I run the code below Part Number / Part Description / PartDrawingNumber are currently returned 25 times along with the PartNarrative.
SELECT DISTINCT TOP 200 Part.PartNumber, Part.PartDescription, ISNULL (Part.PartDrawingNumber,'N/A') AS PartDrawingNumber, Part.UOM, PartNarrative.NarrativeLine, PartNarrative.Narrative,
PartNarrative.NarrativePrintFlag
FROM Part INNER JOIN
PartNarrative ON Part.PartNumber = PartNarrative.PartNumber
GROUP BY Part.PartNumber, Part.PartDescription, Part.PartDrawingNumber, Part.UOM, PartNarrative.NarrativeLine, PartNarrative.Narrative, PartNarrative.NarrativePrintFlag
Is it possible to achieve what I am looking to do?
Can anyone help on this one?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736352 More actions · Answer 1

First, this is really a T-SQL questions, I've moved it to that forum.

Second, you are returning the narratives, so you get distinct rows. If you don't want the narratives shown, don't return them. Otherwise how can you tell what's unique.

If you'll use that stuff in the page somewhere, perhaps a DIV that's hidden or something, use a loop in the web page to not repeat the part numbers and only print/write to a grid when they change.

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks Steve. It's the narrative I want to return...............one Part / Many Narratives (well that's the plan 🙂 )

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I don't know what forum this was in originally, but I have to disagree with Steve about it being a T-SQL question. You have done all you can in T-SQL to get distinct records as T-SQL will return each column for each row so you will get the same part number and description for each Narrative for the part.

Now you could "cheat" this in T-SQL by using a stored procedure with a temp table or table variable and then update the part number and description value from each row after the first. Something like this (uses adventureworks):

[font="Courier New"]DECLARE @table TABLE (row_id INT, salesorderid INT, orderdate smalldatetime, orderqty INT, productid INT, unitprice money)

INSERT INTO @table

SELECT

Row_Number() OVER (Partition BY S.salesOrderid, S.orderdate ORDER BY S.salesorderid, S.orderdate) AS row_Id,

S.salesorderid,

S.orderdate,

D.orderqty,

D.productid,

D.unitprice

FROM

Sales.SalesOrderHeader S JOIN

Sales.SalesOrderDetail D ON

S.salesorderid = D.salesorderid

UPDATE @table

SET salesorderid = NULL,

orderdate = NULL

WHERE

row_id > 1

SELECT * FROM @table[/font]

You would need to replace salesorderheader and salesorderdetails with your tables.

But, this is a display issue so you really should learn how to do it in the UI. I'm not a UI guy so I can't help there.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

2Tall SSChampion Points: 14644 More actions · Answer 4

Thanks Jack. I will look into the UI side of things. I will run though your code and see how it all works. The user request is not priority, at least they can get at the data (even if the formatting is not ideal). Running down an old system, users need to be able to reference data not being migrated.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper