Schema allows multiple record link associations, SELECT DISTINCT from one of the tbls

[mdlueck]

I have db schema which needs to allow multiple parts to be linked to the same tool record. Example being LH / RH parts, or different colors of the parts, which use the same tool.

So, three tables involved:
[parts] <----> [partstoolslink] <----> [tools]

There is a key on the [partstoolslink] table preventing multiple records for the same part / tool linkage.

For a tool report, I need only unique tool listings, and the part number column may show any of the parts that tool record is linked to.

I keep getting duplicate tools records in the report due the fact that said tool is linked to multiple parts, and the partnumber field is included on the report.

If I, for example, GROUP BY the tool table, SQL Server complains that the partnumber field is not included in the GROUP BY. So, it wants to list duplicate tool records which are linked to different parts... which is NOT what I need.

Suggestions?

 

[mdlueck]

I came up with an idea I will try out in the morning...

Use one CTE (Common Table Expression) to select distinct the tooling records possible for the product. So drill all the way down through the hierarchy and select the distinct tool records only.

Then in another CTE, select all of the part records.

Finally join the two CTE's thus I am guaranteed at getting PartNumbers which are actually a part of the product, and only unique tool records.

 

[mdlueck]

SQL Server is being toooo helpful. It is realizing that more than one part record in the product is associated with a paticular tool, and the outer query using the CTE returns multiple listings of the various tools, with each partnumber the tool is associated with. As Charlie Brown says, "Rats!"

Suggestions how to pickup only one partnumber the paticular tool is associated with and NOT receive all part <--> tool associations? I just need unique tools and do not care which partnumber happens to be listed in the case that the same tool is associated with multiple parts within the product.

 

[mdlueck]

All day consulting on this query. Finally a breakthrough!

http://stackoverflow.com/questions/788984/getting-distinct-rows-from-a-left-outer-join#809925

The OUTER APPLY (SELECT TOP 1) seems to have produced the result I was after. Now to audit / test the query more fully.

A great end to a productive week of consulting!

 

[SQL_Hell]

Wish I'd seen this earlier, there are many ways you could have achieved this. Such as returning all part tool associations as a comma delimiated list in one field. Or using an OVER PARTITION BY statement with ROWNUMBER or RANK

 

[mdlueck]

Wish I'd seen this earlier

Never mind me posting deep SQL Server thoughts...

there are many ways you could have achieved this. Such as returning all part tool associations as a comma delimiated list in one field.

rrrr????? What I was after is all DISTINCT tools attached anywhere within the heirichy, and one of the PN#'s that happens to be associated with that tool. A comma deliminated list (extra data) would not be useful.

Or using an OVER PARTITION BY statement with ROWNUMBER or RANK

Glancing at search results for OVER PARTITION BY, those look nothing like what I was doing either.

The OUTER APPLY (SELECT TOP 1) solution makes sense... just odd where it plugs into the overall SQL query. Ja, to get just one part that happens to be attached to the specific tool and project, yes joining to the (SELECT TOP 1) indeed makes sense.