joining data - SQL Server Forums

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

Transact-SQL (2000)

joining data

Author

Topic

ovince
Starting Member

32 Posts

Posted - 2006-11-10 : 01:22:33

hi all

I have this basic query

select glf.*, gi.*
from table1 glf, table2 gi
where glf.primaryKey1 = gi.primaryKey and
glf.a1 = gi.a1 and
glf.a2= gi.a2

I just wanted to join two tables using primary keys and a,b columns. Instead of getting back 567486 rows I get 178767 rows.

On the other hand when plotting primaryKey1 vs primaryKey2, glf.a1 vs glf.a1 and gi.a2 vs gi.a2 I have perfect one-to-one match

What is the reason of getting less rows back?

oliver

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 02:00:55

can you post the query for the 567486 rows and also the 178767 rows ?

ovince
Starting Member

32 Posts

Posted - 2006-11-10 : 02:11:36

even worst is happening...I made a copy of the exactly same table and the same is happening again.

this is the query:

select glf.*, gi.*
from table1 glf, table2 gi
where glf.primaryKey1 = gi.primaryKey and
glf.a1 = gi.a1 and
glf.a2= gi.a2

How to post rows? Just copy-paste to this window?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 02:39:53

quote:
this is the query:

select glf.*, gi.*
from table1 glf, table2 gi
where glf.primaryKey1 = gi.primaryKey and
glf.a1 = gi.a1 and
glf.a2= gi.a2

How many rows does this query return ? Is it 567486 or 178767 ?
How about the other query ?

ovince
Starting Member

32 Posts

Posted - 2006-11-10 : 02:44:56

both queries have the same form. and both queries return 178767 rows instead ~half million.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 03:00:55

check that any nulls value in column a1 and / or a2 ?

when comparing glf.a1 = gi.a1 if a1 of both table are null it will be false

Note that NULL = NULL is FALSE

ovince
Starting Member

32 Posts

Posted - 2006-11-10 : 03:14:30

is there a quick way to search for NULLs?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 03:38:06

Try this:

select glf.*, gi.*
from table1 glf, table2 gi
where glf.primaryKey1 = gi.primaryKey and
IsNull(glf.a1,0) = IsNull(gi.a1,0) and
IsNull(glf.a2,0)= IsNull(gi.a2,0)

Harsh Athalye
India.
"Nothing is Impossible"

ovince
Starting Member

32 Posts

Posted - 2006-11-10 : 03:49:07

this

select glf.*, gi.*
from table1 glf, table2 gi
where glf.primaryKey1 = gi.primaryKey and
IsNull(glf.a1,0) = IsNull(gi.a1,0) and
IsNull(glf.a2,0)= IsNull(gi.a2,0)

should join the data but to skip rows where NULL exist. Am I right?

Well, the result is the same. I get 178767 row in output as before

oliver

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:07:03

You are getting less rows back because you have narrowed down the final resultset by setting finer grain in the filter.

quote:
On the other hand when plotting primaryKey1 vs primaryKey2, glf.a1 vs glf.a1 and gi.a2 vs gi.a2 I have perfect one-to-one match

This is tha same as not having a1 and a2 matched at all!
glf.a1 = glf.a1 is ALWAYS TRUE. Same with gi.a2!!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:09:02

What does this give?

select		glf.*,
		gi.*
from		table1 glf
inner join	table2 gi on gi.primaryKey1 = glf.primaryKey
			and ( glf.a1 = gi.a1 or glf.a1 = gi.a2 or glf.a2 = gi.a1 or glf.a2 = gi.a2) )

Peter Larsson
Helsingborg, Sweden

ovince
Starting Member

32 Posts

Posted - 2006-11-10 : 04:20:30

hi peter,

I am not sure I get it.

I have to join 2 tables where their primary keys match AND their 2 coulumns match. so I applied

select glf.*, gi.*
from table1 glf, table2 gi
where glf.primaryKey1 = gi.primaryKey and
glf.a1 = gi.a1 and
glf.a2= gi.a2

Concerning plotting, I wanted to say that when plotting primaryKey1 vs primaryKey2 they match ie plot is linear. plotting a1 column from glf table vs a1 column from gi table that again match. Same for a2 of to tables. This tells me that I should get back all ~560000 rows (not ~170000)

What I should do? What I missunderstand?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:26:53

JOIN is row-based!
The three values (for any single row) in the columns in table glf must match the three values (for any single row) in the columns in table gi.

Learn and study this example and see how adding more JOIN comparison narrows down the final resultset.

declare @test1 table (a1 int, a2 int, a3 int)

insert	@test1
select	1, 2, 3 union all
select	1, 2, 4 union all
select	2, 1, 3 union all
select	1, 3, 3

declare @test2 table (a1 int, a2 int, a3 int)

insert	@test2
select	1, 2, 4 union all
select	1, 2, 3 union all
select	1, 3, 1 union all
select	1, 3, 1

SELECT		*
FROM		@test1 t1
INNER JOIN	@test2 t2 on t2.a1 = t1.a1

SELECT		*
FROM		@test1 t1
INNER JOIN	@test2 t2 on t2.a1 = t1.a1 and t2.a2 = t1.a2

SELECT		*
FROM		@test1 t1
INNER JOIN	@test2 t2 on t2.a1 = t1.a1 and t2.a2 = t1.a2 and t2.a3 = t1.a3

Peter Larsson
Helsingborg, Sweden

ovince
Starting Member

32 Posts

Posted - 2006-11-10 : 05:31:42

thank you for example Peter and for your patiance

I will use this exaple to better explain

Lets say, I have this 2 simple tables

1, 2, 3
1, 2, 4
2, 1, 3
1, 3, 3

and again the same

1, 2, 3
1, 2, 4
2, 1, 3
1, 3, 3

then we may expect 4 rows in the output. Is it true?

This WAS NOT happening in my case with my data althoug I know that the columns for joining are the same. I got ~170000rows instead of ~500000. To understand what is happening, I started to experiment with tables. That is, I made a copy of the table and put in the same databese under different name. Instead of getting ~500000 rows I get ~170000 rows again.

Harsh_Athalye sugessted that there are maybe NULLs and to try with this:

select glf.*, gi.*
from table1 glf, table2 gi
where glf.primaryKey1 = gi.primaryKey and
IsNull(glf.a1,0) = IsNull(gi.a1,0) and
IsNull(glf.a2,0)= IsNull(gi.a2,0)

basically this should replace all NULLs with zero and prevent some wrong joins.

What do you think?

oliver

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:38:03

Yes, it is true if you bind all three columns in the JOIN.
If only binding col1 and col2, the result would be 6 rows!
(1,2) for two rows in first table matching (1,2) in second table makes four possible combinations.
Then add 1 combination for (2,1) and one for (1,3). 6 in total.
Binding only col1 makes the result be 9 rows.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources