CODEWITHSUNDEEP
mysql
Attila Szeremi
Attila Szeremi

Reputation: 5478

MySQL eliminating rows after left join

I found a weird way of how mysql does something. Someone please explain why it happens.

I select all the columns from table A, and left join from table B with a certain condition, that may or may not be met for each table A row.

Now after the joins, in the WHERE statement, I want to completely leave out rows (not the join part, but whole rows) that have a table B's column categoryId of value 14 IF the there are table B results (if there aren't, I do want the row to be kept).

So in WHERE I do WHERE tableB.categoryId != 14. There are no results (when there are supposed to be). For curiosity, I tried to rewrite the operator to =, so WHERE tableB.categoryId = 14, and the result was that the results I wanted I didn't get, a and the results I didn't want I did get.

I don't understand this, this is weird.
Shouldn't the not equals thing have worked?
I also tried !(tableB.categoryId = 14), but that also yielded no results. At the end, I tried WHERE IF(tableB.categoryId = 14, 0, 1), and that did work for some reason. Why did that work and why didn't the not equals and negating operators work?

Upvotes: 0

Views: 111

Answers (2)

Meier
Meier

Reputation: 3880

I think your problem has nothing to do with left join.

For debugging you should try a simpler query without the left join. Just SELECT * FROM tableB WHERE categoryId = 14

Also check the type the the categoryId, the problem may be implicit conversions from int to string that are not working in all contexts, so they are better made explicit.

Upvotes: 0

poncha
poncha

Reputation: 7866

You need WHERE tableB.categoryId IS NULL OR tableB.categoryId != 14

Upvotes: 5

Related Questions