query problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

query problem

Author

Topic

sun919
Starting Member

22 Posts

Posted - 2007-01-16 : 11:42:50

hi there,
i m trying to create a report with query that should look like this

HN Name Surname School ICD10ID ICD10COde ICD10Name

4 John S Num 2 F0.05 something
3 F2.06 blah blah
5 Sam P other 5 F3.05 etc etc
12 F4.93 balh
3 F2.06 blah blah

at the moment the name john is repeat twice and sam three time
is there a way in which i can produce something like above in sql
this is my sql command

SELECT DISTINCT
Patient.HN, Patient.Names, Patient.Surname, School.SchoolName, DiscoverList.ICD10ID, ICD10.ICDName,
ICD10.ICD10Code
FROM School INNER JOIN
Patient ON School.SchoolID = Patient.SchoolID CROSS JOIN
DiscoverList INNER JOIN
ICD10 ON DiscoverList.ICD10ID = ICD10.ICD10ID
WHERE (DiscoverList.ICD10Date BETWEEN @a AND @b)

many thanks
sun

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-16 : 12:11:50

I am not sure if I follow your issue. The select Distinct will only bring back uniqe records. If more then one record is being brought back, that would mean that there is a field that is not the same between two of the records.

If you are not concerned with the information that is causing the record to be brought back more then 1 time then leave it off your query.

I.e
SELECT DISTINCT Patient.HN, Patient.Names, Patient.Surname, School.SchoolName
rather then
SELECT DISTINCT
Patient.HN, Patient.Names, Patient.Surname, School.SchoolName, DiscoverList.ICD10ID, ICD10.ICDName,
ICD10.ICD10Code

If this is not what you are referring to, then please refrase your question.

sun919
Starting Member

22 Posts

Posted - 2007-01-16 : 12:17:38

sorry remove the distinct key word ... cause putting there make no different .. my result at the moment show mulitple value of the same HN , Name and surname but i want it to only display once not repeatedly
many thanks
sun

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-16 : 12:49:14

Please verify that the two records that you are stating are the same match 100% when using the distinct key word. They shouldn't, even though they have the same HN,Name,Surname also verify that they have the same schoolname,icd10id,icdname,and icdcode.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources