SQL not recognizing equal values

Question

UPDATE: I figured out a large part of what caused my problem. When I queried the database to find the data type, it correctly returned CHAR(4) for t2.sale_code.

However, the GUI did not display leading zero's. t3.sale_code did not have leading zeros in the file that populated the table. That's what caused my confusion and I corrected the zero problem. Now it joins without any CAST or other manipulation.

__________________________________________________I have a query:

SELECT
  t1.id, t2.sale_code, t3.sale_code, t3.title
FROM
  t1
INNER JOIN t2 ON t1.id = t2.id
INNER JOIN t3 ON t3.id = t2.id

There are some different one-to-many relationships that I can't control that induce duplication. I need to do this:

WHERE
  t2.sale_code = t3.sale_code

However, this just won't work. I'm not sure what I'm doing wrong. I can manually make it work:

SELECT
  t1.id, t2.sale_code, t3.sale_code, t3.title
FROM
  t1
INNER JOIN t2 ON t1.id = t2.id
  AND t2.dt > t1.dt_active
  AND t2.dt < t1.dt_inactive
INNER JOIN t3 ON t3.id = t2.id
WHERE
  t2.sale_code = 4
  AND t3.sale_code = 4

For example, this returns a proper matched pair of sale_code's. However, when I compare them directly (`WHERE t2.sale_code = t3.sale_code) it returns no results. Am I doing something wrong here?

That was my first thought too, they both should be VARCHAR(4). I'm going to check one more time just to make sure though. If they are different types will they still respond to the > logic the same way and just not join? — Jeffrey Kramer
– Jeffrey Kramer, Commented Sep 17, 2013 at 17:17
I was mistaken, it says they are both CHAR(4) in the schema provided by the warehouse. So weird. — Jeffrey Kramer
– Jeffrey Kramer, Commented Sep 17, 2013 at 17:47

StingyJack · Accepted Answer · 2013-09-17 17:55:38Z

2

If they are both character/string types, then you should be quoting them like

sale_code = '4'

There is likely some bit of data in the column that is not visible, but is getting truncated by the auto cast of int to varchar(4).

You can either use DATALENGTH() to determine if the column has hidden chars, or copy the value into a decent text editor and look at the value in hex view.

A dirty way to get the join to work is to wrap the sale_code with trims, but you are better off cleaning your data.

RTRIM(LTRIM(t2.sale_code)) = RTRIM(LTRIM(t3.sale_code))

edited Sep 17, 2013 at 17:55

answered Sep 17, 2013 at 17:23

StingyJack

19.6k11 gold badges69 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeffrey Kramer Over a year ago

I can't manipulate the data, it's all stored in a warehouse. The dirty solution doesn't work either, it's so weird.

StingyJack Over a year ago

Were you able to see if the data in the column was of the expected length or not?

Jeffrey Kramer Over a year ago

Well, I casted them both as VARCHAR(4) and it works. I guess I'm stuck with something like that. Thanks for the suggestion. I'm concerned about the performance but I don't think there's much else I can do other than work with what I'm given.

Phil · Accepted Answer · 2013-09-17 17:18:08Z

0

Can you just add to your inner join

SELECT
  t1.id, t2.sale_code, t3.sale_code, t3.title
FROM
  t1
INNER JOIN t2 ON t1.id = t2.id
INNER JOIN t3 ON t3.id = t2.id AND t2.sale_code = t3.sale_code

answered Sep 17, 2013 at 17:18

Phil

4493 silver badges11 bronze badges

1 Comment

StingyJack Over a year ago

That is logically equivalent of having it in the where clause.

Collectives™ on Stack Overflow

SQL not recognizing equal values

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related