Two table join returning extra rows

[shyamal]

I have two tables issue and comments. is_issue_id is a primary key in the issue table and c_is_issue_id is indexed in the comments table. If I run a select statement in the comments table for c_is_issue_id there are 40 rows returned inclusive of nulls. If I run the following select there are 300 rows returned. If I run the query for is_issue_id from issue table only I receive 30 rows. This is confusing. What do you think is going on why are 300 rows returning when neither of the tables have that many rows for the id's listed?


select is_issue_id
from issue,comments
where is_issue_id = c_is_issue_id

Thanks in advance.
Shyamal
[sig][/sig]

 

[foxdev]

It certainly sounds bizarre. Try this:

[tt]select is_issue_id
from issue
join comments on is_issue_id = c_is_issue_id
[/tt] [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[shyamal]

Thanks Robert this does not work, do you think this is occuring because the comment table does not have a foreign key for the primary key in the issue table, the id key is just an index. [sig][/sig]

 

[foxdev]

Just for the heck of it, try prefacing the column names in the where/join clause with the table names.

Not have a declared RI shouldn't make a difference, though normally it would be preferable for other reasons. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[shyamal]

Robert,

This was resolved by the fact that the join was incomplete.
This join was part of a six table join in actuality, the comment table had a common id field in the issue table as well as another table - client the join had to occur with both tables as the third table was being used as well.

So this needs to be expanded to:
select is_issue_id
from issue,comments,client
where c_is_issue_id = is_issue_id
and c_is_issue_id = cl_is_issue_id

I sent a partial query because I could not get things to work even at a simple join level.

Thanks again.

[sig][/sig]