I have something strange happening on SQL2005.
I have 2 tables, orders and products.

Now, I would like to see, if there is any order with productID and countryID
which not exsists in product table.
So, I created join:

select distinct o.productID, o.id_country from  orders o LEFT JOIN products
p
ON i.id_country=p.id_country AND o.productID=p.id
where p.id is null

As result I get productID ='I0001' and id_country ='ALL', but this
combination doesn't exists in orders table,
it exists only in products table.

SELECT productID from orders where id_country ='ALL'
(productID='0001' doesn't exists in this result set)

OR if I execute:
SELECT * from orders where id_country ='ALL' AND productID='0001'

I get nothing.

Any idea, what could be wrong?

The collation is different than default database collation, but the same on
all columns, which I compare.

Regards,Simon

simonZ wrote:
Unless I am missing something, that is impossible. Note there's a typo
in the query you posted, so are you sure you are running the same
query?

Try copying and pasting the below query and running it:

SELECT DISTINCT o.productID, o.id_country
FROM orders o
LEFT JOIN products p
  ON p.id_country = o.id_country
  AND p.id = o.productID
WHERE p.id IS NULL

Trouble with outer join in exclusive disjunctions:


create table    #t1
    (
    c1    int        null
    ,c2    varchar(8)    null
    )
go

create table    #t2
    (
    c1    int        null
    ,c2    varchar(8)    null
    )
go

insert    #t1
    (
    c1
    ,c2
    )
    select    1 as c1
        ,'kjsd' as c2
    union all
    select    2
        ,'ftuh'
    union all
    select    null
        ,'jklmdc'
go

insert    #t2
    (
    c1
    ,c2
    )
    select    1 as c1
        ,'kjshjgwe' as c2
    union all
    select    3
        ,'riuewf'
    union all
    select    null
        ,'nuwiedfh'
go

select    *
    from    #t1

select    *
    from    #t2

select    #t1.*
    from    #t1
        left join    #t2
                on    #t2.c1 = #t1.c1

select    *
    from    #t1
    where    (not exists (
            select    *
                from    #t2
                where    (#t2.c1 = #t1.c1)
            ))
go


ML

---
http://milambda.blogspot.com/

I found what the problem is.

ProductID column in order table is unicode data type: nVarChar(10).

The order table is filled from data of many different countries with many
different collations.

So, there is record in order table with value='I0001' but this data came
from country with cyrilic language and in unicode
SQL has different record even if it looks the same in result grid.

So, 'I0001' comming from cyrilic country is not the same data for SQL server
as 'I0001' comming from latin country.

So, If I run this query:
SELECT productID FROM orders WHERE productID ='I0001'
I get no result

but if i run this query:
SELECT productID FROM orders

I see among the results also 'I0001' as productID.

But it's the same product to me and I need to treat them as the same
product.

Any idea how to deal with this problem?

Regards,Simon

Show quote

Hide quote

Consider using the COLLATE keyword. There are examples in Books Online.


ML

---
http://milambda.blogspot.com/

well, data with productID='I0001' came from Ukrain which has the following
collation:SQL_Ukrainian_CP1251_CI_AS
So, if I use for example:

SELECT productID FROM orders WHERE productID ='I0001'  COLLATE
SQL_Ukrainian_CP1251_CI_AS

i stilll don't get any result, but productID with the value 'I0001' exists
in orders table.

Do you have any particular example? I'm looking in Books online but anything
I try, nothing works.

Thank you,
Regards,Simon

Show quote

Hide quote

How do you get the value 'I0001' from the result set - do you copy it or type
it?


ML

---
http://milambda.blogspot.com/

I just run query:

SELECT DISTINCT productID FROM orders

and one of the results in grid is: 'I0001'

regards,S

Show quote

Hide quote

Yes, I understand. But how do you get the value from this result set to be
used in the query?


ML

---
http://milambda.blogspot.com/

Well, for example, I use it in many queries for statistics and I must join
all the products with ID='I0001' together.

But SQL treats ID='I0001' from ukrain as different product than ID='I0001'
from some other country(the same is with other products).

For example, if I would like to get the sum of quantity for all countries:

SELECT sum(quantity) FROM orders where productID='I0001'

I get the sum of quantity of sold products in all countries without quantity
sold in Ukraina.

Also, if I would like to get the product name for example:

SELECT DISTINCT o.productID,p.productName FROM orders o LEFT JOIN products p
ON o.productID=p.productID AND o.id_country=p.id_country
WHERE p.id_country ='UAH'

I don't get the name of product even if I have productID='I0001' in both
tables, using collate keyword or not, the same result - nothing.

Any idea?
Thanks,
regards,S

Show quote

Hide quote

Please provide DDL, so that we can have a better understanding of your model.

Without proper DDL, anything we suggest is a guessing game.

Are you certain that the rows that should correspond to your search
conditions actually exist? You've mentioned the fact that you can *see* them
in one of the result sets - but are you certain?

You've posted two queries using an outer join and a condition in the where
clause that might restrict the result effecting in a result set that you'd
get fro an INNER join. Try moving the condition(s) from the WHERE clause to
the appropriate JOIN clause.

Something like this:

SELECT DISTINCT o.productID, p.productName
FROM orders o
LEFT JOIN products p
ON o.productID=p.productID AND o.id_country=p.id_country
AND p.id_country ='UAH'


ML

---
http://milambda.blogspot.com/

This are my tables:

CREATE TABLE [dbo].[products](
[productID] [nvarchar](10) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL,
[ID_COUNTRY] [char](3) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL,
[NAME] [varchar](100) COLLATE SQL_Slovenian_CP1250_CI_AS NULL,
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
[productID] ASC,
[ID_COUNTRY] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[orders](
[ID] [nvarchar](10) COLLATE SQL_Slovenian_CP1250_CS_AS NOT NULL,
[ID_ITEM] [int] NOT NULL,
[ID_COUNTRY] [char](3) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL,
[PRODUCTID] [nvarchar](10) COLLATE SQL_Slovenian_CP1250_CI_AS NULL,
[QUANTITY] [float] NOT NULL

CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[ID_ITEM] ASC,
[ID_COUNTRY] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


I'm certain that the row should exists.

If I execute the query:
SELECT * FROM orders

some of the rows I get as result is:

ID           ID_ITEM    ID_COUNTRY    PRODUCTID    QUANTITY
------------------------------------------------------------------------
.......
10239            1                    UAH                 I0001
20
........
11234            3                    ALL                  I0001
10
11236            1                    ALL                  I0001
12
..........

So, If I execute the following query:

SELECT * FROM orders WHERE productID='I0001' AND ID_COUNTRY='UAH'

i should get some row, but I get nothing.

If I execute for example:

SELECT * FROM orders WHERE productID='I0001' AND ID_COUNTRY='ALL'

i get many rows.

I gues that this is because the UAH rows came from cyrilic database and ALL
rows came from latinic database, but I don't know how to deal with that.

If you want, give me one day to prepare database with only couple of rows
and only this 2 tables and I can send it to you filled with data.
Than you will know exact what is the problem.

Just give me some email to send it.

Thanks,
Simon

Show quote

Hide quote

Have you tried prefixing the literal string with the unicode declaration?

I.e.:
SELECT * FROM orders WHERE productID = N'I0001' AND ID_COUNTRY = 'UAH'


ML

---
http://milambda.blogspot.com/

How about:

SELECT * FROM orders WHERE rtrim(ltrim(productID)) = 'I0001' AND
ID_COUNTRY='UAH'


ML

---
http://milambda.blogspot.com/

Thank you ML for your help, but nothing works.

SELECT * FROM orders WHERE rtrim(ltrim(productID)) = 'I0001' AND
ID_COUNTRY='UAH'

or

SELECT * FROM orders WHERE productID = N'I0001' AND ID_COUNTRY = 'UAH'

or

SELECT * FROM orders WHERE rtrim(ltrim(productID))  = N'I0001' AND
ID_COUNTRY = 'UAH'

I tried also:

SELECT * FROM orders WHERE productID like 'I000%' AND ID_COUNTRY = 'UAH'

and all combinations ('%I0001%',.....), but no success.

If I try: SELECT * FROM orders WHERE ID_COUNTRY = 'UAH' i get this product
among results.

This is first time I have problems with that and it looks really strange.

Regards,Simon

Show quote

Hide quote

Hi SimonZ!
Have you tried this
SELECT * FROM orders WHERE productID COLLATE Latin1_General_CI_AI
like 'I000%' AND ID_COUNTRY = 'UAH'

The importante here is to force the conversion from the collation.
You can use the command SELECT * FROM ::fn_helpcollations() to get the
collation list.

HTH,
João Araújo

Show quote

Hide quote

Programatically Accessing Maintenance Plans through SMO
Cannot see system views?
How to calculate a 5 minute running average?
Tree table
error-checking in dynamic SQL?