cartisean product
-
select field 1, field2
from table1 where field1 = 'char 200' -- 17 rows
select field3, field 4
from table2 where field3 = 'char 200' -- 17 rows
select t1.*, t2.*
from table1 t1
inner join table2 on t2.field3 = t1.field1
where t1.field1 = 'char 200' -- Getting a cartisan product ???
Not sure why for every row of table1, I get table2 row : total 289 (17*17) instead of 17.
I am using sqlserver 2000.
-
Each row in table1 where field1 = 'char 200' is being joined with all 17 rows in table2 where field3 = 'char 200'. This is why you are getting 289 rows.
-
Normal SQL behaviour.
What were you expecting ? Use SELECT DISTINCT if you only want unique rows returned.
-
select t1.*, t2.*
from table1 t1
inner join table2 on t2.field3 = t1.field1
where t1.field1 = 'char 200'
I want field1, field3, field4 --?
-
select distinct t1.Field1, t2.Field3, t2.Field4
from table1 t1
inner join table2 on t2.field3 = t1.field1
where t1.field1 = 'char 200' AND t2.Field3 = 'char 200' --gives a 2nd chance to use an index
You may also test the same thing with group by... maybe you'll get better performance!
-
select t1.field1, t2.field3, t2.field4
from table1 t1
inner join table2 on t2.field3 = t1.field1
where t1.field1 = 'char 200'
(gives you many dupe rows)
or.....
select DISTINCT t1.field1, t2.field3, t2.field4
from table1 t1
inner join table2 on t2.field3 = t1.field1
where t1.field1 = 'char 200'
...that what you want?
/Kenneth
-
Does not care with with either DISTINCT or Group by ??
Did not think this will be so complicated
-
What do you mean ("does not care"). It's not like we chose what keywords to use when the language was written. Or what design you created.
In this case you either go with distinct or group by. Or you don't return the correct data. It's all your choice at this point
. -
It's nothing very complicated about it.
DISTINCT and GROUP BY are just two ways of essentially producing the same result - duplicates are removed.
Since your query produces many duplicates in the result (which is expected), any of the above can be used to remove those dupes from your result.
/Kenneth
-
I get the same results:
with distinct or with group by or none of the 2: straight joins as I had in the begning.
-
In this case:
table1: - field1, field2
table2: - field3, field4
field1=field2
so distinct/group by of all the output:
field1, field2, field3, field4 is distinct as field2 and field4 have different values. ???
-
It would probably be easiest if you could post some samples of your data. That way we can see what can or cannot be done with it.
/Kenneth
-
So you get 17 rows with 3 columns instead. If that's not what you want then you
1 - Need to know what you want
2 - Show us cause my mind reading is off today!
-
got it! while creating sample data. There is no way that I can relate table1.field2 and table2.field4.
group1 fname lname
group1 fname2 lname2
group1 fname3 lname3
group1 fname4 lname4
group1 fname5 lname5
table2
field3 field4
group1 user1
group1 user2
group1 user3
group1 user4
group1 user5
As I know the data ( and sqlserver does not know to match fname lname to user)
I expected:
group1 fname lname user1
group1 fname2 lname2 user2
group1 fname3 lname3 user3
Stored procedure is the solution:
thanks for all the input.
-
Remi,
Because she is using SELECT t1.*,t2.*, every spawned row is totally unique... DISTINCT will not work in this case...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply