CODEWITHSUNDEEP
sql-serverjoinduplicates
amza
amza

Reputation: 810

Join table where one has foreign key with duplicate values

So I have a primary table product with an ID as the primary key in it. I want to use this ID to link to another table which contains multiple copies of this primary key ID in it. The problem is that when I join I am getting a lot of duplicate values.

Here is my SQL statement:

DECLARE @BuildID bigint;
SET @BuildID = 1;

SELECT Product.Stream, Product.Strategy, Product.TopLevelPartName, 
        Product.ReleaseVersion, Product.MajorVersion, Product.MinorVersion, Product.BuildNumber, 
        AsBuiltEnvironment.Name, AsBuiltEnvironment.Value, Product.BuildTime FROM Product
INNER JOIN AsBuiltEnvironment
    ON Product.BuildID = AsBuiltEnvironment.BuildID
WHERE product.BuildID=@BuildID

Here is my current result set:

https://i.sstatic.net/Hp24H.png

I'm looking for something like this (where blank spaces would be null or empty): https://i.sstatic.net/ZBtxL.png

Is it possible without just doing multiple select statements?

Upvotes: 1

Views: 1334

Answers (1)

Nameless Voice
Nameless Voice

Reputation: 461

The output shown in the second picture isn't a complete set of normalised database records. Most of them don't mean anything on their own, because they are missing most of the data.

It looks like what you are trying to do is make the output of your query look nicer when displayed, but that's the job of the program which is doing the displaying, not of the SQL query.

For example, you could load the SQL data into an Excel spreadsheet, and then use conditional formatting to hide the extra duplicate entries (for an example of how to do this, see: http://www.techrepublic.com/blog/windows-and-office/a-quick-trick-for-hiding-duplicate-excel-values/)

Upvotes: 1

Related Questions