Help with Where Clause

[willdevelope]

How do I write where clause to compare first two character in two differect table to show the first two don't match
***If Storage first two letters don't match Quantity please show first two letters only
Ex

The data tables quantity and storage

Quantity.unit Storage.area
CS cs,pk,pl
CS cs,pk,pl
CS cs,pk,pl
CS <-- --> pk,pl,cs--first two letter don't match show data

***Desired out put***

Quantity Storage
cs pk

 

[gmmastros]

Where Left(Quantity.unit, 2) <> Left(Storage.area, 2)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

select Quantity.Unit, Storage.Area, other fields from Quantity INNER JOIN Storage on Quantity.Pk = Storage.FK where LEFT(Quantity.Unit,2) <> LEFT(Storage.Area,2)

 

[willdevelope]

Well guys, once i ran the code it did not give any error but no data was returned..

 

[gmmastros]

It's probably a data issue. You could have white-space in your data (something that is very hard to notice).

Usually, white-space is in the form of an actual space character, but could be a tab, carriage return, line feed, etc....

Try this....

Where Left(LTrim(Quantity.unit), 2) <> Left(LTrim(Storage.area), 2)

LTrim will remove leading spaces. If you have other white-space characters, you'll need to remove them another way. Something like....

Where Left(Replace(Quantity.unit, Char(9), ''), 2) <> Left(Replace(Storage.area, Char(9), ''), 2)

That will replace tab characters with empty string before returning the left 2 characters. Char(13) is a carriage return and Char(10) is a line-feed.

After removing the appropriate white-space characters, the code should work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[willdevelope]

Thanks alot Mr.Mastro your SQL FU IS VERY GOOD...