CODEWITHSUNDEEP
mysqlsqlsql-serverjoin
user1908559
user1908559

Reputation: 137

sql - inner join performing cross join

I have the following table structure.

create table t1 (
  id int,
  tno int
  );

  create table t2 (
  id int,
  detailno int
  );

  insert into t1 values (101,1);
  insert into t1 values (101,2);

  insert into t2 values (101,7);
  insert into t2 values (101,8);

When I perform the following query:

select * from t1
inner join t2
on t1.id = t2.id
where t2.detailno = 8;

It performs a cross join and returns 

id | tno | id | detailno
101 | 1  | 101 |    8
101 | 2  | 101 |    8

It is basically performing a Cross join instead of an inner join. Could you please help me return only a single result instead - since detail id = 8 is in the where clause ? I have shortened the table structure and the query for easier understanding. Here is the sql fiddle for the above code. http://sqlfiddle.com/#!9/92c98/1

Upvotes: 0

Views: 267

Answers (2)

user4947592
user4947592

Reputation:

Join is working as expected.

Execute this to check:

select * from t1
inner join t2
on t1.id = t2.id;

Every id is making join with another table's id and which is correct for join.

So if you want result as your expectation you need to pass t1.tno and add into where condition as there are multiple record in another table for same id (t1.id).

Upvotes: 1

Ilessa
Ilessa

Reputation: 622

Your query is doing exactly as you've asked it to do.

What is the one result you're expecting? If you only want one line then maybe you're better off making the query more specific by adding a condition such as

WHERE t2.detailno = 8
AND t1.tno = 1

which will whittle down the results more.

Edit

If you're unsure what the value of t1.tno is going to be then could you not pass that in as a parameter? It might be more clear if you can explain why you're expecting t1.tno = 2.

You'd then potentially end up with something like this if you pass it in as a parameter.

WHERE t2.detailno = 8
AND t1.tno = @tno

Upvotes: 3

Related Questions