Problem with JOIN returning too many results

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)

Problem with JOIN returning too many results

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-07 : 20:55:56

[code]
SELECT T_PROPERTIES.MLS_NUMBER
FROM T_PROPERTIES
INNER JOIN T_PROP_OFFICES
ON T_PROPERTIES.PROP_ID = T_PROP_OFFICES.PROP_ID
INNER JOIN T_OFFICE
ON T_PROP_OFFICES.OFFICE_ID = T_OFFICE.OFFICE_ID
AND T_OFFICE.NAME = 'OPERATIONS'
[/code]

I am running this query and the result set is way too large -- seems that it's doing a cross join. The statement that defines the query is the T_OFFICE.NAME = 'OPERATIONS', but it seems like my query doesn't execute that part.

Can anyone analyze and give me an idea where the logic is wrong here?

thank you
cc

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-07 : 21:06:12

Read the hint link below and give use some more details...what are the #of rows in each table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-07 : 21:17:36

The T_PROPERTIES table probably has about 50 rows.
The T_PROP_OFFICES table has 4 rows
The T_OFFICE table has 30 rows.

The T_PROPERTIES.MLS_NUMBER column has about 5 million rows. I am trying to get to the T_OFFICE table going through the T_PROP_OFFICES table so that I can only select properties from the OPERATIONS office.

The result set should be about 1000, and I am getting over 50,000 results returned.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-07 : 21:18:00

Put in a where clause to limit the result set to a manageable size then check why you are getting too many rows - do a select * or select tbl.* for each table in turn so you can see the complete data.

You aren't getting a cross join but are probably missing some criteria.
I'm guessing that T_PROPERTIES has multiple rows for each T_PROP_OFFICES

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-07 : 21:20:55

T_PROPERTIES table probably has about 50 rows.
The T_PROPERTIES.MLS_NUMBER column has about 5 million rows
I am getting over 50,000 results returned

???
Don't understand.
I am getting over 50,000 results returned
and you aren't talking about the resultset.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-07 : 21:25:09

The resultset in the MLS_NUMBER column is 56,000 when I run this query. It should only be about 1,000. In another words 56,000 rows are returned with MLS_NUMBER's.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-07 : 21:33:28

How many rows do you get from
SELECT *
T_PROP_OFFICES
INNER JOIN T_OFFICE
ON T_PROP_OFFICES.OFFICE_ID = T_OFFICE.OFFICE_ID
AND T_OFFICE.NAME = 'OPERATIONS'

and is that what you expect?
If so then look at the join to T_PROPERTIES

could be
SELECT distinct T_PROPERTIES.MLS_NUMBER
FROM T_PROPERTIES
INNER JOIN T_PROP_OFFICES
ON T_PROPERTIES.PROP_ID = T_PROP_OFFICES.PROP_ID
INNER JOIN T_OFFICE
ON T_PROP_OFFICES.OFFICE_ID = T_OFFICE.OFFICE_ID
AND T_OFFICE.NAME = 'OPERATIONS'

or

SELECT T_PROPERTIES.MLS_NUMBER
FROM T_PROPERTIES
INNER JOIN T_PROP_OFFICES
where T_PROPERTIES.PROP_ID in
(select T_PROP_OFFICES.PROP_ID
from T_PROP_OFFICES
INNER JOIN T_OFFICE
ON T_PROP_OFFICES.OFFICE_ID = T_OFFICE.OFFICE_ID
AND T_OFFICE.NAME = 'OPERATIONS'
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-07 : 22:00:09

thanks so much for you responses! It helped me to figure it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources