Hi All,

This is a very strange situation. I did a "select" query in SQL 2000 to join two tables(table_A and table_B) together. Table_A has 1 column called 'SSN.' Table_B has 2 columns; 'SSN' and 'Name.'

I ran this query string using SQL Query Analyzer:

SELECT Table_A.SSN, Table_B.SSN, Table_B.Name
FROM Table_A
inner join Table_B
on Table_A.SSN=Table_B.SSN

The problem is, the resulted table shows Table_B data appended after Table_A data. In other words, let say there is a match on a SSN from both tables, the result will look like this:


SSN | SSN | Name
-----------------------------------------------
123456789 123456789 Leon
123456789 123456789 Leon

How come it does not show ONLY one row, with Table_B's SSN and Name right next to Table_A ssn? I tried this same EXACT codes in MS Access and it works. Can anyone please tell me how I can fix this problem?

Once again, thank All for your help.

Leon

Leon,

Did you check if there are duplicate entries in any of the tables for that row?

Can you also try this to see if you still get the same output?

Cheers!

_________________________
-Vijay G
Strive for Perfection

Hi Phungleon,

Code below works for me

create table t1 (col1 int)
create table t2 (col1 int, col2 varchar(5))

insert t1 values(12345)
insert t2 values(12345,'test1')

select t1.col1,t2.col1,t2.col2 from t1 Inner join t2 on t1.col1=t2.col1

Expect the problem to be in the data like happygv suggests.

Regards,

Jago

Hi You Guys,

I do have duplicates; there are 3 different records per individual in each table. However, I don't believe the problem is due to the duplicates. When I run my query, I get 9 results per individual; which completely make no sense. Not only that, when I do get a match, I should have ONLY 3 rows of result per individual(because the 2 tables are joined together, NOT results where the rows from table_B are appended right after Table_A).

Below is what my query string really looks like(NOTE: each individual have 3 different dedtype_cd codes in each of the table. HOWEVER, the 3 dedtype_cd code for the person's from dedparms table match the dedtype_cd code from dedetail table):

SELECT dedparms.dedtype_cd, dedparms.over_amt, dedparms.emp_id, dedetail.emp_id,
dedetail.ded_am
from dedparms, dedetail
where dedparms.emp_id=dedetail.emp_id


Could it be the logical syntax error in my querystring? Please help....this is getting so frustrating.

THank you.

Leon

Nope, it's definately the duplicates. you're getting the 'cartesian product' returned. Essentially, for each match in the right-hand table, you get all the matches from the left. Hence nine result, rather than your expected three, or the correct one.
You need to either remove the dupes; find some way of matching each row more accurately i.e. adding more columns to match on; or have another look at the data structure and do a little redesign



There are two secrets to success in this world:
1. Never tell everything you know

Thanks for the tips! I'll go back to the drawing board and see what I can do.

Hi Leon,

If you don't mind posting here the snapshot of data from both the tables(atleast of one entry linked from both tables), one would be in a better position to point you out what to do in getting the desired result. Does that make sense Leon?

May be by seeing the data we would get a better picture of what you wanted and could suggest you if you really require redesigning the same.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

Hi Vijay,

Thanks for taking the time to look into this. Chrislepingwell was right; it does have to do with duplicates records. So the solution I used was to the "Group By" fuction to combine all the columns together. Thanks again. Have a nice day.

Leon

I remember you mentioned about the duplicates records problem as well. So I went back to my SQL and created a much simplier table to test it out. From there, I found the problem you guys mentioned.