Using JOIN with duplicate records table

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)

Using JOIN with duplicate records table

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-07 : 06:58:45

I have two tables to work with:

Table 1
-------
Record_id,
First_name,
Last_name,
Age,
Gender,
Health_Authority,
Date

Table 2
-------

Record_id,
Name,
Address_1,
Address_2,
Address_3,
Address_4
Postcode

I'm using a JOIN so:

SELECT

Record_id,
First_name,
Last_name,
Age,
Gender,
t2.name AS Health_Authority,
Date

FROM patients AS t1

JOIN health_authorities AS t2 ON t1.Record_id = t2.Record_id

WHERE Record_id = '12345'

However this returns no rows. Upon inspection of Table 2 I find that none of the records is unique. There are duplicate rows all over the shop.

Any suggestions for how to handle this situation please?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-07 : 07:03:55

Try this
Instead of where clause use "and Record_id='12345'"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 07:06:46

This maybe? Anyway it will give you an idea how to deal with these things

SELECT	Record_id,
	First_name,
	Last_name,
	Age,
	Gender,
	t2.name AS Health_Authority,
	Date
FROM	patients AS t1
JOIN	(
		SELECT		MAX(Record_id) AS Record_id,
				name
		FROM		health_authorities
		GROUP BY	name
	) AS t2 ON t1.Record_id = t2.Record_id
WHERE	Record_id = '12345'

E 12°55'05.25"
N 56°04'39.16"

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-02-07 : 07:08:41

Maybe:

SELECT *
FROM patients P
    JOIN health_authorities A
        ON P.Health_Authority = A.Record_id

SELECT *
FROM patients P
    JOIN health_authorities A
        ON P.Health_Authority = A.[name]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources