Inner Join help

[lesmith]

Hello Folks

I am trying to do a INNER JOIN of 2 tables. but I am not getting any results or any errors.

I hope you can help as I have been trying this for a few hours now without any success.

Here is the SQL:

SELECT table1.*, table2.*
FROM table1
INNER JOIN table2
ON table1.teamneame = table2.teamname

Obviouslt both tables have data and the teamname will match up.

Like I have said it is not returning any results.

I really help you can advise.

If I do a LEFT , RIGHT join I only get data from that side.

Thanks in advance.

[tripy]

Why don't you go on the simpler way of writing?


SELECT *
FROM table1 t1, table2 t2
WHERE t1.teamname = t2.teamname


If you did a copy/paste, check your syntax, as you put teamneame rather than teamname in the table1 joint.

Now, for that query to return anything, your datas must be correct.
Meaning, having the exact same syntax, without "white characters", like space or tabs, and be the same case.
"SomeThing" is not equals to "someThing", and the datas won't match.

A query like this is really basic, so if it's dont giving any result, the only meaning is that your data set is incoherent.

Also, on the performance side, it would be better to make the joint on a numeric field rather than a name field.
You could use 1 more joint, and store the team names in it, like this:


table table1:
teamId numeric not null auto increment,
nameRef numeric not null,
....

table table2:
teamId numeric not null auto increment,
nameRef numeric not null,
....

table teamNames
nameRef numeric not null auto increment
teamName varchar(255) not null



And then you query would be:

SELECT t.*, n.teamName
FROM table1 t1, table2 t2, teamNames t1name, teamNames t2name
WHERE
t1.nameRef=t1name.nameRef
and t2.nameRef=t2name.nameRef
and t1.nameRef=t2.nameRef
and t1name.teamName = 'whatever'


This would make the matching between every rows from table1 and table2 that would have the teamName "whatever", and the joints between the table1 and table2 would be made on a numeric value.

[lesmith]

Hello Tripy

Thanks for the great info.

Ill have a go on what you have said. I cant use ID's as its named data but its only a small query so it would not take a long time to match up.

Thanks gain for your help.

[guelphdad]

Why don't you go on the simpler way of writing?


SELECT *
FROM table1 t1, table2 t2
WHERE t1.teamname = t2.teamname



your query is exactly the same above, but as the queries get more complicated and include outer joins, are much easier to make mistakes in and end up with cross join effects.

stick to explicit ON clause INNER JOINs rather than comma join syntax.

[tripy]

your query is exactly the same above


I know, and that was the point. For me, at least.
Using one syntax or the other looks more like a personal preference to me.
I would not say than the explicit inner joins are less prone to errors.

In the end, I found the comma syntax more readable, and easier to spot errors.
finally, in both syntax, the user should know what he wants to do with which data structure before writing his query.

[guelphdad]

if you use an INNER JOIN then you would specify the ON clause directly below it. with your method, all tables can be listed. Say there are 10. after you name all of them you can then join the columns in a WHERE clause. it is very easy to leave one of the matching conditions out. With INNER JOIN and an ON clause, each one goes beneath the table it is matching. If you leave it out you will notice you have.

Also when you mix comma join syntax with outer joins you will get an error when you do something like:


select *
from
foo, bar, qux
left outer join moe
where foo.somecolumn=qux.anothercolumn
and bar.thiscolumn=foo.thatcolumn
and qux.this=moe.that


as an example because after you name the MOE table in the outer join, only the MOE and QUX tables are in scope. at that point
foo.somecolumn=qux.anothercolumn
throws an error because foo is not in scope.

Mysql allowed such sloppy syntax in mysql 4 and older but throws the error in mysql 5

so yes there are two examples of why you shouldn't join your tables with comma syntax, and why it is easier to produce errors when doing so.