Inner Join MySQL not gives expected output

[wawane7256]

I am not able to join the table, my code is seems correct but getting the output that fix my logic, actually see,

table1 as t1

> ID  | name   | qty  | other
> ----------------------------------
> 1   | Mango  | 10   | 9
> ----------------------------------
> 1   | Banana | 12   | 12
> ----------------------------------
> 1   | Apple  | 5    | 5

SELECT t1.ID FROM `table1` t1 INNER JOIN `table1` t2 ON t1.`qty` != t2.`other`

Expected Output : ID: 1 because its qty is not equal to 10

I am getting all records, I also read StackOverflow and used other join methods but not worked.
Please help me how to achieve the goal.

[Thallius]

@wawane7256

SELECT t1.ID FROM table1t1 INNER JOINtable1 t2 ON t1.qty != t2.other``

This could not work as the quantity of row 2 is also not the same as other of row 1.
At the end you do not need a join at all. Why not still use

SELECT t1.ID FROM table1 t1 WHERE t1.qty != t1.other

If you absolutely need to use a join (which really makes no sense) you need some unique identifier in your rows. I do not know why your id is always 1. normally an Id column is a unique primary key with Auto increment. In that case you would have your unique identifier of the row and could use

SELECT t1.ID FROM table1 t1 INNER JOIN table1 t2 ON t1.Id = t2.Id AND t1.qty != t2.other

How your table looks at the moment you could also use

SELECT t1.ID FROM table1 t1 INNER JOIN table1 t2 ON t1.name = t2.name AND t1.qty != t2.other

But this would recommend that each fruit is only one time in the table.

[m_hutley]

@wawane7256

Expected Output : ID: 1 because its qty is not equal to 10

SELECT ID FROM table1 WHERE qty != other

Trying to use a join here is going to cause some issues, as Thallius points out; the excessive join conditions makes it functionally inefficient to join the table to itself to do a simple comparison between columns in the same row.

You should only be thinking about joins if you needed to compare things in different rows.

[r937]

@m_hutley

You should only be thinking about joins if you needed to compare things in different rows.

exactly!!

which is why an anti-join is probably what is needed here (or a NOT EXISTS subquery)

SELECT t1.ID 
  FROM table1 AS t1 
LEFT OUTER
  JOIN table1 AS t2 
    ON t2.other = t1.qty
 WHERE t2.id IS NULL

it’s an anti-join because the IS NULL is looking for unmatched rows

“find all IDs whose qty value is not found anywhere in the other column”

[DaveMaxwell]

@r937

which is why an anti-join is probably what is needed here (or a NOT EXISTS subquery)

Even that won’t work if the id (or even id and name, which looks to more likely to be the PK for that table) isn’t accounted for because id 2 records would get compared to id 1 records and so on. The join just isn’t the right thing here at all, nor needed. A simple where would do.

[r937]

@DaveMaxwell

because id 2 records would get compared to id 1 records and so on.

but that could be exactly the use case here!

we ~need~ OP’s clarification here, not speculation, of which i, too, am guilty

because that table and those column names and values are clearly made up

the real situation is probably difficult to explain, and OP thought (incorrectly) that dumbing it down might make it easier

[DaveMaxwell]

That very well could be. I was basing my comment on the expected output in the original post, but the point is accurate.

Do Mango, Banana and Apple all have the same IDs, or was that just quick dummy data.

If the ID is the same, do they want to see 1 because the sums aren’t equal (10+12+5) != (9+12+5), or the individual record where 10 !=9? If the second, then shouldn’t the name show as well to make it clearer what is being looked at?

@wawane7256, we could use some clarity on your data source, and more detail on what the end result should be.