How to deal with duplicates in complex query

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 2000 Forums

SQL Server Development (2000)

How to deal with duplicates in complex query

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-03-29 : 10:15:08

[this is a complicated problem, so I apologize if my explanation is unclear]

I'm doing a three table join to access particular data. I am getting some duplicates returned because one of the tables has duplicates in the table.


	SELECT T_PROPERTIES.MLS_NUMBER, 
	        T_PROP_OFFICES.PROP_ID, 
		T_OFFICE.OFFICE_ID, 
		T_PROP_OFFICES.OFFICE_TYPE_ID
	FROM T_PROPERTIES, T_PROP_OFFICES, T_OFFICE
	WHERE T_PROPERTIES.PROP_ID = T_PROP_OFFICES.PROP_ID
	AND T_PROPERTIES.MLS_NUMBER IS NOT NULL AND T_PROPERTIES.MLS_NUMBER <> '0' 
	
	AND T_PROP_OFFICES.OFFICE_ID = T_OFFICE.OFFICE_ID

The bold PROP_ID is where there are duplicate entries. This is information for a real estate database. The PROP_ID has duplicate entries because some of the properties are co-listed by two agents. So you have two property id's, with two different OFFICE_TYPE_ID's.

OFFICE_TYPE_ID's are 1 or 2. So the duplicate entries will look like this: This is the output after running the query.

MLS_NUMBER PROP_ID OFFICE_ID OFFICE_TYPE_ID
2608301 262253 383 1
2008000 222255 111 1
2008000 222255 111 2
2108243 260048 245 1
2507279 260043 309 1

What I'm tring to do is check to see if the OFFICE_TYPE_ID is just 1 or just 2, then fine. If it is 1 and two (thus the duplicate entry) then just use 1.

Any help would be greatly appreciated, as I have been pondering this and I am having a problem finding a solution.

I need to get rid of the duplicate entry that appears in the output.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-29 : 10:20:11

Use a MIN on OFFICE_TYPE_ID, you will need to add a GROUP BY to your query as well..

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-03-29 : 10:24:34

Rick,

Any chance you could show me how that would look in the code, as I'm not familiar with using min? And also because OFFICE_TYPE_ID is not a condition in the WHERE statement.

ty

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 10:30:08

[code]
SELECT T_PROPERTIES.MLS_NUMBER,
T_PROP_OFFICES.PROP_ID,
T_OFFICE.OFFICE_ID,
min(T_PROP_OFFICES.OFFICE_TYPE_ID) as OFFICE_TYPE_ID
FROM T_PROPERTIES, T_PROP_OFFICES, T_OFFICE
WHERE T_PROPERTIES.PROP_ID = T_PROP_OFFICES.PROP_ID
AND T_PROPERTIES.MLS_NUMBER IS NOT NULL AND T_PROPERTIES.MLS_NUMBER <> '0'
AND T_PROP_OFFICES.OFFICE_ID = T_OFFICE.OFFICE_ID
group by T_PROPERTIES.MLS_NUMBER,
T_PROP_OFFICES.PROP_ID,
T_OFFICE.OFFICE_ID
[/code]

and use joins!!
also read about aggregate functions and group by in BOL = books online = sql server help.

Go with the flow & have fun! Else fight the flow

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-03-29 : 11:04:49

What about using GROUPBY when using aliasing.


SELECT T_PROPERTIES.MLS_NUMBER as mlsnumber, 
       T_PROP_OFFICES.PROP_ID as propid, 
       T_OFFICE.OFFICE_ID as officeid, 
       min(T_PROP_OFFICES.OFFICE_TYPE_ID) as OFFICE_TYPE_ID
FROM   T_PROPERTIES, T_PROP_OFFICES, T_OFFICE
WHERE  T_PROPERTIES.PROP_ID = T_PROP_OFFICES.PROP_ID
	AND T_PROPERTIES.MLS_NUMBER IS NOT NULL AND T_PROPERTIES.MLS_NUMBER <> '0' 	
	AND T_PROP_OFFICES.OFFICE_ID = T_OFFICE.OFFICE_ID 
group by mlsnumber, 
       propid, 
       officeid

I get an Invalid Column name error when I try this. Is this allowed?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 11:05:41

no it's not

Go with the flow & have fun! Else fight the flow

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-03-29 : 11:10:10

Does the GROUPBY statement have to include all the columns in the select statement except the one using the aggregate function? Or could you just include 1 or 2?

Example:


SELECT T_PROPERTIES.MLS_NUMBER, 
       T_PROP_OFFICES.PROP_ID, 
       T_OFFICE.OFFICE_ID, 
       min(T_PROP_OFFICES.OFFICE_TYPE_ID) as OFFICE_TYPE_ID
FROM   T_PROPERTIES, T_PROP_OFFICES, T_OFFICE
WHERE  T_PROPERTIES.PROP_ID = T_PROP_OFFICES.PROP_ID
	AND T_PROPERTIES.MLS_NUMBER IS NOT NULL AND T_PROPERTIES.MLS_NUMBER <> '0' 	
	AND T_PROP_OFFICES.OFFICE_ID = T_OFFICE.OFFICE_ID 
group by T_PROPERTIES.MLS_NUMBER

I'm just asking because I've created a temp table and aliased all the column names, so I'll have to go back and change a lot of the query.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 11:18:48

of course it has to. how else would it know how to properly group data.
read about how group by works.
aliases are just there to differently name the output columns... thus alias

Go with the flow & have fun! Else fight the flow

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-29 : 11:39:23

As for using joins:


SELECT T_PROPERTIES.MLS_NUMBER, 
       T_PROP_OFFICES.PROP_ID, 
       T_OFFICE.OFFICE_ID, 
       min(T_PROP_OFFICES.OFFICE_TYPE_ID) as OFFICE_TYPE_ID
FROM   T_PROPERTIES P
inner join T_PROP_OFFICES PO
ON PO.PROP_ID = P.PROP_ID
inner join T_OFFICE O
ON O.OFFICE_ID = O.OFFICE_ID
WHERE  T_PROPERTIES.MLS_NUMBER IS NOT NULL AND T_PROPERTIES.MLS_NUMBER <> '0' 	
group by T_PROPERTIES.MLS_NUMBER, 
       T_PROP_OFFICES.PROP_ID, 
       T_OFFICE.OFFICE_ID

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-03-29 : 11:54:07

Thank you all very much for your responses, Very Helpful! Just one last question hopefully

I am doing all this by creating a temporary table and putting this data into the temp table. Is this allowed when using GROUPBY? The reason I ask is because I am getting an error when I run this query on the line that is in bold. The error is Invalid column name 'MLS_NUMBER'.


CREATE TABLE #Temp_MLSNumbers ( 
	[MLS_NUMBER] INT NOT NULL,
	[PROP_ID] VARCHAR(255) NULL,	
	[OFFICE_MLS_ID] VARCHAR(255) NULL,
	[OFFICE_TYPE_ID] VARCHAR(255) NULL
)

INSERT INTO #Temp_MLSNumbers (MLS_NUMBER, PROP_ID, OFFICE_MLS_ID, OFFICE_TYPE_ID)

SELECT T_PROPERTIES.MLS_NUMBER, 
       T_PROP_OFFICES.PROP_ID, 
       T_OFFICE.OFFICE_ID, 
       min(T_PROP_OFFICES.OFFICE_TYPE_ID) as OFFICE_TYPE_ID
FROM   T_PROPERTIES P
inner join T_PROP_OFFICES PO
ON PO.PROP_ID = P.PROP_ID
inner join T_OFFICE O
ON O.OFFICE_ID = O.OFFICE_ID
WHERE  T_PROPERTIES.MLS_NUMBER IS NOT NULL AND T_PROPERTIES.MLS_NUMBER <> '0' 	
group by T_PROPERTIES.MLS_NUMBER, 
       T_PROP_OFFICES.PROP_ID, 
       T_OFFICE.OFFICE_ID

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 12:06:17

you can also do this. so you don't have to create the temp table first.


SELECT T_PROPERTIES.MLS_NUMBER, 
       T_PROP_OFFICES.PROP_ID, 
       T_OFFICE.OFFICE_ID, 
       min(T_PROP_OFFICES.OFFICE_TYPE_ID) as OFFICE_TYPE_ID
INTO   #Temp_MLSNumbers
FROM   T_PROPERTIES P
inner join T_PROP_OFFICES PO
ON PO.PROP_ID = P.PROP_ID
inner join T_OFFICE O
ON O.OFFICE_ID = O.OFFICE_ID
WHERE  T_PROPERTIES.MLS_NUMBER IS NOT NULL AND T_PROPERTIES.MLS_NUMBER <> '0' 	
group by T_PROPERTIES.MLS_NUMBER, 
       T_PROP_OFFICES.PROP_ID, 
       T_OFFICE.OFFICE_ID

as for the error... your code seems fine...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources