Distinct Values Programming - SQL Server Forums

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

Distinct Values Programming

Author

Topic

rodneytheodore
Starting Member

5 Posts

Posted - 2012-04-27 : 15:18:53

Hello All,

I am trying to join a few tables together but want the data to display without duplicate values in some of the columns.

Table A
Patient Name – Repeats for every row of data
Birth Date – Repeats for every row of data
Specimen Taken Date – Repeats with matching specimen name and result
Specimen Name – Displays whenever available for that specific date
Specimen Result – Always unique

Table B
Patient Name
Birth Date
Specimen Taken Date(B)
Specimen Name (B)
Specimen Result (B)

Table C
Patient Name
Birth Date
Specimen Taken Date (C)
Speciment Name (C)
Specimen Result (C)

I am trying to merge these tables, between the tables the number of rows associated with each patient is not the same. A patient might have 12 rows of data in Table A, 5 in Table B, and 3 in Table C. Basically I don’t want repeated column data displayed other than the Patient Name and Birth Date.

I want my data to display like this:

The tables would be joined based on Patient Name. When I tried to merge the tables it displayed with Specimen Name (A) repeating for each Specimen (B) where I just want it listed independent of the other table columns. How do I accomplish that?

I started out with code like this

SELECT TableA.PatientName, TableA.BirthDate, TableA.SpecimenDate, TableA.SpecimenName, TableA.SpecimenResult, TableB.SpecimenDate,
TableB.SpecimenName, TableB.SpecimenResult, TableC.SpecimenDate, TableC.SpecimenName, TableC.SpecimenResult
FROM TableA
JOIN TableB
ON TableA.PatientName=TableB.PatientName
JOIN Table C
ON TableA.PatientName=TableC.PatientName;

From searching on google I think I need to use SELECT and DISTINCT statements within the JOIN statements but not sure how to put it all together. Could you possibly tell me how to program for this output?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:22:04

you need to have a field in all tables to indicate which specimen values to be merged from tables. You cant rely upon order of records in table to do merge as there's no concept of order in sql table. I hope you've identifier field for specimen in each table to identify similar record values from each

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rodneytheodore
Starting Member

5 Posts

Posted - 2012-04-27 : 15:30:12

Would the identifier have to be unique to each specimen type? I'm new to SQL programming so sorry if I'm not following.

quote:
Originally posted by visakh16

you need to have a field in all tables to indicate which specimen values to be merged from tables. You cant rely upon order of records in table to do merge as there's no concept of order in sql table. I hope you've identifier field for specimen in each table to identify similar record values from each

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:41:37

yep...it needs to be unique for each of specimens in table as that's only thing which ensure you merge corresponding rows across tables. Since the Patient Name ,Birth Date is same for many rows, joining with them effective does cartesian joins among all rows with same Patient Name,Birth Date value which is why you get so many duplicates in the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rodneytheodore
Starting Member

5 Posts

Posted - 2012-04-27 : 15:59:13

Ok if I have unique identifiers for each specimen how do I incorporate that into the join?

quote:
Originally posted by visakh16

yep...it needs to be unique for each of specimens in table as that's only thing which ensure you merge corresponding rows across tables. Since the Patient Name ,Birth Date is same for many rows, joining with them effective does cartesian joins among all rows with same Patient Name,Birth Date value which is why you get so many duplicates in the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 16:02:14

[code]
SELECT TableA.PatientName, TableA.BirthDate, TableA.SpecimenDate, TableA.SpecimenName, TableA.SpecimenResult, TableB.SpecimenDate,
TableB.SpecimenName, TableB.SpecimenResult, TableC.SpecimenDate, TableC.SpecimenName, TableC.SpecimenResult
FROM TableA
JOIN TableB
ON TableA.PatientName=TableB.PatientName
AND TableA.UniqueSpecimenID = TableB.UniqueSpecimenID
JOIN Table C
ON TableA.PatientName=TableC.PatientName;
AND TableA.UniqueSpecimenID = TableC.UniqueSpecimenID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rodneytheodore
Starting Member

5 Posts

Posted - 2012-04-27 : 16:14:12

Ok that makes sense concept wise, however the specimens in each table have no relation to each other beside being from the same patient. Also there may be 100 specimens for a patient in table A and 10 for that same patient in table B, therefore wouldn't joining based on the unique identifier not work for that case?

quote:

Originally posted by visakh16


SELECT TableA.PatientName, TableA.BirthDate, TableA.SpecimenDate, TableA.SpecimenName, TableA.SpecimenResult, TableB.SpecimenDate,
TableB.SpecimenName, TableB.SpecimenResult, TableC.SpecimenDate, TableC.SpecimenName, TableC.SpecimenResult
FROM TableA
JOIN TableB
ON TableA.PatientName=TableB.PatientName
AND TableA.UniqueSpecimenID = TableB.UniqueSpecimenID 
JOIN Table C
ON TableA.PatientName=TableC.PatientName;
AND TableA.UniqueSpecimenID = TableC.UniqueSpecimenID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 16:15:47

if they dont have any relation with each other then whats the purpose of linking them from both tables? whats exactly the problem statement you're trying to implement with above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rodneytheodore
Starting Member

5 Posts

Posted - 2012-04-27 : 16:29:33

I'm trying to create a master table with all information to be able to create pivot table reports.

quote:
Originally posted by visakh16

if they dont have any relation with each other then whats the purpose of linking them from both tables? whats exactly the problem statement you're trying to implement with above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 16:33:04

then you should be merging them vertically using union all and then apply pivot logic over them based on patient fields to get specimen as columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources