Ambiguous values returned - 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)

Ambiguous values returned

Author

Topic

stranter
Starting Member

17 Posts

Posted - 2008-05-15 : 10:48:29

Hello,

I'm brand new to this forum and I'm stuck. We've got a query which queries three tables: booking, catheter and patients. The following (dodgy) query has pulled off the data for years, without problems:


SELECT
CARDIAC_CATHETER.CARDIAC_CATHETER_ID,
CARDIAC_CATHETER.OPERATOR1, 
CARDIAC_CATHETER.TEST_DATE, 
BOOKING.STATUS, 
BOOKING.TEST_DATE AS BOOKING_TEST_DATE, 
BOOKING.PROCEDURE_DATE_TIME, 
CARDIAC_CATHETER.ITEM_NO, 
CARDIAC_CATHETER.BILLING_CATEGORY, 
PATIENTS.REFERRING_PHYSICIAN1, 
PATIENTS.HOSPITAL_UNIT_NUMBER  
FROM 
(CARDIAC_CATHETER INNER JOIN PATIENTS ON CARDIAC_CATHETER.PATIENT_ID = PATIENTS.PATIENT_ID)
LEFT OUTER JOIN BOOKING ON PATIENTS.PATIENT_ID = BOOKING.PATIENT_ID AND 'CATHETER' = BOOKING.CLINIC AND BOOKING.STATUS <> 'CANCELLED' AND  BOOKING.PROCEDURE_DATE_TIME >= (CARDIAC_CATHETER.TEST_DATE - 1) 
AND BOOKING.PROCEDURE_DATE_TIME < (CARDIAC_CATHETER.TEST_DATE + 1) 
WHERE
CARDIAC_CATHETER.TEST_DATE > '2005-04-01' AND CARDIAC_CATHETER.BILLING_CATEGORY LIKE '3%'

About 2500 perfect rows and 12 rows which have duplicate catheter id values. There are six pairs of duplicated records. It's only the cardiac_catheter.catheter_id and cardiac_catheter.test_date which are duplicated - the first row shown below contains correct values, the second doens't. The other values returned, on both rows, are correct. Here is some sample data:

CARDIAC_CATHETER_ID
cat_id1234
cat_id1234

CARDIAC_CATHETER_TEST_DATE
19/12/2005 13:17
19/12/2005 13:17

BOOKING_TEST_DATE
19/12/2005 13:33
19/12/2005 08:46

PROCEDURE_DATE_TIME
20/12/2005 10:30
19/12/2005 08:46

CLINIC
CATHETER
CATHETER

I know the query is rubbish. Has anyone got any idea how it can be improved and sorted so that we don't pull back ambiguous data?

Thanks,

Stewart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 11:21:19

Without knowing your table data its very difficult for proposing a solution. Can you give a brif idea about tables and also some sample data along with your requirement?

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-15 : 14:13:29

Visakh16 is right, however you could try putting the keyword "DISTINCT" right after the word "select". Though I'm sure we could come up with a better way even if that works -- if we knew the table structure, relationshsips and data.

An infinite universe is the ultimate cartesian product.

stranter
Starting Member

17 Posts

Posted - 2008-05-16 : 06:52:41

Hi,

Our table structure is thus:

PATIENTS:

PATIENT_ID *
HOSPITAL_UNIT_NUMBER
REFERRING_PHYSICIAN1

CARDIAC_CATHETER:

CARDIAC_CATHETER_ID *
PATIENT_ID **
OPERATOR1
TEST_DATE
ITEM_NO 
BILLING_CATEGORY

BOOKING:

BOOKING_ID *
PATIENT_ID **
STATUS
TEST_DATE
PROCEDURE_DATE_TIME
CLINIC

Primary key is indicated by a *, a foreign key is indicated by a **.

Some sample data:

PATIENTS:

PATIENT_ID	HOSPITAL_UNIT_NUMBER	REFERRING_PHYSICIAN1
111111		H1			Dr Doolittle
222222		H2			Dr Pepper
333333		H3			Dr Kildare 

CARDIAC_CATHETER:

CARDIAC_CATHETER_ID	PATIENT_ID	OPERATOR1		TEST_DATE		ITEM_NO		BILLING_CATEGORY
0001			111111		Dr Zhivago		26/04/2000		CB07		3      Day Case
0002			222222		Dr Crippen		11/01/2005 09:30:00	CB07		1      In Patients
0003			111111		Dr Jeykl		21/06/2005 10:54:00	CB07		1      In Patients
0004			111111		Dr Who			08/09/2005 11:29:00	CA11		3      Day Case
0005			333333		DR Spock		27/09/2005 08:58:00	CB07		3      Day Case
BOOKING:

BOOKING_ID	PATIENT_ID	STATUS		TEST_DATE		PROCEDURE_DATE_TIME	CLINIC
123456		111111		ARRIVED		21/06/2005 08:21:00	21/06/2005 10:30:00	CATHETER
123457		222222		ARRIVED		10/01/2005 07:36:00	11/01/2005 08:30:00	CATHETER
123458		333333		ARRIVED		26/08/2005 09:44:00	27/09/2005 08:30:00	CATHETER

I require the following data:

CARDIAC_CATHETER.CARDIAC_CATHETER_ID,
CARDIAC_CATHETER.OPERATOR1,
CARDIAC_CATHETER.TEST_DATE,
BOOKING.STATUS,
BOOKING.TEST_DATE AS BOOKING_TEST_DATE,
BOOKING.PROCEDURE_DATE_TIME,
CARDIAC_CATHETER.ITEM_NO,
CARDIAC_CATHETER.BILLING_CATEGORY,
PATIENTS.REFERRING_PHYSICIAN1,
PATIENTS.HOSPITAL_UNIT_NUMBER

WHERE:

CARDIAC_CATHETER.TEST_DATE > '2005-04-01'
CARDIAC_CATHETER.BILLING_CATEGORY LIKE '3%' - Day cases
BOOKING.CLINIC = 'CATHETER' - only for catheter clinics
BOOKING.STATUS= 'ARRIVED' - patient has turned up to appointment
BOOKING.PROCEDURE_DATE_TIME is one day either side of the CARDIAC_CATHETER.TEST_DATE
CARDIAC_CATHETER.ITEM_NO starts with "CB0"

Hope that helps! In the meantime, I'll try DISTINCT...

Thanks!

Stewart

stranter
Starting Member

17 Posts

Posted - 2008-05-16 : 08:50:57

Tried DISTINCT - no luck.

Stewart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 10:51:04

Ok. AS eident from your some sample data some of PATIENTIDs (like 111111)have more than one record in CARDIAC_CATHETER table so thet join with it causes all other fields to be duplicated. So this can be avoided by only grouping on the fields from other tables. But by doing so we will be able to return only a single row value from CARDIAC_CATHETER table (as we now have only a single record). What should be the value you wanted to get? the minimum value, maximum value or you want all three values from three rows of 111111 in CARDIAC_CATHETER comma seperated?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources