Joining of 3 tables

[dawnKo]

Hi, i need to join 3 tables. After researching, i realised i shld use inner join to join all of them together. But after joining there are extra rows. This is my code...

Select * From table1 inner join table 2 on table1.id = table2.id inner join table3 on table2.id = table3.id

can anyone tell me what is wrong with the codes??

 

[fomhoire]

If the extra lines are duplicats of rows in table1 then adding DISTINCT between the select and *.

If the rows are unexpcted rows in table try adding a where cluse in to remove them.

are the tableN.id columns primary keys?
g

 

[dawnKo]

Hi, nopt they are not primary keys... I have tried using DISTINCT but it seems like there are still rows that shld not be there... i did have a WHERE clause to filter thou..

Select * From table1 inner join table 2 on table1.id = table2.id inner join table3 on table2.id = table3.id Where StaffName = @name

 

[fomhoire]

Ok what is the result you are expecting?
I'm assuming from the query that you you are looking for a case where table1.id = table3.id (if it exists in table2.id)?
g

 

[cjard]

If the extra lines are duplicats of rows in table1 then adding DISTINCT between the select and *.

nooooo ... distinct should never be used to "get rid of duplicated rows".. instead you should put effort into understanding why they are there in the first place

 

[cjard]

can anyone tell me what is wrong with the codes??

not without seeing your data. infact, i'm not even sure what you mean by "extra rows"

do you mean there are 10 instead of 5? 2 instead of 1? you havent said what youre expecting..

lets assume you only want a single row.. but youre getting more. they might be identical or they might differ on some values
the situation arises when the link condition table1.id = table2.id (or the other one) is satisfied more than once

example:
table 1 contains id "1" just once
table 2 contains it twice
and table 3 contains it three times

lets assume the tables only contain 2 columns, id and another column called VAL that has the table name in and the row number (so table 1 has t11, table 2 has t21 and t22 etc). if we ignore the id columns (theyre all the same, all 1) you'll get six rows out of this:

t11 t21 t31
t11 t22 t31
t11 t21 t32
t11 t22 t32
t11 t21 t33
t11 t22 t33


they all have ID 1, but:

1 row in t1 matches both rows in t2 (both t2 rows have id 1)
then these two rows each match all 3 rows in table 3 so you get a combination of all 3 rows each matching twice the other rows.. i.e. 1x2x3 = 6 rows

the where clause wont necesarily help. suppose we put a where clause on the VAL column:

WHERE val = t11 //6 rows match this
WHERE val = t21 //3 rows match this
WHERE val = t33 //2 rows match this


so im still getting "extra rows"

-> the database esign is broken or you havent specified clearly enough the join conditions/you arent sure what data you want out of the database. if you can be more specific about the contents of t1, t2 and t3 and what youre hoping to get out of them, im sure we can help

 

[fomhoire]

Agreed more onformation is required.
but if DISTINCT had no effect (Ok not best solution for whole line removal but works) then the another option for extra rows is as follows:


example:
table 1 contains id "1" three times
table 2 contains it twice
and table 3 contains it once

lets assume the tables only contain 2 columns, id and another column called VAL that has the table name in and the row number (so table 1 has t11, table 2 has t21 and t22 etc). if we ignore the id columns (theyre all the same, all 1) you'll get six rows out of this:

t11 t21 t31
t11 t22 t31
t12 t21 t31
t12 t22 t31
t13 t21 t31
t13 t22 t31

if it's like this you will get the same numbre of rows but using DISTINCT will have limited effect (6 ->3) also if the WHERE cluse was on ether t2 or t3 again the effect would be multiple rows.

to fix a more detailed requiirement of the desiered function would be needed

 

[cjard]

Agreed more onformation is required.


t11 t21 t31
t11 t22 t31
t12 t21 t31
t12 t22 t31
t13 t21 t31
t13 t22 t31


if it's like this you will get the same numbre of rows but using DISTINCT will have limited effect (6 ->3)

distinct will have no effect in your example? afaics, you have only turned around the tables I gave so that t1 now has 3 rows instead of 1 and t3 has 1 row instead of 3.

ends up, every row you have produced in your 6 row results set, is unique, so distinct will not reduce this to 3

 

[fomhoire]

Yes sorry i've mentally changed the query to SELECT table1.* ...
but he should check that the tables are in the right order any way.
g

 

[cjard]

SELECT table1.* will return 6 rows, not 3?

join order / sort order have no effect on the number of rows returned?


maybe i'm missing your points here but your posts are nearly as bad as the OP's for missing info and guesswork

 

[fomhoire]

yeah been a bad day i was thinking that he was only sellecting from table1 so in your example using DISTINCT would only return one line and in mine it would return 3 (t1,t2,t3) but as thats not the case the way they are linked only really effects the WHERE cluse depending which table STAFFNAME is in.
g